Skip to content Skip to sidebar Skip to footer
Home / Google Apps Script / Google Sheets / Javascript

Google App Function - Recalculate/refresh Values

Post a Comment
I have tried to find a solution to this problem already for a while. This is the code function CHECK(INPUT) { var url = 'GOOGLE SHEET URL' + INPUT; var response = UrlFetchApp.f

Solution 1:

  • You want to forcibly recalculate the custom function using Google Apps Script.

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.

Sample script 1:

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();
varrange = ss.getSheetByName(sheetName).getRange(cell);
var formula = range.getFormula();
range.clearContent();
SpreadsheetApp.flush();
range.setFormula(formula);

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();
varrange = 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);

Note:

  • When the above sample scripts are run, the function is recalculated. For example, one of sample scripts is installed as the time-driven trigger, the function you want to recalculate is forcibly updated by the time-driven trigger.

References:

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

Post a Comment for "Google App Function - Recalculate/refresh Values"