这是我解决问题的方法:
在电子表格中,转到顶部菜单 > 单击“工具”>,然后单击“脚本编辑器”并添加以下脚本:
这是调用您的数据的脚本,例如XML。请使用您自己的信息更新脚本。 YOUR-SHEET-NAME,是选项卡名称,例如“价格”。
function getData() {
var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("YOUR-
SHEET-NAME");
var queryString = Math.random();
var cellFunction = '=ImportXML("https://yoururl-xml.php","//trade/price")';
var range = sheetName.getRange('A2');
range.clearContent(); // You can also use range.setFormula("");
SpreadsheetApp.flush();
range.setFormula(cellFunction);
}
在该脚本下方,添加以下脚本,更多信息请参见此页面:定期刷新 IMPORTXML() 电子表格函数
YOUR-SHEET-ID 是电子表格网址中的长数字,例如1YTB12xSTMSNdoT_S1U67MtOUDTf6n4OL2tJLnTNAXYZ
function RefreshImports() {
var lock = LockService.getScriptLock();
if (!lock.tryLock(5000)) return; // Wait up to 5s for previous refresh to end.
var id = "YOUR-SHEET-ID";
var ss = SpreadsheetApp.openById(id);
var sheet = ss.getSheetByName("YOUR-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();
}
这是两个脚本的屏幕截图:
然后添加计时器,转到顶部菜单,单击时钟并添加触发器。确保选择正确的功能,即 RefreshImports。
Done!