Update gsheets formula range with google script

Question

This was originally posted on google product forums, but haven't gotten much traction there. I'm trying to find a script (or formula method, if it's possible) that can update a formula range either via some action or perhaps a button press.

I've created a spreadsheet to illustrate here.

In this sheet, I have a query formula in the report tab, cell C3, as follows:

=query({'20Feb'!$A:$C;'21Feb'!$A:$C},"select Col3 where Col2 = '" & $B3 & "' ",0)

I would like to update the following:

'20Feb'!$A:$C;'21Feb'!$A:$C

Either by script or by some set of formulas (I've tried indirect with no success, doesn't like multiple tabs).

The idea here is that I could add new tabs with dated ranges, and then press a button to run a script which updates it to include the new tab for reporting purposes. The output would then be:

=query({'19Feb'!$A:$C;'20Feb'!$A:$C;'21Feb'!$A:$C},"select Col3 where Col2 = '" & $B3 & "' ",0)

In a perfect world I could feed the script with a cell input, such that a separate tab for "script update" might have a place where I can write out the new range in full '19Feb'!$A:$C;'20Feb'!$A:$C;'21Feb'!$A:$C, stick that in A1, and tell the script "update to whatever is in cell A1 of the current sheet"

Sorry if that's too many details. Appreciate any help! One person did post a script which copies any text range to a new range, but doesn't specifically work with formulas. I could pop that in here if it's helpful.

It looks like the critical part of the formula are the two sections with the day of the month and the month: 20Feb and 21Feb You can use string formulas in JavaScript to concatenate multiple parts of a string. Then use the setFormula() method of a range to insert a formula. Apps Script documentation txt1 + " " + txt2 — Alan Wells

Wim den Herder Wim den Herder · Accepted Answer · 2017-02-25T00:42:47

You'll need to use escape signs in the formula, but then it works fine:

var nameReportSheet = "Report";
var targetCellNotationForQuery = "C3";

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Generate Formula")
    .addItem("Generate Formule","generateFormula")
    .addToUi();
}

function generateFormula() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var formula = "=query({<DATA>},\"select Col3 where Col2 = '\" & $B3 & \"' \",0)";
  var data = [];
  sheets.forEach(function(sheet) {
    if(!new RegExp(nameReportSheet,"i").test(sheet.getName()))
      data.push("'" + sheet.getName() + "'!$A:$C");
  });
  var newFormula = formula.replace("<DATA>",data.join(";"));
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(nameReportSheet).getRange(targetCellNotationForQuery).setValue(newFormula);
}

Update gsheets formula range with google script

1 Answers