1

I'm trying to add some "validation" to a google sheet to prevent errors from creeping in as the sheet is edited (think assertion when programming).

Because of the way my sheet is structured to do this validation it would be useful to know the number of sheets in the current workbook. Is it possible to do this?

Approaches tried:

Excel-compatible function

Excel provides the functions =SHEETS() for this purpose (https://exceljet.net/excel-functions/excel-sheet-function). But this function doesn't exist in sheets

Custom function

This post on reddit suggests using the following functions in scripts:

function getNumSheets() {
  return SpreadsheetApp.getActiveSpreadsheet().getSheets().length;
}

This works - but it doesn't get updated when the number of sheets change - unless you force it to recalculate.

I'm leaning towards doing my validation "in code" rather than in the equation language and running it every time I want to do my tests....

Improve this question

1 Answer 1

Reset to default
1

In Google Sheets, custom functions are recalculated when one of its parameters change. NOTE: They don't allow volatile functions (NOW, TODAY, RANDOM, etc.)

In order to get the current number of sheets on something close to real time, instead of using a custom function you might use a change installable trigger (usually referred as onChange) as it's triggered, among other cases, when a new sheet is inserted and when a sheet is deleted.

Resources

Related

Improve this answer
4
  • Taht seems like a reasonable idea... can I get the function to invalidate "on change" or would I have to dig around in sheet and cause a recalculation myself?
    – Att Righ
    Commented Jun 16, 2021 at 14:53
  • 1
    Links fixed. Triggers can't be "invalidated", what is usually done is to add if..else statements to control what should be executed when the required conditions are met.
    – Rubén - Volunteer Moderator - ♦
    Commented Jun 16, 2021 at 14:56
  • Thanks. By invalidated I mean "recalculate all the cells which use this function". It feels more natural to have =numSheets() in a sell somewhere, rather than have cells magically update on change.
    – Att Righ
    Commented Jun 16, 2021 at 15:00
  • 1
    I don't think that "invalidated" is a correct term for what are trying to say... anyway, you might use the onChange trigger to search for all the cells having that function to "force a recalculation" by changing a parameter (it could be a sequential number, toggling a value i.e. changing 1 to 0 then on the next execution change 0 to 1..). Another way commonly used is to clear the cell having the function, execute flush then add again the formula. If you need further help, please post a new question.
    – Rubén - Volunteer Moderator - ♦
    Commented Jun 16, 2021 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.