To try this tutorial as a coding challenge, make yourself a copy of this Spreadsheet. Open the Script Editor from the Tools menu in your copy of the spreadsheet and see the comments for further instructions.

This tutorial will use what we learned in the first tutorial to iterate through a sheet of data and produce multiple Google Sheets, while logging the new Sheets' file IDs and URLs for later use. Each of these copied Sheets will contain the same exact header structure, and we will use this fact to aggregate their contents together and write them back to a single Google Sheet.

This might be useful when you want data entry to happen in identical Sheets that have been distributed to individual users, but you need a fast way to aggregate it back together. In this example, we want to collect information about all waivers that have been returned in each advisor's class.

First we're going to need a way to provision a templated Sheet to each advisor and write each Sheet's file id to a column in a single sheet. We'd like to name the Spreadsheet and share it in the process. Fortunately, we learned in another tutorial how to iterate through row-based data, perform a task, and write a value to a column. We take the code from the rather silly first tutorial and modify it to perform a real task.

First we write a small function that takes in three arguments to produce a copy of a spreadsheet, with a given title, and shared to specific collaborators, and returns an object containing a spreadsheet ID, a URL, and a status.

{@lang javascript}

/**
* templateId - string
* title - string
* editors - string, comma separated list of emails
*
*/
function copyAndShare(templateId, title, editors) {
  var file = DriveApp.getFileById(templateId).makeCopy(title);
  var fileId = file.getId();
  var fileUrl = file.getUrl();
  var status = "File created on " + new Date();
   if (editors !== '') {
   editors = editors.replace(/ /g,'').split(','); //remove whitespaces and turn into an array of emails
   status += " and shared with ";
   var successes = [];
   var failures = [];
   for (var i=0; i<editors.length; i++) {
   try {
      file.addEditor(editors[i]);
      successes.push(editors[i]);
     } catch(err) {
      failures.push(editors[i]);
     }
     status += successes.join(', ');
     if (failures.length) {
       status += " , errors sharing with " + failures.join(', ');
     }
   }
  }
  return {
    'id': fileId,
    'url': fileUrl,
    'status': status
  }
}

The onOpen function runs on each users' spreadsheet open, and adds a custom menu to the Sheet. The makeNewCopies function iterates through the rows in the "Distributed Sheets" tab of the spreadsheet, shown below the code, and updates a column for the Spreadsheet ID and Spreadsheet URL, including a note that provides status and any sharing errors.

{@lang javascript}

//GLOBAL VARIABLE
//Change this to the file ID of the spreadsheet you want to use as a template
var templateId = "1v01hM3fS1bPuwQfELIedarnJxkuWoHvHTFyYdQDpjmQ";


function onOpen() {
  var menu = SpreadsheetApp.getUi().createMenu('Sheets Aggregator');
  menu.addItem('Make copies', 'makeNewCopies');
  menu.addItem('Aggregate data', 'aggregateData');
  menu.addToUi()
}

function makeNewCopies() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Distributed Sheets');
  var headers = GSheetsUtils.getUpsertHeaders(sheet);
  var idCol = headers.indexOf('Spreadsheet ID') + 1;
  var urlCol = headers.indexOf('Spreadsheet URL') + 1;
  var data = GSheetsUtils.getRowsData(sheet);
  for (var i=0; i<data.length; i++) {
    if ((data[i]['Spreadsheet ID'] === '')&&(data[i]['Spreadsheet Name'])) {
      var result = copyAndShare(templateId, data[i]['Spreadsheet Name'], data[i]['Editors to Add']);
      sheet.getRange(i+2, idCol).setValue(result.id);
      sheet.getRange(i+2, urlCol).setValue(result.url);
      sheet.getRange(i+2, urlCol).setNote(result.status);
      SpreadsheetApp.flush();
    }
  }
}

Here's what the "Distributed Sheets" tab would look like after you run the makeNewCopies function.

Let's assume that each distributed sheet has manually-entered data in it that looks something like this.

Our goal is to iterate through all of these sheets and bring the data together into a single sheet.

Finally, we create a function that concatenates the data arrays from all the distributed spreadsheets and uses GSheetsUtils.setRowsData() to write this combined data set back to the "Aggregated Data" tab in the master spreadsheet.

{@lang javascript}
function aggregateData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Distributed Sheets');
  var headers = GSheetsUtils.getUpsertHeaders(sheet);
  var statusCol = headers.indexOf('Aggregation Status') + 1;
  var data = [];
  var distributedSheets = GSheetsUtils.getRowsData(sheet);
  for (var i=0; i<distributedSheets.length; i++) {
    var status = '';
    var sheetId = distributedSheets[i]['Spreadsheet ID'];
    if (sheetId) {
      try {
        var thisSS = SpreadsheetApp.openById(sheetId);
        var thisSheet = thisSS.getSheetByName('Waivers');
        var thisSheetData = GSheetsUtils.getRowsData(thisSheet);
        data = data.concat(thisSheetData);
        status = new Date() + ": pulled back " + thisSheetData.length + " records";
      } catch(err) {
        status = "Error: " + err.message;
      }
      sheet.getRange(i+2, statusCol).setValue(status);
    }
  }
  var aggregateSheet = ss.getSheetByName('Aggregated Data');
  var lastRow = aggregateSheet.getLastRow();
  if (lastRow > 1) {
    aggregateSheet.getRange(2, 1, lastRow-1, aggregateSheet.getLastColumn()).clear();
  }
  GSheetsUtils.setRowsData(aggregateSheet, data);
}

Once this function has run, the "Distributed Sheets" tab ends up looking something like this...

...and the "Aggregated Data" tab is cleared and ends up holding aggregation of the data from all sheets.

An alternate approach to using a concatenated array, and one that might be more appropriate when dealing with a process that might exceed Google Apps Script's 6 minute execution time limit, would be to append the results for the sheet only if it's current status is blank in the "Distributed Sheets" tab. This could allow a resume to occur that would continue appending where the job left off...

{@lang javascript}
function aggregateDataAlternate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Distributed Sheets');
  var headers = GSheetsUtils.getUpsertHeaders(sheet);
  var statusCol = headers.indexOf('Aggregation Status') + 1;
  var distributedSheets = GSheetsUtils.getRowsData(sheet);
  var aggregateSheet = ss.getSheetByName('Aggregated Data');
  var lastRow = aggregateSheet.getLastRow();
  for (var i=0; i<distributedSheets.length; i++) {
    var status = '';
    var sheetId = distributedSheets[i]['Spreadsheet ID'];
    if (sheetId && (distributedSheets[i]['Aggregation Status'] === '')) { //only append for sheets with blank status
      try {
        if (i==0) { //if starting over from first sheet, clear the aggregate sheet out
          //TO DO:  Build a way to auto-resume execution if things time out...
          aggregateSheet.getRange(2, 1, lastRow-1, aggregateSheet.getLastColumn()).clear();
        }
        var thisSS = SpreadsheetApp.openById(sheetId);
        var thisSheet = thisSS.getSheetByName('Waivers');
        var thisSheetData = GSheetsUtils.getRowsData(thisSheet);
        GSheetsUtils.appendRowsData(aggregateSheet, thisSheetData);
        status = new Date() + ": pulled back " + thisSheetData.length + " records";
      } catch(err) {
        status = "Error: " + err.message;
      }
      sheet.getRange(i+2, statusCol).setValue(status);
    }
  }
}