Getting specific data from a lot of different sheets with a script in Google Spreadsheet

Question

I've got a pretty massive workbook that contains a little over 300 sheets. I'm trying to grab 2 cells from each sheet and move that data to master sheet. I'll create a sheet that is similar to my main buy smaller. Here is a test sheet

https://docs.google.com/spreadsheets/d/19DoIyBVFLCt7TU5PEZCMcFMCbRcXhKgNc3OBGcUPixk/edit?usp=sharing

Right now I have a script that does this but its completely inefficient, but I'm brand new to this. I have to do this set of code below for each sheet I have. So the code ends up being over 2000+ lines...

  sheet.insertRowAfter(49);
  var Date = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetName").getRange("J16").getValues();
  DateCell.setValue(Date);
  NameCell.setValue("SheetName");

I'm looking for a script that will do what I have but in a loop of some sort. The way I have it is just wrong.

Chris Chris · Accepted Answer · 2019-02-13T06:35:43

You'd want to use for^[1] loops or forEach()^[2] on the Spreadsheet.getSheets() method.

For this to work Master must be the first sheet in the spreadsheet.

 var ss = SpreadsheetApp.getActiveSpreadsheet(); //Gets the current spreadsheet.
var sheets = ss.getSheets(); //Gets a list of all sheets in the current spreadsheet

//Get master for use later by removing the first sheet.
var master = sheets.shift(); 

sheets.forEach(function (sheet){
  //Here we can perform a task on each sheet in our spreadsheet...
  var name = sheet.getRange("A1").getValue(); //Get name from sheet
  var date = sheet.getRange("C7").getValue(); //Get date from sheet

  master.appendRow([name, date]); //append to master sheet
}

This will allow you to go through every sheet in your spreadsheet file and perform the same task in each!

Getting specific data from a lot of different sheets with a script in Google Spreadsheet

1 Answers