Is there a Google Sheets formula to put the name of the sheet into a cell?

The following illustration should help:

Solution 1:

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

Solution 2:

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.

Solution 3:

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.

Solution 4:

Here is my proposal for a script which returns the name of the sheet from its position in the sheet list in parameter. If no parameter is provided, the current sheet name is returned.

function sheetName(idx) {
  if (!idx)
    return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  else {
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    var idx = parseInt(idx);
    if (isNaN(idx) || idx < 1 || sheets.length < idx)
      throw "Invalid parameter (it should be a number from 0 to "+sheets.length+")";
    return sheets[idx-1].getName();
  }
}

You can then use it in a cell like any function

=sheetName() // display current sheet name
=sheetName(1) // display first sheet name
=sheetName(5) // display 5th sheet name

As described by other answers, you need to add this code in a script with :

Tools > Script editor