for each in ss.getSheets() get a range of data

Question

I'm struggling on a child task within a function I'm working on.

For each sheet in sheets, I would like to get the data in range A16:D, combine into one big array and then out put the combined data into a new sheet. But each time I try to select each data range it comes as undefined?

How can I get the data from each sheet in sheets and then add into one big array?

    var ss = SpreadsheetApp.getActiveSpreadsheet();
var ranges = [];

function combineData() {
  var activeSheetName = ss.getActiveSheet().getName();
  var sheets = ss.getSheets();
  sheets.forEach(function(e) {
    var sheetName = e.getName();
    if (sheetName != activeSheetName && sheetName != 'Report Configuration' && sheetName != 'Sheet1')
      var wee_data = e.getRange('A16:D').getValues(); // TROUBLE HERE, WEE_DATA IS UNDEFINED
    for(var j = 0; j<wee_data.length; j++) {
      store.push(wee_data[j]);
    }
      ranges.push(wee_data);
  });
  if (ranges.length) {
    Logger.log('range length is: ' + ranges.length);
    adjustSheetLength(); // ensure there are enough rows in the destination sheet.
    // figure out how to output data array "ranges" into a sheet "combined".
  }
}

function adjustSheetLength(){
  var comb = ss.getSheetByName('combined');
  var lastRow = 4; 
  var maxRows = comb.getLastRow();
  comb.deleteRows(lastRow, maxRows-lastRow);
  var datapullSize = ranges.length;
  comb.insertRows(4, datapullSize-2);// insert exactly the number of rows you need.
}

corn3lius corn3lius · Accepted Answer · 2015-08-18T14:38:19

I think your issue is in the .forEach callback function. When I debugged those functions I got a not allowed in callback exception.

Change the foreach to a for in I think that'll fix your issue.

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

for each in ss.getSheets() get a range of data

1 Answers