Get the spreadsheet name, the current sheet's name and the list of sheets in Google Sheets using Apps Script

In this tutorial, I'll show you how to get the name of the Google Sheets spreadsheet and the names of the sheets it contains via Apps Script. Since these are custom functions, you can also use them in formulas just like you would use any other function in Google Sheets.

Prerequisites

This tutorial assumes that you're familiar with the basics of Apps Script and Google Sheets. In particular, you should be familiar with:

Note

If you're new to Apps Script, I've created a series of introductory tutorials that will help you learn coding using Google Sheets and Apps Script.

How to get the name of the Google Sheets spreadsheet using Apps Script?

To get the name of the Google Sheets spreadsheet, we need to first get a reference to the spreadsheet and then use the getName() method of the Spreadsheet object to get its name.

/**
 * Returns the name of the Google Sheets spreadsheet.
 * 
 * @return The name of the Google Sheets spreadsheet.
 * @customfunction
 */
function SPREADSHEETNAME() {
  let ss = SpreadsheetApp.getActive();
  return ss.getName();
}

How to get the name of the currently active sheet using Apps Script?

To get the name of the currently active sheet, we need to first get a reference to the spreadsheet. Then we use the getActiveSheet() method of the Spreadsheet object to get the sheet that is active. Finally, we use the getName() method of the Sheet object to get its name. 

/**
 * Returns the name of the active sheet.
 * 
 * @return The name of the active sheet.
 * @customfunction
 */
function SHEETNAME() {
  let activeSheet = SpreadsheetApp.getActiveSheet();
  return activeSheet.getName();
}

How to get the names of all the sheets in the Google Sheets spreadsheet using Apps Script?

To get the names of all the sheets in a Google Sheets spreadsheet, we need to first get a reference to the spreadsheet. Then we need to get all the sheets in it using the getSheets() method of the Spreadsheet object.. Finally, we get the name of each sheet using the getName() method of the Sheet object.

/**
 * Returns the names of all sheets.
 * 
 * @return The name of all sheets in the Google Sheets spreadsheet.
 * @customfunction
 */
function ALLSHEETNAMES() {
  let ss = SpreadsheetApp.getActive();
  let sheets = ss.getSheets();
  let sheetNames = [];
  sheets.forEach(function (sheet) {
    sheetNames.push(sheet.getName());
  });
  return sheetNames;
}

Conclusion

In this tutorial you learned how to programmatically get the name of the Google Sheets spreadsheet and the names of the sheets in it using Apps Script.

Hope you found this tutorial helpful. Thanks for reading!

Sign up to be notified when I publish new content

By signing up you agree to the Privacy Policy & Terms.