CODEWITHSUNDEEP
google-sheets
John Hummel
John Hummel

Reputation: 3

Auto refresh custom function for: COUNTIF Statements: Range Across All Sheets + Cell Reference as Criterion

I used this function in Google Sheets:

/**
 * Counts the cells within the range on multiple sheets.
 *
 * @param {"A1:B23"} range The range to monitor (A1Notation).
 * @param {"valueToCount"} countItem Either a string or a cell reference
 * @param {"Sheet1, Sheet2"} excluded [Optional] - String that holds the names of the sheets that are excluded (comma-separated list);
 * @return {number} The number of times the item appears in the range(s).
 * @customfunction
 */

function COUNTALLSHEETS(range, countItem, excluded) {
    try {
        var count = 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) {
                            if (v === countItem) count += 1;
                        });
                };
            });
        return count;
    } catch (e) {
        throw e.message;
    }
}

function Trim(v) {
    return v.toString().replace(/^\s\s*/, "")
    .replace(/\s\s*$/, "");
}

You can use the custom function in your spreadsheet like this:

=COUNTALLSHEETS("B2:B10", "Shaun")

This function works great but it does not auto update if a new Shaun is added to one of the active sheets. How do I edit my function to update when a new variable is added?

Upvotes: 0

Views: 550

Answers (1)

JPV
JPV

Reputation: 27262

That is correct. In order to loop through all sheets to get the same range, that range needs to be passed in as a string. There is simply no other way to do this currently.

However, you can try the following workaround:

  • create a new sheet and call it 'HELPER'. You only need the first cell (A1). So you can delete all other rows and columns. This sheet can also be hidden.

  • Add these scripts to the script editor and save.

function onOpen() {
    SpreadsheetApp.getUi().createMenu('Update')
        .addItem('Force Recalculation', 'forceRecalculation')
        .addToUi()
}

function forceRecalculation() {
    SpreadsheetApp.getActive().getSheetByName('HELPER').getRange(1, 1).setValue(Math.floor(Math.random() * (100 - 1 + 1)) + 1);
}

  • Add a reference to the cell 'HELPER!A1 to the custom formula as the LAST parameter.

=COUNTALLSHEETS("B2:B10", "Shaun", 'Helper'!A1)

  • Reopen the spreadsheet. You should now have an extra menu item in the spreadsheet menu called 'Update'.

Open the menu-item and click 'Force Recalculation'. No, every time a value in the range gets updated, just click the menu item. That should force a recalculation of all the custom formulas that have a reference to the cell 'HELPER'!A1.

See if that works?

Best regards,

JPV

Upvotes: 1

Related Questions