How to get sheets name and rename them using Google Script

Renaming all sheets in Google Sheets is a tedious task. If you do it manually, it’ll kill your valuable time. Let’s see How to get sheets name and rename them using Google Script

To change all sheets name in one go, first, you need to get all sheets name then replace it accordingly.

How to get all sheets name in column A

//Get all Sheets name on Active Sheet
function getSheetsName() {
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const name = new Array();
  
  for (var i=0; i<sheets.length;i++)
      name.push([sheets[i].getName()])
      var copy = ss.getActiveSheet().getRange(2, 1, sheets.length);
      copy.setValues(name);      
}

How to rename all sheets based on a Range in column B

//Rename all Sheets based on a Range in Active Sheet
function renameSheets(){
try {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();

  for (var i=0; i<=sheets.length;i++) {
      var copy = ss.getActiveSheet().getRange(2+i, 2).getValue();
      var renameSheet = sheets[i].setName(copy);   
     }
  } catch(e) {}  
}

Full Code :

//Get all Sheets name on Active Sheet
function getSheetsName() {
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const name = new Array();
  
  for (var i=0; i<sheets.length;i++)
      name.push([sheets[i].getName()])
      var copy = ss.getActiveSheet().getRange(2, 1, sheets.length);
      copy.setValues(name);
       
}

//Rename all Sheets based on a Range in Active Sheet

function renameSheets(){

try {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();

  for (var i=0; i<=sheets.length;i++) {
      var copy = ss.getActiveSheet().getRange(2+i, 2).getValue();
      var renameSheet = sheets[i].setName(copy);   
     }
  } catch(e) {}  
}

Follow below Steps to run the script for How to get sheets name and replace them using Google Script

Step 1 – Go to Tools>>Click on Script Editor

Step 2 – Click on Copy Code on the above Code window (see this right image icon)

Step 3 – Delete the standard code

Rename all sheets in Google Sheets

Step 4 – Then Paste the Code

Step 5 –  Click on Run it (refer below image), it’ll ask for the permission. Yes you need to click on ‘unsafe’ then proceed

Rename all sheets in Google Sheets

Step 6 –  You can create buttons using Drawing (Insert>>Drawing) and then assign the script to them

Rename all sheets in Google Sheets

Step 7 –  Click on Buttons to Run the Script

Changes in Code:

If you want to change the range then change it in .getRange()

getRange(row, column, numRows)

the first argument is Row, from which row you want to start

the second argument is Column, which column you want to put Sheets name

the third argument is the total number of rows

Read More:

COMMENTS