Consolidate Spreadsheet Sheets with Apps Scripts

A better way to create a Master sheet

By Dito’s Steve Webster, Google Apps Script Top Contributor, 1/19/2012

Use Case

Let’s say you have a sales team where they update a spreadsheet with activity information. Each Sales Rep has their own sheet to maintain. The Sales Manager has a master sheet that copies all the data from each Sales Rep sheet.

Why must the master sheet be updated real-time by using formulas when the Sales Manager only visits the spreadsheet on an occasional basis? Let’s improve the load performance and eliminate the complex formulas with an on-demand solution.

Solution

You can create a Google Apps Script to update the master sheet with a customized menu option. This can be done within the Script Editor (click on Tools > Script Editor).

The function below creates a menu option on the spreadsheet.

function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: “Update Master”, functionName: “updateMaster”}];
ss.addMenu(“Scripts”, menuEntries);
}

Add the “updateMaster” function code below. After saving the Apps Script and refreshing the spreadsheet, the “Scripts” menu is added.

When invoked, the “updateMaster” function runs. The goal is to limit the “reads” and “writes” to the spreadsheet. To accomplish this, arrays (used to store multiple values in a single variable) are leveraged to store the data before updating the master sheet.

The first array stores all the Sales Rep sheets. Since some sheets could be something other than Sales Rep sheets, the script stores sheets only if the sheet name has a “-Rep” suffix (e.g. “JohnDoe-Rep”)

The second array stores each Sales Rep data (all the rows and columns). The script loops through each sales sheet from the sheet array to build the master data array (all the rows and columns for all the Sales Rep sheets).

With all the Sales Reps data stored in the master data array, the script can now update the entire master sheet.

Just in case there are some empty rows at the bottom of the master sheet, the script compares the last row of data with the total rows to identify which rows to delete.

function updateMaster() {
// This script was developed by Dito’s Steve Webster (www.ditoweb.com)
/*
Licensed under the Apache License, Version 2.0 (the “License”);
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an “AS IS” BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/
var repArray = new Array();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var allSheets = ss.getSheets();

// build array of all sheets
for (i in allSheets) {
if ((allSheets[i].getName()).match(/.*?-Rep$/))
{repArray.push(allSheets[i].getName());}
}

// store all sheets in array
var sheetArray = [];

// loop through all rep sheets
for (var j in repArray) {

// get each sheet

var tempSheet = ss.getSheetByName(repArray[j]);

// get sheet data

var dataRange = tempSheet.getDataRange().getValues();

// remove the first header row

dataRange.splice(parseInt(0), 1);

// append sheet data to array
var sheetArray = sheetArray.concat(dataRange);
}

// Time to update the master sheet
var mSheet = ss.getSheetByName(“Master”);

// save top header row
var headerRow = mSheet.getRange(1,1,1,12).getValues();

// clear the whole sheet
mSheet.clear({contentsOnly:true});

// put back the header row

mSheet.getRange(1, 1, 1, 12).setValues(headerRow);

// write to the Master sheet via the array
mSheet.getRange(2, 1, sheetArray.length, 12).setValues(sheetArray);

// force spreadsheet updates
SpreadsheetApp.flush();

// pause (1,000 milliseconds = 1 second)

Utilities.sleep(“200”);

// delete empty rows at bottom
var last = mSheet.getLastRow();
var max = mSheet.getMaxRows();
if (last !== max) {mSheet.deleteRows(last+1,max-last);}
}

To reduce the number of blank rows in between each Sales Rep data in the master sheet, each Sales Rep can manually delete blank rows at the bottom of their sheets. We don’t want the script to delete Sales Rep rows as a best practice (too risky). Alternately, the script could be improved to look for and delete blank rows within the master array before updating the master sheet.

You may copy this script as long as you keep the code comment that mentions Dito (www.ditoweb.com) authored the script and agree to the license.

To learn more visit the Google Apps Script site. Get help by visiting the support forum.

Let us know what you think about on-demand versus real-time master sheets.