CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Ryan Capule
Ryan Capule

Reputation: 143

How to Print the names of the sheets and the Google ID

I am trying to get print the names of all the sheets in a Google Spreadsheet in one column and their respective IDs (part of the URL) in another column. Here is my script so far

function getSchedule() {
  var ss=SpreadsheetApp.openById("Sheet_ID");
  var sh=ss.getActiveSheet();
  var allSheets=ss.getSheets();
  var ids=[];
  for(var i=0;i<allSheets.length;i++)
  {
    ids[allSheets[i].getName()]=allSheets[i].getSheetId();
  }

  var invoicess = SpreadsheetApp.getActiveSpreadsheet();
  var schedule = invoicess.getSheetByName("Schedule");
  schedule.getRange("A1:B").clear();
  var headers = [["Sheet Name", "ID"]];
  schedule.getRange("A1:B1").setValues(headers);
  
  for(var d = 1; d=allSheets.length;d++){
  schedule.getRange(d+1,1).setValue(allSheets[d]);}
}

So I believe that allSheets should be an array of all the sheet names and ids is an array of the IDs, but the last for loop that I believe should print the values of the allSheets array does not print anything. I would like to have it print starting in cell A2. I do not have a loop for the IDs yet. Can anyone tell me why nothing is printing in the cells?

Upvotes: 1

Views: 114

Answers (2)

Marios
Marios

Reputation: 27390

Issues:

  • The following line is not going to add new values to the array: ids[allSheets[i].getName()]=allSheets[i].getSheetId();
  • As a result, data are not added to the array and this is the reason you are not seeing data in your sheet. Instead you should use push() to add elements (rows) to the array.
  • Also since you are pasting a 2D array of values, you need to use setValues instead of setValue.
  • Your code uses unnecessary lines of code. For example you use setValues to add the header in your file when you can simply add it to the original array in which the data is going to be appended. You don't need for loops to append values with setValues.
  • I improved your code by removing redundant calls and made it more JavaScript friendly.

Improved Solution:

function getSchedule() {
  const ss=SpreadsheetApp.openById("Sheet_ID");
  const invoicess = SpreadsheetApp.getActiveSpreadsheet();
  const schedule = invoicess.getSheetByName("Schedule");
  const allSheets = ss.getSheets();
  const ids = [["Sheet Name", "ID"]];
  allSheets.forEach(sh=>{
  ids.push([sh.getName(),sh.getSheetId()]);                  
  });   
  schedule.getRange("A1:B").clear();
  schedule.getRange(1,1,ids.length,ids[0].length).setValues(ids);
}

Upvotes: 1

Cooper
Cooper

Reputation: 64120

Instead of this ids[allSheets[i].getName()]=allSheets[i].getSheetId();

try this: ids.push([allSheets[i].getName(),allSheets[i].getSheetId()])

and use setValues(ids) at the end.

Upvotes: 0

Related Questions