Automatic Recalculation of Custom Function on Spreadsheet

Gists

In this report, I would like to introduce a workaround for automatically recalculating custom functions on Spreadsheet.

1. Situation

The sample situation is below. This is a sample situation for this document.

• There are 3 sheets with “sheet1”, “sheet2” and “sheet3” of sheet name in a Spreadsheet.
• Calculate the summation of values of “A1” of each sheet using a custom function.
• Sample script of the custom function is as follows.

function myFunction(e) {
    var r = 0;
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    for (var i in sheets) {
        r += sheets[i].getRange(e).getValue();
    }
    return r;
}

2. Workaround for recalculating

When =myFunction("A1") is put in a cell, the custom function sums each “A1” of “sheet1”, “sheet2” and “sheet3”. But in this case, when “A1” of one of 3 sheets is changed, the custom function is not recalculated.

In order to make spreadsheet recalculate, it is required to make spreadsheet know that the cell value which is used for the custom function was changed.

So, I thought of a workaround. I think that this is one of several workarounds. In order to make spreadsheet know the change of the cell value, it added the information of cell value to =myFunction("A1") as follows.

• When =myFunction("A1") is used to sum each “A1” of “sheet1”, “sheet2” and “sheet3”, it uses =myFunction("A1", sheet1!A1, sheet2!A1, sheet3!A1) instead of =myFunction("A1").

By giving sheet1!A1, sheet2!A1, sheet3!A1, when one of each “A1” is changed, spreadsheet can know the change and recalculates the custom function.

Note :

• The sample script is not required to modify.
  • sheet1!A1, sheet2!A1, sheet3!A1 is not used for the custom function. This is used only to make spreadsheet know the change of cell value.
  • You can see a following sample script for this situation.

function foo(e) {
    return e;
}

function bar() {
    var r = foo(1, 2, 3);
    console.log(r); // >>> 1
}

3. Reference :

• Custom Functions in Google Sheets