Wednesday, October 10, 2012

Google Spreadsheet Script to get all the sheet names in Active Spreadsheet

Question:

Im trying to use the gallery script getAllSheetNames to put a list of sheets on a "report" sheet that I have. Im very new to javascript and while I understand how it is getting the names and creating a list, Im not sure how to have the data flow into my sheet. 

Im trying for instance to have it start at range A5 for the first item, A6 for the second etc...

Can somebody help me with an example how to populate cells from data in a list. 



Solution:

Here is the script to get all sheet names in the active spreadsheet, open script editor and paste the following script in it:


function getAllSheetNames() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var currentSheet = ss.getSheetByName("report");
  var sheets = ss.getSheets();
  var sheetnames = [];
  
  for(var i=0;i<sheets.length;i++)
    sheetnames.push([sheets[i].getName()]);

  //To set values from Cell A5
  currentSheet.getRange(5,1,sheetnames.length,1).setValues(sheetnames);
}



The above script will populate all the sheet names in Sheet 'report', which you can change according to your requirement.


And If you are not much familiar with scripts then check out the following link:


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private or confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,

4 comments:

  1. Thank you very much for the help

    ReplyDelete
  2. Hi Kishan,
    when I use the script below, I get the following reply:

    "erro: TypeError: Cannot call method "getRange" of null. (line 11, file "Code")"

    It perhaps is something obvious but i dont know how to make it work. Do you think it is possible you help me? Thanks a lot!

    ReplyDelete
  3. Lisetonga: You have to create a sheet named "report" first. That will make it work.

    ReplyDelete
  4. Very useful, if i want to auto fill "sheetname!B3" to 'report', how to make this really load the content of sheetname!B3, but not only show in text?

    ReplyDelete