Google App功能-重新计算/刷新值 [英] Google App Function - Recalculate/Refresh values

问题描述

一段时间以来，我一直在试图找到解决该问题的方法.

I have tried to find a solution to this problem already for a while.

这是代码

function CHECK(INPUT) {
  var url = "GOOGLE SHEET URL" + INPUT;
  var response = UrlFetchApp.fetch(url);
  var json = response.getContentText("UTF-8");
  var data = JSON.parse(json);
  var price = data;
  SpreadsheetApp.flush();
  return price;
}

此函数从Google工作表中提取值，结果将其显示在另一个Google工作表中.问题在于，当源更改时，目标值不会更改.

This function pulls values from google sheet and, as a result, display it in another google sheet. The problem is that when the source changed the destination value do not change.

我所需要的是一些将强制重新计算Google表格中的值或刷新功能或其他功能，但将使值保持最新状态的

All that I need is something which will force to recalculate values in google sheet, or refresh function or something else but will keep values up-to-date.

PS =CHECK(INPUT, **DATE**)不是解决方案

推荐答案

• 您要使用Google Apps脚本强制重新计算自定义函数.

如果我的理解是正确的，那么该示例脚本如何?我认为您的情况有几个答案.因此，请仅将此作为几个答案之一.

If my understanding is correct, how about this sample script? I think that there are several answers for your situation. So please think of this as just one of several answers.

在此示例脚本中，为了重新计算功能，该功能被相同的功能替换.

In this sample script, in order to recalculate the function, the function is replaced by the same function.

在此示例脚本中，当=CHECK(INPUT)是"Sheet1"的单元格"A1"时，该功能将被相同的功能替换.

In this sample script, when =CHECK(INPUT) is the cell "A1" of "Sheet1", the function is replaced by the same function.

var sheetName = "Sheet1"; // Please modify this for your situation.
var cell "A1"; // Please modify this for your situation.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var range = ss.getSheetByName(sheetName).getRange(cell);
var formula = range.getFormula();
range.clearContent();
SpreadsheetApp.flush();
range.setFormula(formula);

示例脚本2:

在此示例脚本中，"Sheet1"中的所有功能都被同一功能替换.

Sample script 2:

In this sample script, all functions in "Sheet1" are replaced by the same function.

var sheetName = "Sheet1"; // Please modify this for your situation.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var range = ss.getSheetByName(sheetName).getDataRange();
var values = range.getValues();
var formulas = range.getFormulas();
var replacedValues = values.map(function(e, i) {return e.map(function(f, j) {return formulas[i][j] || f})});
range.clearContent();
SpreadsheetApp.flush();
range.setValues(replacedValues);

注意:

• 运行上述示例脚本时，将重新计算该函数.例如，安装了一个示例脚本作为时间驱动触发器，时间驱动触发器会强制更新要重新计算的功能.
• getFormula()
• getFormulas()
• setFormula(formula)
• setFormulas(formulas)
• 时间驱动的触发器
• getFormula()
• getFormulas()
• setFormula(formula)
• setFormulas(formulas)
• Time-driven triggers

如果我误解了你的问题，而这不是你想要的方向，我深表歉意.

If I misunderstood your question and this was not the direction you want, I apologize.

这篇关于Google App功能-重新计算/刷新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！