等待公式完成后再执行脚本 [英] Wait for formula to be completed before executing script

问题描述

我有这种情况:

• 工作表"BQ"使用

  这是工作表根据公式进行更新的时间:

  我有问题，我不知道该怎么做"等待"部分.起初我以为仅 SpreadsheetApp.flush(); 就足够了，但是最后，即使查询仍在运行或Spreadsheet仍在更新公式，该脚本也开始读取，因此该命令对我的需要.

  目前，我正在使用固定的 Utilities.sleep(20000); ，这是基于我看到的两项操作(查询和工作表更新)所需的时间，但是我想知道在执行工作表完全更新之前，是否有更高级的方法可以执行睡眠"操作

  解决方案

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

  这篇关于等待公式完成后再执行脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！