Formula to get the name of the current sheet in Google Sheets

current_sheet_name.md

How to get the current sheet's name in Google Sheets

Lots of people have suggestions at https://stackoverflow.com/q/45502538. Some of those answers inspired the methods I provide below.

Both methods require a way to parse a sheet name from a cell reference. To do this, we'll add a named function.

Go to "Data > Named functions", click "Add New Function", and input the following:

Function name: PARSE_SHEET_NAME (or whatever you want)

Function description: Parses the sheet name from a cell reference formula.

Argument placeholders: cell_ref

Formula definition:

=LET(quoted_name,REGEXEXTRACT(cell_ref, "^=(.*)!\$?[A-Z]+\$?\d+$"),
IF(LEFT(quoted_name, 1)="'",
  SUBSTITUTE(MID(quoted_name, 2, LEN(quoted_name)-2), "''", "'"),
  quoted_name
))

Additional details, Argument description: cell: The cell reference formula text.

Additional details, Argument example: cell: "=MySheet!A1"

This uses the REGEXEXTRACT function to extract the part between the starting = and the !A1 cell reference at the end. Note that this works with absolute references as well, so the input could be =MySheet!$ABC$1000. Finally, if the sheet name was quoted (because of special characters), we remove them and unescape any single quotes inside the actual sheet name. (If your sheet name is Don't worry, a valid cell reference would be ='Don''t worry'!A1. In general in Google Sheets, "" inside double quotes means a single " and '' inside single quotes means a single '.)

Method 1: Same sheet

This method only uses a single sheet, but requires 2 cells to hold "dummy" information. You will then be able to get the name of the current sheet. Inspiration for this method came from https://stackoverflow.com/a/56911128.

For this example, let's say the sheet name is "MySheet", and we will use A1 and A2 as dummy cell 1 and dummy cell 2, respectively.

In A1, put the formula =NOW(). Then go to File > Settings, go to the Calculation tab, and select "On change and every minute" for Recalculation. This ensures that any new sheet names will be picked up at least every minute, though changes should automatically do this (such as if you change the sheet name).

In A2, put the formula =MySheet!A1. That is, add a reference to dummy cell 1, but make sure to spell out the full sheet name. Of course just =A1 by itself will work for the purposes of displaying the same value, but we specifically want the sheet name in the formula here. If your sheet name has any symbols that are not letters or numbers (such as spaces, punctuation, etc.), then make sure to quote the sheet name (e.g., ='My First Sheet'!A1). If you rename the sheet later, this reference will automatically update.

Then, using the named function we defined above, we can put the following formula in any cell to get the current sheet name:

=PARSE_SHEET_NAME(FORMULATEXT(A2))

This uses the FORMULATEXT function to get the formula in A2 as a string. This is why we wanted the sheet name in the cell reference.

You can put this formula in a third dummy cell and then reference it wherever else you need it. I usually do this in cells A1:A3 then hide the first row.

Dummy cell 1 might be unnecessary; you might just be able to do =MySheet!A1 that references any cell (e.g., A1 can be empty). In my experience this still works fine, but I guess having =NOW() makes it a little safer in case the formula didn't update with the new sheet name.

Method 2: Helper sheet

This method uses a helper sheet. If you have a sheet that holds meta information, configuration values, constants, etc., that will work well with this. Inspiration for this method came from https://stackoverflow.com/a/52471022.

For this example, let's say the sheet name we want is "MySheet", and the dummy sheet name is "DUMMY".

In "DUMMY", in any cell, write the formula:

=PARSE_SHEET_NAME(CELL("address", MySheet!A1))

This uses the CELL function to get the address of the given reference as a string. However, the sheet name will only be included if CELL() is in a different sheet than the reference, which is why we need the dummy sheet.

Instead of MySheet!A1, you can put a reference to any cell in "MySheet". If the sheet is renamed, then this reference will update, and this cell will get the new sheet name. Note that if the referenced cell ever gets deleted, this formula will return an error.

Now in "MySheet", we can get the name of the sheet via ="DUMMY"!A1 or wherever you put the previous formula.

Obviously a downside of this method is that in "MySheet" you need to reference an arbitrary cell in "DUMMY". This can't be helped, due to the way CELL() works.