CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Alex
Alex

Reputation: 95

How to sort more then 100 sheets in spreadsheet?

I have google spreadsheet whit more then 100 sheets and i need to sort it by alphabet. Now i have script

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetArray = ss.getSheets();
  var sheetNameArray = [];

  for(var i = 0; i<sheetArray.length; i++){ 
    sheetNameArray.push(sheetArray[i].getSheetName());
  };
 
  sheetNameArray.sort();
  sheetNameArray.forEach(function (element) {
         var sheet = ss.getSheetByName(element);
         ss.setActiveSheet(sheet);
         ss.moveActiveSheet(ss.getNumSheets())
    })

This run in onEdit. But it's work more then 30 seconds and cannot complete. What should i do?

Upvotes: 0

Views: 52

Answers (1)

Marios
Marios

Reputation: 27390

I think this would work faster because I tested both codes:

function sortSheets() {   
   const ss = SpreadsheetApp.getActive();
   const allSheets = ss.getSheets();
   const sheetsAr = allSheets.map(sh=>[sh,sh.getName()]);
   sheetsAr.sort((a,b) => a[1].charCodeAt(0)-b[1].charCodeAt(0));
   sheetsAr.forEach((v,i)=>{
                   ss.setActiveSheet(v[0]);
                   ss.moveActiveSheet(i+1);                   
   });
}

You will gain some performance because I got rid of the for loop.

If this does not work, I am afraid you have to use a time-driven trigger which can run from 6 minutes (if you have a consumer account) or 30 minutes (if you have a business account). You can set up the time driven trigger to run every 1 minute or longer so you can have a sort of "live" adjustments in the orders of the sheet.

Upvotes: 2

Related Questions