Script to rename & reorder sheets based on table

Question

Question 1.

I'm trying to write a script to rename and reorder sheets in a Google Spreadsheet based on a table on one of the sheets. I have been trying different methods for hours to get it to work to no avail. (I am still trying to get the hang of loops)

This script will be in a sheet that I will share with other people who may accidently reorder or possibly rename a sheet.

I have included the current code and a sample file.

NOTE: the sheet names will be completely different and it won't be possible to order them alphabetically.

function OnOpen(){

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();

 for( var i in sheets )
  if(sheets[i].getSheetId() == '978626951'){
  var sheet = ss.getSheets()[i];
   break;
 }

 var sheetData = sheet.getRange(2,1,11,3).getValues();

        for (var a = 0; a < sheetData.length; a++) {
            for (var b = 0; a < sheetData.length; b++){

    var find = sheetData[a][0]; Logger.log(sheets[a].getSheetName());                 

        if(find == sheets[a].getSheetId()) {        
    var temp = ss.getSheets()[a].activate(); 
               ss.moveActiveSheet(sheetData[a][2]);

    }  
   }  
  }
 }

Link to sample spreadsheet with script.

Question No. 2

According to W3schools, it is possible to increment a loop from inside the loop. (code form W3)

var i = 0;
var len = cars.length;
for (; i < len; ) { 
    text += cars[i] + "<br>";
    i++;
} 

However, when I try to do this in Google Script the debugger hangs and I have to refresh. Is this not possible in Google script?

Any help would be greatly appreicated.

Answer 1

I modified your code a little and it seems to work now :

function onOpen(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();

  for( var i in sheets )
    if(sheets[i].getSheetId() == '978626951'){
      var sheet = sheets[i]; // reuse the var sheets
      break;
    }


  //I USED COMMENTED CODE BELOW SINCE I NEEDED TO FIND THE SET OF SHEET IDS FOR MY TEST SPREADSHEET; SO ITS NOT REQUIRED TO RUN ONCE IDs ARE FOUND
  //sheet.appendRow(['-'])
  //  for( var x in sheets)
  //    sheet.appendRow([sheets[x].getSheetId(), sheets[x].getSheetName() ] );
  //  


  var sheetData = sheet.getRange(2,1,11,3).getValues();

  for (var a = 0; a < sheetData.length; a++) {    
    var find = sheetData[a][0]; 

    for(var b = 0 ; b < sheets.length; b++){
      if(find == sheets[b].getSheetId() ) {
        Logger.log(sheets[b].getName());

        sheets[b].activate(); // not : ss.getSheets()[a] 
        ss.moveActiveSheet(sheetData[a][2]);
        sheets[b].setName(sheetData[a][1]); // I added this rename bit

      } 
    } 
  }
}

(but note that for me I even still have to refresh the ss (i.e. F5 in the browser) after having opened the sheet - it seems like the script makes the changes to the spreadsheet, but they are not shown on the page until I refresh)

I tried the code in this spreadsheet: https://docs.google.com/spreadsheets/d/1W7IiEQgsHlWFm7EThyN3lcY6duKh9EW2sQDYFqbwID8/edit?usp=sharing

Your Q2: this works fine in Google Apps Script:

function myFunction() {
  var cars = 'cars', text = '';
  var i = 0;
  var len = cars.length;
  for (; i < len; ) { 
    text += cars[i] + "<br>";
    i++;
  } 
  Logger.log(text)
}