Google Spreadsheet App Script不会等待结果加载 [英] Google Spreadsheet App Script will not wait for results to load

问题描述

我有一个小脚本，我想要做的是从'Sheet 1'写入一个值到'Sheet 2'。等待结果加载并比较单元格以查看它是否高于10％。我在电子表格中有一些 = importhtml 函数，并且需要一段时间才能加载。我试过睡觉，公用设施睡觉，并冲洗。

I have a small script and what I'm trying to do is to write one value from 'Sheet 1' to 'Sheet 2'. Wait for the results to load and compare the cells to see if it is above 10% or not. I have some =importhtml functions in the spreadsheet and it takes along time to load. I've tried sleep, utilities sleep, and flush. None have been working, maybe because I might be putting it in the wrong area..

function compareCells() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var listSheet = ss.getSheetByName('Stocks');
    var dataSheet = ss.getSheetByName('Summary');
    var listSheetLastRow = listSheet.getLastRow();
    var currRow = 1;
    for (i = 1; i <= listSheetLastRow; i++) {
        if (listSheet.getRange(1, 3).getValue() == 'asd') {
            var ticker = listSheet.getRange(currRow, 1).getValue();
            dataSheet.getRange(5, 4).setValue(ticker);
            var value1 = dataSheet.getRange(15, 4).getValue();
            var value2 = dataSheet.getRange(22, 4).getValue();
            SpreadsheetApp.flush();
            if (value1 > 0.10 && value2 > 0.10) {
                listSheet.getRange(currRow, 8).setValue('True');
                listSheet.getRange(currRow, 9).setValue(value1);
                listSheet.getRange(currRow, 10).setValue(value2);
            } else {
                listSheet.getRange(currRow, 8).setValue('False');
            }
        } else {
            Browser.msgBox('Script aborted');
            return null;
        }
        currRow++;
    }
}

推荐答案

在工作表中使用= IMPORTHTML（）函数并不重要，最简单的方法是在Apps脚本中使用 UrlFetchApp 。以这种方式获取数据将导致脚本阻塞，直到返回HTML响应。您还可以创建基于时间的触发器，以便您的数据始终保持新鲜，并且用户在查看表单时无需等待URL提取。

If it is not important that you use the =IMPORTHTML() function in your sheet, the easiest way to do this will be to use UrlFetchApp within Apps Script. Getting the data this way will cause your script to block until the HTML response is returned. You can also create a time-based trigger so your data is always fresh, and the user will not have to wait for the URL fetch when looking at your sheet.

一次您可以获得HTML响应，您可以在脚本中执行与Sheet1中相同的所有处理。如果由于Sheet1中有复杂的处理而无法工作，您可以：

Once you get the HTML response, you can do all of the same processing you'd do in Sheet1 within your script. If that won't work because you have complex processing in Sheet1, you can:

1. 使用 UrlFetchpApp.fetch（ 'http://sample.com/data.html'）检索您的数据


2. 将数据写入Sheet1


3. 调用 SpreadsheetApp.flush（）来强制执行写入操作和任何后续处理


4. 按上面的示例进行操作

1. use UrlFetchpApp.fetch('http://sample.com/data.html') to retrieve your data
2. write the data to Sheet1
3. call SpreadsheetApp.flush() to force the write and whatever subsequent processing
4. proceed as per your example above

通过在脚本中顺序处理这些步骤，您可以保证在数据出现之前不会发生后续步骤。

By handling these steps sequentially in your script you guarantee that your later steps don't happen before the data is present.

这篇关于Google Spreadsheet App Script不会等待结果加载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！