CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formulagoogle-sheets-api
Christiaan Lennaerts
Christiaan Lennaerts

Reputation: 15

Change Google Sheet tab name using Script and GID

Using google App script I try to change google sheet tab names. However as the tabs change names regularly the idea is uisng the GID instead in the script to change the ID. Imagine table below: having a table with a column of the supposed sheet name and then the gid of the tab that should have that name.

Name Event GID
abc jan 12 981289394
BCD jan 18 901985265
ZYX feb 18 1085929622

Is this possible and how would this look like?

Upvotes: 1

Views: 2143

Answers (1)

Tanaike
Tanaike

Reputation: 201358

I think that the sheet name can be changed using "GID" (sheet ID) using Google Apps Script. In your table, when "Name Event" and "GID" are the columns "A" and "B", respectively, and this table is the same with the Spreadsheet including the sheets you want to rename, how about the following script?

The flow of this script is as follows.

  1. Retrieve the table for renaming the sheet name using GID from the sheet.
  2. Create an object for searching the GID.
  3. Rename the sheet name using GIDs retrieved from the source sheet.

In this sample script, in your table, 1st column and 2nd column are retrieved, and the values of 2nd column are used as the GID list. And, the values of 1st column are used as the sheet name you want to rename.

Sample script:

Please copy and paste the following script to the Spreadsheet and please set the sheet name which includes the table in your question. When you use this script, please run myFunction. By this, the values are retrieved from the source sheet, and the sheets are renamed using the retrieved values of Name Event and GID.

function myFunction() {
  const sheetName = "Sheet1"; // Please set the sheet name of the sheet of your table in your question. This is a source sheet.

  // 1. Retrieve the table for renaming the sheet name using GID from the sheet.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName(sheetName);
  const data = srcSheet.getRange("A2:B" + srcSheet.getLastRow()).getValues();

  // 2. Create an object for searching the GID.
  const sheetObj = ss.getSheets().reduce((o, sheet) => Object.assign(o, {[sheet.getSheetId()]: sheet}), {});

  // 3. Rename the sheet name using GIDs retrieved from the source sheet.
  data.forEach(([name, gid]) => {
    if (sheetObj[gid] && !ss.getSheetByName(name)) {
      sheetObj[gid].setName(name);
    }
  });
}

Note:

  • In this sample script, it supposes that "Name Event" and "GID" are the columns "A" and "B", respectively, and the sheets, which have the GIDs, are the same Spreadsheet with the source sheet. Please be careful this. If this was the difference from your actual situation, please tell me.
  • At Google Spreadsheet, the same sheet names cannot be created in a Google Spreadsheet. So please be careful this.

References:

Upvotes: 1

Related Questions