CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Oksana Ok
Oksana Ok

Reputation: 555

Google App Function - Recalculate/Refresh values

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.fetch(url);
  var json = response.getContentText("UTF-8");
  var data = JSON.parse(json);
  var price = data;
  SpreadsheetApp.flush();
  return price;
}

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.

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.

P.S. =CHECK(INPUT, **DATE**) is not a solution

Upvotes: 1

Views: 1460

Answers (1)

Tanaike
Tanaike

Reputation: 201653

  • 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();
var range = 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();
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);

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.

Upvotes: 2

Related Questions