Clever Geek Handbook

Get list of sheet names from google spreadsheet to another spreadsheet

I have a google spreadsheet that contains 50 to 60 sheets. I am trying to get the name of all these sheets in another table.

So far I have written the following code

function myfunction(){
var ss = SpreadsheetApp.openById("abck12345");
var sheetCount = ss.getSheets().length;
var  i=0;
var sheetlist;
while(i<sheetCount){
sheetlist = ss.getSheets()[i];
Logger.log(sheetlist);
}

}

While doing this I get the error "Maximum execution time exceeded"

+3


source to share


2 answers


Efficiently get all sheets at once. Your code gets all the sheets in every loop. This code gets all the sheet names and puts them in a new table:



function myfunction() {
  var allSheets,i,sheetCount,sheetlist,sheetName,ss,
      targetSh,targetSS,thisSheet;

  ss = SpreadsheetApp.openById("abck12345");
  targetSS = SpreadsheetApp.openById("999999");

  allSheets = ss.getSheets();
  sheetCount = allSheets.length;

  sheetlist = [];

  for (i=0;i<sheetCount;i++) {
    thisSheet = allSheets[i];
    sheetName = thisSheet.getName();

    sheetlist.push([sheetName]);//Put an inner array of each sheet tab name into 
    //an outer array named sheetlist
    //Creates a 2D array for setting the values later
  }

  Logger.log(sheetlist);

  targetSh = targetSS.getSheetByName('target sheet tab name');
  targetSh.getRange(targetSh.getLastRow()+1, 1, sheetlist.length, sheetlist[0].length).setValues(sheetlist);
}
+1


source


You are not increasing i. The best way to do it would be as follows:



function myfunction(){
  var ss = SpreadsheetApp.openById("1cmUWU_nbE6idJUmpNtKnhq0KUekVXnW9OYVZr93TSNs");
  var sheets = ss.getSheets();
  var  i;

  for(i in sheets){
    Logger.log(sheets[i].getName())
  }

}
0


source






More articles:


All Articles