Script to Sort Sheets by Name in Google Sheets Spreadsheet

1
votes

I use a Google Sheets spreadsheet to manage the files that I am working on. I have roughly 60-70 files at any given time, each of which has a unique file number. Each file is represented in my spreadsheet by a unique sheet named by its file number. File numbers fit the following format: [#]-[##]-[####] (e.g. 5-18-0040). I also have a sheet named "Files" which is a list of all of those sheets and a few other hidden sheets with operations contained in them.

I have cobbled together a script that I had thought would sort those sheets by increasing file number (e.g. 1-15-0023, 2-16-1924, 3-14-2012, 5-17-0040) and then pushes the Files sheet and those hidden sheets to the start. However, it doesn't work. It definitely moves sheets around somewhat, but I still end up with sheets out of order.

Can anyone take a look at this code and tell me what could be failing? For instance, do I need to specify the names as integers (and if so, how?) or is there something else that could be preventing this script from working?

function sortSheets () {
  var sheetNameArray = [];
  var sheets = ss.getSheets(); 
  for (var i = 0; i < sheets.length; i++) {
    sheetNameArray.push(sheets[i].getName());
  }
  sheetNameArray.sort(); 
  for( var j = 0; j < sheets.length; j++ ) {
    ss.setActiveSheet(ss.getSheetByName(sheetNameArray[j]));
    ss.moveActiveSheet(j + 1);
  }
  ss.setActiveSheet(ss.getSheetByName("Files"));
  ss.moveActiveSheet(1);
  ss.setActiveSheet(ss.getSheetByName("Court Information"));
  ss.moveActiveSheet(2);
  ss.setActiveSheet(ss.getSheetByName("Timelines"));
  ss.moveActiveSheet(3);
  ss.setActiveSheet(ss.getSheetByName("Template"));
  ss.moveActiveSheet(4);
  ss.setActiveSheet(ss.getSheetByName("Sheet Operators"));
  ss.moveActiveSheet(5);
}
2
google-apps-scriptgoogle-sheets
Have you looked at the sorted sheetNameArray and verified that it has the order you want? console.log(sheetNameArray); will send it to Stackdriver, where you can more easily interact with multiple logs. - tehhowch
So, upon adding the console.log(sheetNameArray); it decided to work correctly. It also decided that it needed a reload after running the script (understandable), but it worked. Think I need the logging to make it work? Maybe that slows the script down enough to actually function? - Databoy2k
No idea. Note that you can write your last set a lot more succinctly: ["Files", "Court Information" "Timelines", "Template", "Sheet Operators"].forEach(function (name, index) { ss.setActiveSheet(ss.getSheetByName(name)); ss.moveActiveSheet(1 + index); }); - tehhowch
Forever learning. I'm a JS hack so much of what I develop is google-fu'ed together. I appreciate the tip and have restructured it. Thanks! Getting an error though on save: "Missing ] after element list." Is there something there that needs to be redone? - Databoy2k
Yeah, I missed a comma after "Court Information". There's a reason code doesn't belong in comments ;) - tehhowch

2 Answers

0
votes

For whatever reason, this code actually does work. Thanks to @tehhowch for his or her help as well.

0
votes
function sortSheets () {
  var sheetNameArray = [];
  var sheets = ss.getSheets(); 

  if (sheets.length <= 2){
    return;
  }

  for (var i = 3; i < sheets.length; i++) {
    sheetNameArray.push(sheets[i].getName());
  }

  sheetNameArray.sort(); 

  for( var j = 0; j < sheetNameArray.length; j++ ) {
    ss.setActiveSheet(ss.getSheetByName(sheetNameArray[j]));
    ss.moveActiveSheet(j + 4);
  }
}

for my problem, i want the first 3 to stay and any sheet after to sort accordingly. This is a neater way of doing