Is there a Google Sheets formula to put the name o

Question

The following illustration should help:

Answer 1

You have 2 options, and I am not sure if I am a fan of either of them, but that is my opinion. You may feel differently:

Option 1: Force the function to run.

A function in a cell does not run unless it references a cell that has changed. Changing a sheet name does not trigger any functions in the spreadsheet. But we can force the function to run by passing a range to it and whenever an item in that range changes, the function will trigger.

You can use the below script to create a custom function which will retrieve the name:

function mySheetName() {
  var key = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  return key;
}

and in the cell place the following:

=mySheetName(A1:Z)

Now if any value in a cell in that passed range changes the script will run. This takes a second to run the script and sets a message in the cell each time any value is changed so this could become annoying very quickly. As already mentioned, it also requires a change in the range to cause it to trigger, so not really helpful on a fairly static file.

Option 2: Use the OnChange Event

While the run time feels better than the above option, and this does not depend on a value changing in the spreadsheet's cells, I do not like this because it forces where the name goes. You could use a Utilities sheet to define this location in various sheets if you wish. Below is the basic idea and may get you started if you like this option.

The OnChange event is triggered when the sheet name is changed. You can make the code below more sophisticated to check for errors, check the sheet ID to only work on a given sheet, etc. The basic code, however, is:

function setSheetName(e) {
  var key = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange('K1').setValue(key);
}

Once you have saved the code, in the script editor set the Current Project's On Change Trigger to this function. It will write the sheet name to cell K1 on any change event. To set the trigger, select Current project's triggers under the Edit menu.

Answer 2

Here is what I found for Google Sheets:

To get the current sheet name in Google sheets, the following simple script can help you without entering the name manually, please do as this:

1. Click Tools > Script editor

2. In the opened project window, copy and paste the below script code into the blank Code window, see screenshot:

......................

function sheetName() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

Then save the code window, and go back to the sheet that you want to get its name, then enter this formula: =sheetName() in a cell, and press Enter key, the sheet name will be displayed at once.

See this link with added screenshots: https://www.extendoffice.com/documents/excel/5222-google-sheets-get-list-of-sheets.html

Answer 3

I have a sheet that is made to used by others and I have quite a few indirect() references around, so I need to formulaically handle a changed sheet tab name.

I used the formula from JohnP2 (below) but was having trouble because it didn't update automatically when a sheet name was changed. You need to go to the actual formula, make an arbitrary change and refresh to run it again.

=REGEXREPLACE(CELL("address",'SHEET NAME'!A1),"'?([^']+)'?!.*","$1")

I solved this by using info found in this solution on how to force a function to refresh. It may not be the most elegant solution, but it forced Sheets to pay attention to this cell and update it regularly, so that it catches an updated sheet title.

=IF(TODAY()=TODAY(), REGEXREPLACE(CELL("address",'SHEET NAME'!A1),"'?([^']+)'?!.*","$1"), "")

Using this, Sheets know to refresh this cell every time you make a change, which results in the address being updated whenever it gets renamed by a user.

Answer 4

I got this to finally work in a semi-automatic fashion without the use of scripts... but it does take up 3 cells to pull it off. Borrowing from a bit from previous answers, I start with a cell that has nothing more than =NOW() it in to show the time. For example, we'll put this into cell A1...

=NOW()

This function updates automatically every minute. In the next cell, put a pointer formula using the sheets own name to point to the previous cell. For example, we'll put this in A2...

='Sheet Name'!A1

Cell formatting aside, cell A1 and A2 should at this point display the same content... namely the current time.

And, the last cell is the part I'm borrowing from previous solutions using a regex expression to pull the fomula from the second cell and then strip out the name of the sheet from said formula. For example, we'll put this into cell A3...

=REGEXREPLACE(FORMULATEXT(A2),"='?([^']+)'?!.*","$1")

At this point, the resultant value displayed in A3 should be the name of the sheet.

From my experience, as soon as the name of the sheet is changed, the formula in A2 is immediately updated. However that's not enough to trigger A3 to update. But, every minute when cell A1 recalculates the time, the result of the formula in cell A2 is subsequently updated and then that in turn triggers A3 to update with the new sheet name. It's not a compact solution... but it does seem to work.

Answer 5

Not using script:

I think I've found a stupid workaround using =cell() and a helper sheet. Thus avoiding custom functions and apps script.

=cell("address",[reference]) will provide you with a string reference (i.e. "$A$1") to the address of the cell referred to. Problem is it will not provide the sheet reference unless the cell is in a different sheet!

So:

where

This also works for named sheets. Then by all means adjust to work for your use case.

Source: https://docs.google.com/spreadsheets/d/1_iTD6if3Br6nV5Bn5vd0E0xRCKcXhJLZOQqkuSWvDtE/edit#gid=1898848593

Answer 6

If you reference the sheet from another sheet, you can get the sheet name using the CELL function. You can then use regex to extract out the sheet name.

=REGEXREPLACE(CELL("address",'SHEET NAME'!A1),"'?([^']+)'?!.*","$1")

update: The formula will automatically update 'SHEET NAME' with future changes, but you will need to reference a cell (such as A1) on that sheet when the formula is originally entered.