如何在 Google Apps Script 中刷新工作表的单元格值 [英] How to refresh a sheet's cell value in Google Apps Script

问题描述

我正在试验 Blockspring，它提供了一个 Google 表格插件，例如可以运行一个从网络服务返回数据的函数，例如

I'm experimenting with Blockspring which provides a Google Sheets add-on which can, for example, run a function which returns data from a webservice e.g.

=BLOCKSPRING("get-stock-current-stats", "ticker", "MSFT")

我想刷新单元格的数据，但在文档中没有看到刷新"调用.

I want to refresh a cell's data but don't see a 'refresh' call in the docs.

function onOpen() {
  createTimeDrivenTriggers()
}

function createTimeDrivenTriggers() {
  // Trigger every minute
  ScriptApp.newTrigger('myFunction')
      .timeBased()
      .everyMinutes(1)
      .create();

}

function myFunction() {
  Logger.log('I ran'); // can see this in the logs
  SpreadsheetApp.getActiveSheet().getRange('B4').getValue() //presumably returns a value to the script

}

推荐答案

可以使用 flush() 方法.另请参阅其他人提供的答案.

The flush() method can be used. Also see answers provided by others.

文档

SpreadsheetApp.flush();

引用自文档:

应用所有待处理的电子表格更改

Applies all pending Spreadsheet changes

如果电子表格没有更改，但您想强制重新计算公式，则需要进行更改，然后使用SpreadsheetApp.flush();

If there were no changes to the spreadsheet, but you want to force formulas to recalculate, you will need to make a change, and then use SpreadsheetApp.flush();

例如，您可以从单元格 A1 中获取值，然后为单元格 A1 设置相同的值.因此，不会丢失数据，因为您正在获取和设置相同的值.变化允许.SpreadsheetApp.flush(); 重新计算所有公式.

For example, you could get the value from cell A1, then set the same value to cell A1. So, there is no chance of losing data because you are getting and setting the same value. The change allows. SpreadsheetApp.flush(); to recalculate all the formulas.

文档中有一些关于优化的信息:Google 表格自定义功能优化

The documentation has some information on optimization: Google Sheets Custom Function Optimization

这篇关于如何在 Google Apps Script 中刷新工作表的单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！