Google Sheets – Insert Sheet Names into Cells

Posted on | by Prolific Oaktree

See the linked video to learn how to insert the sheet names into cells in Google Sheets. This page is the for the text that accompanies the YouTube video.



The code for the regular expression is =IF(TODAY()=TODAY(), REGEXREPLACE(CELL(＂address＂,'Detail 1'!A1),＂'?([^']+)'?!.*＂,＂$1＂), ＂＂). The text in the box below is meant to be copy and pasted for the custom functions that were used in this sheet. The text in this Doc is the same, grab it from here if it’s easier.

//Return the current sheet name.
function SheetName() {
return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

//Return all current sheet names.
function SheetNames() {
var out = new Array()
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
for (var i=0 ; i<sheets.length ; i++) out.push( [ sheets[i].getName() ] )
return out
}

//Return specified sheet name
function SheetNumber(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)

return sheets[idx-1].getName();
}
}

0 thoughts on “Google Sheets – Insert Sheet Names into Cells”

  • jaclyn says:

    Hello – the regexreplace formula is returning “$A$1” for the sheet name vs the actual sheet name. This is the formula I am using.
    =IF(TODAY()=TODAY(), REGEXREPLACE(CELL(“address”,’Tab1′!A1),”‘?([^’]+)’?!.*”,”$1″), “”)

    • Prolific Oaktree says:

      Sorry, I can’t give a better answer, but your best bet is to look at the comments on the YouTube video and ask there is you don’t find the answer. I modified the text of this page so there is a link to the video instead of an embed. Just follow the link to find it.

  • puk says:

    If anyone received a syntax error on line 22 of the code above.

    Simply delete all the existing ” (quotation marks) and retype them, on line 22:

    throw "Invalid parameter (it should be a number from 0 to "+sheets.length+")";

    This will resolve any issues.

    Thanks for this code @ Prolific Oaktree

  • Belal Youssef says:

    hello ! is it possible to create a var with the sheetname and call it back using =IMPORTANCE…… ? i’m combining two different sheets ,it’s for cars that we fix,there’s a col for spare parts which i link with a different sheet file for each car individually.so is it possible to do that!

  • Carmen Hunt says:

    I’m using google docs sheets to create a daily sign in form. I want the sheet names to appear on the actual sign in sheet. Each sheet is name with the day and date. and I’d like that to show on the sheet. can you help?

  • Carmen Hunt says:

    this is what i used:

    =IF(TODAY()=TODAY(),REGEXREPLACE(cell(“address”,July2!A1),”‘?([^’]+)’?!.*”,”$1″), “”)

    This is what i got:

    $A$1

    • Jeremy says:

      Sounds to me like you are trying to use that formula on the same sheet you want to display the sheet name in. It will only work if the formula is pointing at a different sheet. In other words, you have to have the formula in Sheet1 referencing a cell in Sheet2 and it will display “Sheet2.” If you have the formula in Sheet1 referencing a cell in Sheet1 you will get the absolute reference to the cell.

      You may need a work-around if you want to display the sheet name on the same sheet.
      The simplest way is to use the script editor to create your own function:

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

      Or you can use the above formula you started with but you’ll have to have it on a second sheet looking at the one you want to display and then reference that cell from your original sheet. In other words, if you want to display the shet name of Sheet1 on Sheet1 you’ll have to have your formula on Sheet2 pointing at Sheet1 and then use a simple cell reference on Sheet1 pointing at the cell that has the sheet name in Sheet2.

      Hope that’s not too confusing.

      • J says:

        The problem with the custom formula in app script is that it wont update if the sheet name changes.

        I even tried attaching it to a button with a .clearcontent() before it and it didn’t work. It kept returning the original sheet name which for me was copy of template. I guess i could just add an IMPORTANT tag to the instructions but id rather just have a function that works on renaming that is self contained.

        function test() {
        var ss = SpreadsheetApp.getActiveSheet();
        ss.getRange(‘Y5’)..clearContent();
        ss.getRange(‘Y5’).setValue(‘=sheetname()’);
        }

      • Stefan says:

        The way I want to use it is to have 1 “original” sheet, that I then make multiple duplicates of and afterwards rename the tabs on the sheets. None of these presented functions do this.

        However I found a hackish way to do it using
        function sheetName(){return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();}

        If I make eg 10 copies, and change all the new sheet names, I can then afterwards go into the script editor and re-save the script file (with no changes made) and all the sheets are then updated to the correct new name.

        If someone has a solution for an automatic, same sheet, update, please let us know. 🙂

Leave a Reply