CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetscustom-function
Dženis H.
Dženis H.

Reputation: 7822

How can i tweak this custom function so it updates when data in sheets changes?

Using Google Apps Scripts I was able to select all sheets and exclude the ones that I do not need, but when I change data, it is not updated on my master sheet, where I use this function. How can I tweak this script in order to achieve just that?

I tried adding onEdit() but I didn't quite figure it out.

function SUMALLSHEETS(range, excluded) {
    try {
        var sum = 0,
            ex = (excluded) ? Trim(excluded.split()) : false;
        SpreadsheetApp.getActive()
            .getSheets()
            .forEach(function (s) {
                if (ex && ex.indexOf(s.getName()) === -1 || !ex) {
                    s.getRange(range)
                        .getValues()
                        .reduce(function (a, b) {
                            return a.concat(b);
                        })
                        .forEach(function (v) {
                            sum += (!isNaN(parseFloat(v) && isFinite(v))) ? v : 0;
                        });
                };
            });
        return sum;
    } catch (e) {
        throw e.message;
    }
}

function Trim(v) {
    v = (v === null || typeof v == 'undefined') ? '' : v.toString();
    return v.replace(/^\s\s*/, "")
        .replace(/\s\s*$/, "");
}

Upvotes: 0

Views: 110

Answers (2)

tehhowch
tehhowch

Reputation: 9872

Custom functions such as yours cache their output based on the input parameters, because they are assumed to be deterministic. Only if these parameters change will the custom function recalculate. See the Apps Script documentation on custom functions, along with other custom-function questions asked here.

The solution to your issue - update the sums when a value in a specific range is altered on a non-excluded sheet - is only trivial if you have a single use of the function. Diego's answer provides you with a hint of how you can obtain the value for a given cell after it is edited.

If however you use the custom function in multiple places with different arguments each time, the required case handling for a single on edit will be unmaintainable. The solution is to change your data storage architecture such that related raw data is stored in a central location, and views of that raw data are used and post-processed where needed.

If you'd prefer not to alter the storage architecture, then you could abandon the custom function concept entirely. You'd use a script with hard-coded range+name pairs, and then manually call it to update them when you have finished updating data. You could add a menu option to simplify this manual action, or even write a sidebar that allows manipulation of these pairs without needing to open the Script Editor.

Upvotes: 3

Diego
Diego

Reputation: 9571

It's easy to overthink the onEdit() function. This is a special function (a "simple" trigger) that Google knows what to do with by its mere presence. Simply copy, paste, and save this code. The next time you make an edit to, say, "H1", all of the H1 values will be summed and logged. 

function onEdit(event) {
  var cellA1Notation = event.range.getA1Notation();
  var excluded; // Define how you want
  Logger.log(SUMALLSHEETS(cellA1Notation, excluded));
}

Upvotes: 1

Related Questions