我不是Google表格专家.知道公式何时完成可能是一种实用的方法.但是，即使没有它，您也可以注入某些签名"来表示公式结果被更改了吗?天真地，它可能是 concat(< original_formula_output&gt ;,"|||||"，NOW()).

因此等待循环可以是:

  var cell_content_before = FetchingFormulaCellContent();TriggerFormulaUpdate();WHILE(cell_content_before == FetchingFormulaCellContent()AND Waiting_timeout_is_not_reached){Utilities.sleep(1000)`;} 

I have this scenario:

• A sheet "BQ" is connected with BigQuery using 1

• A second sheet "F" contains a Formula which reads data from "BQ"

• An AppsScript that trigger BigQuery data reload

Here is the script sample:

var spreadsheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
SpreadsheetApp.enableAllDataSourcesExecution();
spreadsheet.getRange("A1").getCell(1,1).getDataSourceTables()[0].refreshData();

This appscript perform these actions:

• trigger BigQuery data reload, as mentioned
• "wait" the query to be completed
• "wait" the formula to process new data and update second sheet
• parse data in second sheet and do what it has to do...

Here is when the query is running:

Here is when sheet is updating basing on formula:

I have a problem, I don't know how to do the "wait" part. At first I thought that only SpreadsheetApp.flush(); was enough, but at the end the script starts to read even if query is still running or Spreadsheet is still updating formula, so this command is useless for my need.

At the moment I'm using a fixed Utilities.sleep(20000); basing on the time I saw that is needed for both operation (query and sheet update), but I'm wondering if there is a more high-level approach on perform a "sleep" until the Sheet is fully updated

I'm not a Google Sheet expert. There might be a pragmatical way of just knowing when the formula is finished. But even without it, can you inject certain "signature" to indicate formula result being changed? Naively, it could be concat( <original_formula_output>, "|||||", NOW()).

So the waiting loop can be:

var cell_content_before = FetchingFormulaCellContent();
TriggerFormulaUpdate();
WHILE (cell_content_before == FetchingFormulaCellContent() 
       AND waiting_timeout_is_not_reached) {
  Utilities.sleep(1000)`;
}