Периодически обновлять функцию электронных таблиц IMPORTXML()
У меня есть большой лист с около 30 importxml функции, которые получают данные с сайта, который обновляется обычно два раза в день.
Я хотел бы запустить importxml функция своевременно (каждые 8 часов) для моей таблицы Google, чтобы сохранить данные на другом листе. Сохранение уже работает, а обновление - нет!
Я прочитал в обновлении строки Google Spreadsheet, что оно может запускаться каждые 2 часа, однако я не верю, что это правда, потому что с тех пор, как я добавил его в свой лист, ничего не изменилось или не обновилось, когда электронная таблица НЕ открыта.
Как я могу "вызвать" importxml работать в моей таблице Google легко, так как у меня много importxml функции в нем?
3 ответа
Вопрос об обновлении строки таблицы Google и ее ответы относятся к "старым таблицам", поведение которых отличалось от версии Google Sheets 2015 года. Нет автоматического обновления контента с помощью "Новых листов"; изменения оцениваются только в ответ на изменения.
Хотя Sheets больше не предоставляет эту возможность изначально, мы можем использовать скрипт для обновления формул "import" (IMPORTXML, IMPORTDATA, IMPORTHTML а также IMPORTANGE).
Служебный скрипт
Для периодического обновления формул IMPORT установите эту функцию как управляемый по времени триггер.
Предостережения:
- Функция импорта. Изменения формулы, внесенные в электронную таблицу другими сценариями или пользователями в течение периода обновления,МОГУТ БЫТЬ ЗАПИСАНЫ.
- Перекрывающиеся обновления могут сделать вашу электронную таблицу нестабильной. Чтобы смягчить это, служебный скрипт использует
ScriptLock, Это может конфликтовать с другим использованием этой блокировки в вашем скрипте.
/**
* Go through all sheets in a spreadsheet, identify and remove all spreadsheet
* import functions, then replace them a while later. This causes a "refresh"
* of the "import" functions. For periodic refresh of these formulas, set this
* function up as a time-based trigger.
*
* Caution: Formula changes made to the spreadsheet by other scripts or users
* during the refresh period COULD BE OVERWRITTEN.
*
* From: https://stackru.com/a/33875957/1677912
*/
function RefreshImports() {
var lock = LockService.getScriptLock();
if (!lock.tryLock(5000)) return; // Wait up to 5s for previous refresh to end.
// At this point, we are holding the lock.
var id = "YOUR-SHEET-ID";
var ss = SpreadsheetApp.openById(id);
var sheets = ss.getSheets();
for (var sheetNum=0; sheetNum<sheets.length; sheetNum++) {
var sheet = sheets[sheetNum];
var dataRange = sheet.getDataRange();
var formulas = dataRange.getFormulas();
var tempFormulas = [];
for (var row=0; row<formulas.length; row++) {
for (col=0; col<formulas[0].length; col++) {
// Blank all formulas containing any "import" function
// See https://regex101.com/r/bE7fJ6/2
var re = /.*[^a-z0-9]import(?:xml|data|feed|html|range)\(.*/gi;
if (formulas[row][col].search(re) !== -1 ) {
tempFormulas.push({row:row+1,
col:col+1,
formula:formulas[row][col]});
sheet.getRange(row+1, col+1).setFormula("");
}
}
}
// After a pause, replace the import functions
Utilities.sleep(5000);
for (var i=0; i<tempFormulas.length; i++) {
var cell = tempFormulas[i];
sheet.getRange( cell.row, cell.col ).setFormula(cell.formula)
}
// Done refresh; release the lock.
lock.releaseLock();
}
}
Я внес несколько поправок в ответ Могсдада:
- Исправлено
releaseLock()размещение вызова - Обновляет (или добавляет) параметр строки запроса в URL в функции импорта (в отличие от сохранения, удаления, ожидания 5 секунд и восстановления всех соответствующих формул)
- Работает на конкретном листе в вашей электронной таблице
- Показывает время последнего обновления
...
function RefreshImports() {
var lock = LockService.getScriptLock();
if (!lock.tryLock(5000)) return; // Wait up to 5s for previous refresh to end.
var id = "[YOUR SPREADSHEET ID]";
var ss = SpreadsheetApp.openById(id);
var sheet = ss.getSheetByName("[SHEET NAME]");
var dataRange = sheet.getDataRange();
var formulas = dataRange.getFormulas();
var content = "";
var now = new Date();
var time = now.getTime();
var re = /.*[^a-z0-9]import(?:xml|data|feed|html|range)\(.*/gi;
var re2 = /((\?|&)(update=[0-9]*))/gi;
var re3 = /(",)/gi;
for (var row=0; row<formulas.length; row++) {
for (var col=0; col<formulas[0].length; col++) {
content = formulas[row][col];
if (content != "") {
var match = content.search(re);
if (match !== -1 ) {
// import function is used in this cell
var updatedContent = content.toString().replace(re2,"$2update=" + time);
if (updatedContent == content) {
// No querystring exists yet in url
updatedContent = content.toString().replace(re3,"?update=" + time + "$1");
}
// Update url in formula with querystring param
sheet.getRange(row+1, col+1).setFormula(updatedContent);
}
}
}
}
// Done refresh; release the lock.
lock.releaseLock();
// Show last updated time on sheet somewhere
sheet.getRange(7,2).setValue("Rates were last updated at " + now.toLocaleTimeString())
}
Чтобы ответить на ваш вопрос для простого "триггера", чтобы заставить функцию перезагрузить:
добавьте дополнительный неиспользуемый параметр к URL-адресу, который вы загружаете, одновременно ссылаясь на ячейку для значения этого параметра. Как только вы измените содержимое этой ячейки, функция перезагрузится.
пример:
importxml("http://www.example.com/?noop=" & $A$1,"...")
к сожалению, вы не можете поместить функцию вычисления даты в указанную ячейку, которая выдает ошибку, что это недопустимо.
Вы также можете поместить каждую формулу XML в качестве комментария в соответствующие ячейки и записать макрос, чтобы скопировать и вставить его в ту же ячейку. Позже используйте скрипты, а затем функцию триггера, чтобы запланировать этот макрос. 
