Избегайте повторной отрисовки пользовательской функции

На чтение 3 мин Опубликовано 22.09.2024

Вопрос или проблема

Я определяю пользовательскую функцию в Google Apps Script, которая вызывает внешний API для возврата определенного значения в ячейке. Похоже, что эта функция продолжает вызываться через определенные интервалы или при повторном открытии таблицы.

Пример: =FETCH_DATA(A1)

function FETCH_DATA(input) {
  var url = "https://example.com/test-endpoint";
  var response = UrlFetchApp.fetch(url, {
     'method' : 'post',
     'payload': {
      input: input
    }
  }).getContentText();
  
  data = JSON.parse(response)["data"]

  return data;
}

Есть ли способ, который позволяет сделать запрос только один раз и обновить его только в том случае, если пользователь явно снова сохраняет ячейку?

Многие другие люди рекомендуют создать пользовательское меню и затем напрямую заменить значение ячейки, вместо того чтобы определять пользовательскую функцию, что нецелесообразно для моего случая использования.

Ответ или решение

Проблема, с которой вы столкнулись, связана с тем, что пользовательские функции в Google Sheets вызываются автоматически при пересчете таблицы. Это может происходить при открытии документа или при изменении любого значения в таблице. Если вы хотите избежать повторных вызовов вашей функции FETCH_DATA, вам нужно использовать другой подход.

К сожалению, невозможно полностью отключить автоматическое пересчитывание пользовательских функций. Однако вы можете использовать свойство CacheService в Google Apps Script для хранения значений и их последующих извлечений. Таким образом, вы сможете снизить количество обращений к внешнему API и обновлять данные только при явном запросе.

Вот пример, как это можно реализовать:

function FETCH_DATA(input) {
  var cache = CacheService.getScriptCache();
  var cachedData = cache.get(input);

  // Проверяем, есть ли данные в кеше
  if (cachedData !== null) {
    return cachedData; // Возвращаем данные из кеша
  }

  // Если данных в кеше нет, делаем запрос к API
  var url = "https://example.com/test-endpoint";
  var response = UrlFetchApp.fetch(url, {
     'method' : 'post',
     'payload': {
      input: input
    }
  }).getContentText();

  var data = JSON.parse(response)["data"];

  // Сохраняем данные в кеш с временем истечения 1 час
  cache.put(input, data, 3600);

  return data;
}

Тем не менее, это всё еще приводит к вызовам функции при каждом изменении данных на листе, если кеш не содержит данных. Чтобы избежать вызовов вообще, вам нужно будет использовать другой метод, например, создание пользовательского меню и функцию для обновления значений.

Вот пример создания пользовательского меню:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Fetch Data')
      .addItem('Обновить данные', 'updateData')
      .addToUi();
}

function updateData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange("A1"); // Укажите диапазон, который нужно обновить
  var input = range.getValue();

  var url = "https://example.com/test-endpoint";
  var response = UrlFetchApp.fetch(url, {
    'method': 'post',
    'payload': {
      input: input
    }
  }).getContentText();

  var data = JSON.parse(response)["data"];

  // Записываем данные в ячейку
  range.setValue(data);
}

С помощью этого подхода пользователь сам может инициировать обновление данных, и повторы обращений к API будут исключены, если он не выберет явно опцию обновления.

Несмотря на то что этот подход требует от пользователя дополнительных действий, он позволяет избежать постоянных вызовов функции и чрезмерного использования ресурсов API.