Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Last active July 26, 2023 16:07
Show Gist options
  • Save tanaikech/b8ea7bd7fd87bcd7bb28ddede1781889 to your computer and use it in GitHub Desktop.
Save tanaikech/b8ea7bd7fd87bcd7bb28ddede1781889 to your computer and use it in GitHub Desktop.
Automatic Recalculation of Custom Function on Spreadsheet Part 1

Automatic Recalculation of Custom Function on Spreadsheet Part 1

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. References:

@jason-ransom
Copy link

Thanks for the work-around! Worked perfectly for my use case.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment