Google Apps Script for copying an array from multiple sheets onto a single Status Summary sheet

Question

I've been trying to find a script I can copy over for my purposes (with some modifications for my specific sheet), but nothing is quite doing what I need it to and I don't have the coding skills to write it from scratch.

I have a Google spreadsheet with multiple sheets where we will constantly be adding new sheets, one for each new product we launch. Each of the sheets (including the new ones, created from a template) have an "At a Glance" summary of that product's launch readiness status. This is always located at M3:M10 on each sheet.

I have a Status Summary sheet where I'd like to paste the information found at M3:M10 from each sheet. This will allow us to quickly see on the Status Summary how each product is doing ahead of launch, without requiring us to check each specific sheet's tab.

This is what I'd ideally like to happen:

• The script will loop through each sheet contained within the workbook, but skip the sheet called "Status Summary."
• Script will pull data from M3:M10 from each sheet
• Script will deposit M3:M10 data on "Status Summary," organizing the information across A2:H2 (my column labels are A1:H1). For example: M3 data on Sheet 1 will be deposited in A2 on Status Summary, M3 data on Sheet 1 will be deposited in B2 on Status Summary, etc.
• Script will automatically use a new line for each new sheet. For example: M3 data on Sheet 1 will be in A2 on Status Summary, M3 data on Sheet 2 will be in A3 on Status Summary, etc.

I don't need the names of the sheets included.

Is anyone able to write this for me? I'm trying to teach myself how to code these scripts, so a few "//" explanations about what each section does would be a huge help. Thank you for looking!

Answer 1

try this:

function atAGlance() {
  var ss=SpreadsheetApp.getActive();
  var excl=['Status Summary'];
  var sh=ss.getSheetByName('Status Summary');
  var shts=ss.getSheets();
  var n=1;
  for(var i=0;i<shts.length;i++) {
    if(excl.indexOf(shts[i].getName())==-1){
      var v1=shts[i].getRange("M3:M10").getValues();
      var v2=transpose(v1);
      sh.getRange(1 + n++,1,v2.length,v2[0].length).setValues(v2);
    }
  }
}
//https://stackoverflow.com/a/16705104/7215091
function transpose(a)
{
  return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); });
}

Got the transpose from [https://stackoverflow.com/a/16705104/7215091] (https://stackoverflow.com/a/16705104/7215091)

This version allows you to exclude sheets from the process and you can run it as shown below in the testAtAGlance() function. The example I was using excluded 'Status Summary' and 'Globals'.

function testAtAGlance() {
  atAGlance(['Status Summary','Globals']);
}

function atAGlance(excl) {
  var excl=excl || ['Status Summary'];
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Status Summary');
  var shts=ss.getSheets();
  var n=1;
  for(var i=0;i<shts.length;i++) {
    if(excl.indexOf(shts[i].getName())==-1){
      var v1=shts[i].getRange("M3:M10").getValues();
      var v2=transpose(v1);
      sh.getRange(1 + n++,1,v2.length,v2[0].length).setValues(v2);
    }
  }
}
//https://stackoverflow.com/a/16705104/7215091
function transpose(a)
{
  return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); });
}