Обновить данные, полученные с помощью пользовательской функции в Google Sheet
Я написал собственный скрипт Google Apps, который получит id и получить информацию из веб-службы (цена).
Я использую этот скрипт в электронной таблице, и он работает просто отлично. Моя проблема в том, что эти цены меняются, а моя таблица не обновляется.
Как я могу заставить его перезапустить скрипт и обновить ячейки (без ручного обхода каждой ячейки)?
14 ответов
Хорошо, похоже, моя проблема заключалась в том, что Google ведет себя странным образом - он не перезапускает скрипт, если параметры скрипта схожи, он использует кэшированные результаты предыдущих запусков. Следовательно, он не повторно подключается к API и не восстанавливает цену, он просто возвращает предыдущий результат сценария, который был кэширован.
Подробнее см. Здесь: https://code.google.com/p/google-apps-script-issues/issues/detail?id=888
и здесь: скрипт для суммирования данных не обновляется
Моим решением было добавить еще один параметр в мой скрипт, который я даже не использую. Теперь, когда вы вызываете функцию с параметром, который отличается от предыдущих вызовов, ей придется перезапустить скрипт, потому что результат для этих параметров не будет в кеше.
Поэтому всякий раз, когда я вызываю функцию, для дополнительного параметра я передаю "$A$1". Я также создал пункт меню с именем refresh, и при запуске он помещает текущую дату и время в A1, поэтому все вызовы сценария с $ A $ 1 в качестве второго параметра придется пересчитать. Вот код из моего скрипта:
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Refresh",
functionName : "refreshLastUpdate"
}];
sheet.addMenu("Refresh", entries);
};
function refreshLastUpdate() {
SpreadsheetApp.getActiveSpreadsheet().getRange('A1').setValue(new Date().toTimeString());
}
function getPrice(itemId, datetime) {
var headers =
{
"method" : "get",
"contentType" : "application/json",
headers : {'Cache-Control' : 'max-age=0'}
};
var jsonResponse = UrlFetchApp.fetch("http://someURL?item_id=" + itemId, headers);
var jsonObj = eval( '(' + jsonResponse + ')' );
return jsonObj.Price;
SpreadsheetApp.flush();
}
И когда я хочу поместить цену товара с идентификатором 5 в ячейку, я использую следующую формулу:
=getPrice(5, $A$1)
Когда я хочу обновить цены, я просто нажимаю пункт "Обновить" -> "Обновить". Помните, что вам нужно перезагрузить электронную таблицу после изменения onOpen() скрипт.
Я знаю, что это старый вопрос. Но этот метод не требует никаких действий пользователя, кроме внесения изменений.
То, что я сделал, было похоже на tbkn23.
Функция, которую я хочу переоценить, имеет дополнительный неиспользуемый параметр, $A$1. Так что вызов функции
=myFunction(firstParam, $A$1)
Но в коде функция подписи
function myFunction(firstParam)
Вместо того, чтобы иметь функцию Refresh, я использовал функцию onEdit (e), как это
function onEdit(e)
{
SpreadsheetApp.getActiveSheet().getRange('A1').setValue(Math.random());
}
Эта функция активируется всякий раз, когда редактируется любая ячейка в электронной таблице. Итак, теперь вы редактируете ячейку, случайное число помещается в A1, это обновляет список параметров, как предложил tbkn23, вызывая переоценку пользовательской функции.
Это очень поздно, и я не знаю, было бы полезно, но на самом деле есть настройки, которые вы можете сделать NOW() обновлять автоматически
Логика скрипта:
- Пользовательские функции не обновляются, пока не изменятся аргументы.
- Создайте триггер onChange для изменения всех аргументов всех пользовательских функций в электронной таблице с помощью textFinder
Фрагмент:
/*@customfunction*/
function sheetNames(e) {
return SpreadsheetApp.getActive()
.getSheets()
.map(function(sheet) {
return sheet.getName();
});
}
/*Create a installable trigger to listen to grid changes on the sheet*/
function onChange(e) {
if (!/GRID/.test(e.changeType)) return; //Listen only to grid change
SpreadsheetApp.getActive()
.createTextFinder('=SHEETNAMES\\([^)]*\\)')
.matchFormulaText(true)
.matchCase(false)
.useRegularExpression(true)
.replaceAllWith(
'=SHEETNAMES(' + (Math.floor(Math.random() * 500) + 1) + ')'
);
}
Читать:
Если ваша пользовательская функция находится внутри определенного столбца, просто закажите вашу электронную таблицу по этому столбцу.
Упорядочивание вызывает обновление данных, которое вызывает вашу пользовательскую функцию сразу для всех строк этого столбца.
Как отмечалось ранее:
Пользовательские функции не обновляются, пока не изменятся аргументы.
Возможное решение - создать флажок в одной ячейке и использовать эту ячейку в качестве аргумента для пользовательской функции:
- Установите флажок: выберите свободную ячейку, например, [A1], перейдите к [Вставить] > [Флажок]
- Сделайте эту ячейку аргументом:
=myFunction(A1) - Установите флажок, чтобы обновить формулу
Это может быть очень старая тема, но может быть полезно для того, кто пытается это сделать, как я только что.
Отработав сценарий Lexi "как есть", он больше не работает с текущими Sheets, но если я добавлю фиктивную переменную в свою функцию в качестве параметра (нет необходимости фактически использовать ее внутри функции), она действительно будет заставить листы Google обновить страницу еще раз.
Итак, объявление типа: функция myFunction(firstParam,dummy) и затем ее вызов будут такими, как было предложено. Это сработало для меня.
Кроме того, если случайная переменная появляется на всех ваших редактируемых листах, неудобно ограничить одним листом следующее:
function onEdit(e)
{
e.source.getSheetByName('THESHEETNAME').getRange('J1').setValue(Math.random());
}
Здесь есть несколько хороших ответов, которые сделали примерно, но не совсем то, что я хотел. Я собрал несколько таких ответов. Я также использовал некоторые новые функции, которые, вероятно, были недоступны, когда этот вопрос впервые был задан.
Проблема, которую я хотел решить, заключалась в том, чтобы пользовательские функции обновлялись в режиме, близком к реальному, без ручного обновления и чтобы это было прозрачно для пользователя электронной таблицы (но не для программиста расширений).
Я создал новый лист под названием
update varsи разместил здесь именованный диапазон для каждой желаемой детализации обновления. Например, я создал именованный диапазонupdate_minuteдля функций я хотел автоматически обновлять каждую минуту. Я спрятала этот лист для чистоты.Для функций, которые я хотел обновить, я создал дополнительный аргумент
forceUpdateдля этого потребуется вспомогательная переменная (как упоминалось во многих других ответах). Напримерfunction VPRICEUPDATE(fund, forceUpdate)принимает название фонда Vanguard для получения данных о ценах и вспомогательную переменную, которая служит только для аннулирования записи в кэше.Я создал именованную функцию в своей электронной таблице под названием
VPRICEэто делает эту вспомогательную переменную невидимой для пользователя. Требуется один аргумент,fundи псевдонимыVPRICEUPDATE(fund, update_minute). Обратите внимание, что я бы использовалupdate_hourв моем псевдониме, если я хочу ежечасное обновление.Наконец, чтобы все это работало, именованный диапазон
update_XXXнеобходимо обновлять каждый разXXXинтервал. Я сделал это, используя функцию обновления и встроенные триггеры. Например, для минутной детализации я определяю:
function refresh_minute(){
SpreadsheetApp.getActiveSpreadsheet().getRangeByName("update_minute").setValue(Date(Date.now()));
}
Затем я добавил в свой проект триггер для вызоваrefresh_minute()каждую минуту. Для функций, которые нужно обновлять только ежедневно, я повторил это с другим таймером, установленным на триггере. Похоже, вы также можете получить детализацию ежедневных обновлений, используяTODAY()в качестве вспомогательной переменной, но делать что-либо раз в день сложно, поскольку Google допускает ошибку при ссылкеNOW()в формуле.
Сегодня я решил это
- добавление еще одного параметра в мою функцию:
function MY_FUNC(a, b, additional_param) { /* ... */ }
- добавив значение и к этому параметру, ссылаясь на ячейку:
=MY_FUNC("a", "b", A1)
- и установите флажок в указанной ячейке (A1).
Теперь достаточно одного щелчка (по флажку), чтобы принудительно пересчитать мою функцию.
Высочайшая производительность
Если вас устраивает, что ваш скрипт имеет разрешение «Просмотр, редактирование, создание и удаление всех ваших таблиц Google Sheets», существует гораздо более простой и быстрый способ обновить набор ячеек, чем любые другие ответы, упомянутые до сих пор.
Служба Advanced Sheets позволяет записывать сразу в несколько несвязанных диапазонов , используя метод пакетного обновления , и эта операция выполняется очень быстро (по сравнению с.setValues/.setFormulas):
function recalculateCells(recalcData) {
const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
// Effectively, quote the values/formulas - https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
Sheets.Spreadsheets.Values.batchUpdate({
data: recalcData,
valueInputOption: 'RAW',
}, spreadsheetId);
// Restore them as entered by the user - https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
Sheets.Spreadsheets.Values.batchUpdate({
data: recalcData,
valueInputOption: 'USER_ENTERED',
}, spreadsheetId);
}
recalculationDataпредставляет собой массив объектов , где каждый объект имеет два поля интереса:
-
range- ячейка для обновления в нотации A1 , например'My Custom Sheet'!B2:B2 -
values- значение для записи, которое должно представлять собой формулу в ячейке, которую вы хотите обновить (выраженную в виде двумерного массива с одной строкой и одним столбцом, например[[cellFormula]]).
Вы можете заметить, что идентификатор электронной таблицы является излишним, если сценарий запускается для текущей электронной таблицы. Однако,batchUpdateтребуется идентификатор электронной таблицы, поскольку его можно запустить в любой электронной таблице, поэтому вашему скрипту потребуется разрешение «Просмотр, редактирование, создание и удаление всех ваших электронных таблиц Google Sheets». Пожалуйста, проголосуйте за этот вопрос , чтобы разрешить использование службы Advanced Sheets только для текущей электронной таблицы.
Немного ниже производительность
Если вы не можете получить разрешение «Просмотр, редактирование, создание и удаление всех ваших электронных таблиц Google Sheets», вы можете использовать RangeList для хранения ячеек, которые вы хотите обновить, а затем.setValues(),flush(), and finally .setFormulas()andlush()` еще раз, чтобы принудительно пересчитать их.
const activeSS = SpreadsheetApp.getActiveSpreadsheet();
const rangesInSheet = [];
for (const range of sheet.getActiveRangeList().getRanges()) {
const formulas = range.getFormulas();
// Prepare a set of values to store in the cell briefly until
// storing the formulas again. This serves two purposes:
// 1. Provide visual feedback to the user
// 2. More importantly, if the script gets terminated (e.g. due to
// Apps Scripts limits), the original formula is preserved as a value
const recalcValues = [];
for (let row = 0; row < formulas.length; row++) {
recalcValues[row] = [];
for (let col = 0; col < formulas[row].length; col++)
recalcValues[row][col] = `[⚠ RECALCULATING ⚠] ` + formulas[row][col];
}
rangesInSheet.push({
range,
formulas,
recalcValues
});
}
// Flush all recalculation values first, THEN all formulas. This reduces the number of flush() calls from N*2 to 2.
for (const r of rangesInSheet) {
// Storing noop formulas like =NOOP(...) or IFERROR(1/0, ...) didn't work because Sheets apparently caches the
// result of the original formula and returns it right away to the noop. The only way to trigger calling the
// custom functions again was to set the *values* of the cells, then setFormulas() again.
console.log(`Storing raw values for ${r.range.getA1Notation()}...`); // won't display the sheet name
r.range.setValues(r.recalcValues);
}
SpreadsheetApp.flush(); // force changes to be written
for (const r of rangesInSheet) {
console.log(`Restoring original formulas for ${r.range.getA1Notation()}...`); // won't display the sheet name
r.range.setFormulas(r.formulas);
console.log('Formulas recalculated.');
}
// Force restoring original formulas; both flush()es are necessary
SpreadsheetApp.flush();
Я проверил этот подход на скорости более 50 ячеек в секунду, но не пробовал использовать большее количество ячеек. Для разумного количества ячеек это может быть практически так же быстро, как иbatchUpdate()подход.
Каждый раз, когда вы развертываете свое веб-приложение, оно использует одну и ту же версию. Вот почему он не обновляется.
После внесения изменений перейдите в Файл-> Управление версиями и сохраните новую версию. Новая версия появляется. Затем при развертывании выберите версию, которую вы хотите запустить ht tps:https://stackru.com/images/cf901c5c6f4aada8302564a29c4bcea004a004a0.png
Я следил за этим видео с 1:44, и это сработало для меня.
Вы должны использовать определенную функцию обновления, инициализировать переменную «текущего времени» и передать эту постоянно обновляемую переменную вашей пользовательской функции. Затем перейдите в раздел «Триггеры» и настройте «ежеминутный» триггер по времени для функции обновления (или выберите другой временной интервал для обновлений).
Код:
function update() {
var dt = new Date();
var ts = dt.toLocaleTimeString();
var cellVal = '=CustomFunction("'+ ts + '")';
SpreadsheetApp.getActiveSheet().getRange('A1').setValue(cellVal);
}
Просто добавьGOOGLEFINANCE("eurusd")в качестве дополнительного аргумента для вашей пользовательской функции, например:
=myFunction(arg1, arg2, GOOGLEFINANCE("eurusd"))
Если вы написали пользовательскую функцию и использовали ее в электронной таблице в качестве формулы, то каждый раз, когда вы открываете электронную таблицу или изменяете любую ячейку, на которую ссылаются, формула пересчитывается.
Если вы просто хотите продолжать смотреть на электронную таблицу и хотите, чтобы ее значения изменились, подумайте о добавлении синхронизированного триггера, который обновит ячейки. Узнайте больше о триггерах здесь