谷歌表计算延迟和相关函数调用 [英] google sheets calulation latency and dependant function calls
问题描述
这是一个用公式对列进行假化的函数:
Here is a function that pupulates a column with a formula:
function fillAccount(lastRow) {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('B1').activate();
spreadsheet.getCurrentCell().setValue(' ');
spreadsheet.getRange('B2').activate()
.setFormula('=ifna(vlookup(C2,Accounts!$A$1:$A$7,1,false),B1)');
spreadsheet.getRange('B3').activate();
var currentCell = spreadsheet.getCurrentCell();
spreadsheet.getRange('B3:B' + lastRow).activate();
spreadsheet.getRange('B2').copyTo(spreadsheet.getActiveRange(),
SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
}
此功能完成后会有一定的等待时间,并且B列中的所有行都填充有计算结果.我想在此函数之后执行另一个函数,但是该函数需要先填充所有行,然后才能执行.这是它在驱动程序脚本中的显示方式:
There is some latency from the time this function completes and all of the rows in column B are populated with the results of the calculation. I want to execute another function after this one but that function need to have all rows populated before it can execute. Here is how it would appear in driver script:
fillAccount(lastrow);
copyAllData(); // this needs to have all rows in column B fully
// populated.
推荐答案
首先,您的函数需要一些清理(可以进一步完善,但这只是一个开始):
First of all, your function needs a bit of cleanup (it can be done refined further, but that's a start):
function fillAccount(lastRow) {
var spreadsheet = getSpread(); //custom method calling the Spreadsheet, change to your logic;
var colB = spreadsheet.getRange('B1:B'+lastRow);
var row1 = colB.getCell(1,1);
var row2 = colB.getCell(2,1);
row1.setValue(' ');
row2.setFormula('=ifna(vlookup(C2,RENAMED!$A$1:$A$7,1,false),B1)');
row2.copyTo(colB.offset(1,0,lastRow-1), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
SpreadsheetApp.flush(); //make sure changes are applied;
}
然后,只需调用第二个函数并确保您访问值以确保计算(如果等待时间相当长(例如,每个公式calc> 1s),则某些公式可能会以#ERROR!
值结尾-如果您要解决这个问题,添加检查==='#ERROR!'
,该检查在遇到这样的值时终止第二个功能并递归地重新启动它):
Then, simply call the second function and make sure you access values to ensure calculation (if you have a pretty long latency (e.g. > 1s for each formula calc), some formulas might end up with #ERROR!
value - if you want to account for that, add check ==='#ERROR!'
that terminates second function upon encountering such a value and recursively restarts it):
/**
* Caller function;
*/
function triggerFill() {
//...your logic and lastRow initialization here;
fillAccount(lastRow);
copyAllData();
}
/**
* Function accessing updated values;
*/
function copyAllData() {
var spreadsheet = getSpread(); //custom method calling the Spreadsheet, change to your logic;
var dataRange = spreadsheet.getDataRange(); //just an example that gets all data;
var values = dataRange.getValues(); //just an example that gets all values;
for(var i=0; i<values.length; i++) {
if(values[i].indexOf('#ERROR!')!==-1) { //for high latency;
Utilities.sleep(t) //change t to number of ms to wait to your liking;
return copyAllData();
}
}
//do other stuff, like cell copy;
}
这篇关于谷歌表计算延迟和相关函数调用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!