CODEWITHSUNDEEP
loopsgoogle-apps-scriptgoogle-sheets
Ben Spear
Ben Spear

Reputation: 51

Renaming sheets with a loop but avoiding duplicate names

I have a spreadsheet that has a list of motorcycle tours.

I need to name the sheets according to this list and rename the sheets if the list changes. The sheet name will be a concatenation of tour name and departure index.

The code below works well except, if there are 2 departures of identical tours the following exception occurs:

'A sheet with the name ‘XXX’ already exists.'

If I reverse the loop (i-- instead of i++) then it works.

I want to alter the code to loop in one direction, then if the exception occurs, loop in the other direction.

Edit:

So, if I have a list of tours:

Tour A 1, Tour B 1, Tour C 1, Tour A 2, Tour D 1, Tour E 1,

and I want to change 'Tour B 1' into another instance of 'Tour A'. Then 'Tour B 1' would change to 'Tour A 2' and the existing 'Tour A 2' would change to 'Tour A 3'.

Looping from the top, the error occurs because 'Tour B 1' is trying to change to 'Tour A 2' whilst the original 'Tour A 2' still exists.

However, looping from the bottom, it works because the existing 'Tour A 2' changes to 'Tour A 3' first, and by the time the loop reaches 'Tour B 1' it can alter to 'Tour A 2' as there is no longer an existing sheet with that name!

I hope this is clear? The inverse is true if a sheet name is changed near the bottom of the list. A ++ loop will work but a -- loop won't.

So I would like to try a ++ loop followed by a -- loop if the error occurs...

// Triggered by onEdit

function renameSheets() {

// Sets the sheet names to match season schedule

  var tourNames = seasonScheduleTab.getRange('D5:D34').getValues();
  var departures = seasonScheduleTab.getRange('C5:C34').getValues();

  var sheets = ss.getSheets();


// I think I need an if statement above my for loop to change the loop to (var i = 30; i > 0; i --) if an exception occurs?

  for (var i = 1; i < 31; i ++){

    if (tourNames[i-1] !='') {

    sheets[i].setName(tourNames[i-1] + ' ' + departures[i-1]);
      }

    }

  }

Upvotes: 1

Views: 399

Answers (1)

Tanaike
Tanaike

Reputation: 201553

I believe your goal as follows.

  • You want to change the sheet names on a Google Spreadsheet using tourNames[i-1] + ' ' + departures[i-1] retrieved by var tourNames = seasonScheduleTab.getRange('D5:D34').getValues(); and var departures = seasonScheduleTab.getRange('C5:C34').getValues();
  • The names for updating the sheet names are the unique values for all names.
  • When the loop is run with the forward direction, when an error occurs, you want to change the loop to the reverse direction.

For this, how about this answer?

Pattern 1:

In this pattern, your goal that when the loop is run with the forward direction, when an error occurs, you want to change the loop to the reverse direction is achieved. For this, "try...catch" is used. But in this case, when the error occurs with the both loop directions, the error occurs. Please be careful this.

Modified script:

When your script is modified, please modify as follows.

From: 
for (var i = 1; i < 31; i ++){

  if (tourNames[i-1] !='') {

  sheets[i].setName(tourNames[i-1] + ' ' + departures[i-1]);
    }

  }

}
To: 
try {
  for (var i = 1; i < 31; i ++) {
    if (tourNames[i-1] !='') {
      sheets[i].setName(tourNames[i-1] + ' ' + departures[i-1]);
    }
  }
} catch(e) {
  for (var i = 30; i > 0; i --) {
    if (tourNames[i-1] !='') {
      sheets[i].setName(tourNames[i-1] + ' ' + departures[i-1]);
    }
  }
}

Pattern 2:

In this pattern, the following flow is used.

  1. Check the duplicate names. When the duplicate names are existing, the script is stopped.
  2. Change all sheet names with the temporal names without including the names for changing the sheet names.
  3. Change all sheet names with the names for changing the sheet names.

By this flow, I think that it is not required to change the loop direction. In this case, in order to reduce the process cost, Sheets API of Advanced Google services is used.

Modified script:

The sample script is as follows. Before you use this, please enable Sheets API at Advanced Google services.

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const seasonScheduleTab = ss.getSheetByName("###");  // Please set the sheet name.

  const spreadsheetId = ss.getId();
  const sheetNames = seasonScheduleTab.getRange('C5:D34').getValues().map(([c, d]) => `${c} ${d}`);

  // 1. Check the duplicate names. When the duplicate names are existing, the script is stopped.
  if (sheetNames.length > [...new Set(sheetNames)].length) {
    throw new Error("Duplicated names are existing.");
  }

  const sheets = ss.getSheets();
  let sheetIds = [];
  for (let i = 1; i < 31; i++) {
    sheetIds.push(sheets[i].getSheetId());
  }
  const tempName = "tempSheetName_";  // This is used for changing the sheet name to the temporal names.

  // 2. Change all sheet names with the temporal names without including the names for changing the sheet names.
  let reqs = sheetIds.map((id, i) => ({updateSheetProperties: {properties: {sheetId: id, title: `${tempName}${i + 1}`}, fields: "title"}}));

  // 3. Change all sheet names with the names for changing the sheet names.
  reqs = reqs.concat(sheetIds.map((id, i) => ({updateSheetProperties: {properties: {sheetId: id, title: `${sheetNames[i]}`}, fields: "title"}})));

  // Request to Sheets API with the created request body.
  Sheets.Spreadsheets.batchUpdate({requests: reqs}, spreadsheetId);
  SpreadsheetApp.flush(); // I'm not sure whether this is required to be used.
}

Note:

  • In this case, at least, it is required to include 31 sheets in the Google Spreadsheet. Please be careful this.
  • Please use this script with V8.

References:

Upvotes: 3

Related Questions