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 athttp://www.apache.org/licenses/LICENSE-2.0Unless required by applicable law or agreed to in writing, softwaredistributed 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 andlimitations under the License.*/ var repArray = new Array(); var ss = SpreadsheetApp.getActiveSpreadsheet(); var allSheets = ss.getSheets();

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 theGoogle Apps Script site. Get help by visiting thesupport forum.Let us know what you think about on-demand versus real-time master sheets.