Google App Script to merge multiple sheets into a master

Question

I have a small sales team (5 people but is growing) that log sales into multiple sheets within a workbook, I want to run a script to merge all these sheets, if possible daily, with a script.

So the stages would be on a daily basis

1) Remove all records from Master Daily (This is to account for any changes on previous data from the individual sales sheets) 2) Loop through all the sheets and add in the data

There are 6 Columns which are all in the same order but contain different data.

Demo Sheet Here

Editing Open until bounty awarded.

Code attempt :

function merge() {
    var v, arr,
        ss = SpreadsheetApp.getActive();
    ss.getSheets().filter(function(s) {
        return s.getName()
            .indexOf('Project') > -1
    }).forEach(function(s, i) {
        v = s.getDataRange()
            .getValues()
            .filter(function(r) {
                return r.toString()
                    .length > 0
            })
        arr = (i == 0) ? v : arr.concat(v);
    })
    ss.getSheetByName('Master')
        .getRange(1, 1, arr.length, arr[0].length)
        .setValues(arr);
}

any help would be appreciated.

Thanks!

Take a look at [this SO question](https://stackoverflow.com/questions/41580887/google-spreadsheets-query-to-combine-multiple-sheets-same-workbook) as it might be a non-scripting solution for your problem — Casper, Jun 22 '18 at 11:09
*I did try adding Project to each of my teams sheets, and what happened was that only one sheet was returned in the Master sheet.* Was there "Project" in each of the team sheet name? — TheMaster, Feb 11 '20 at 06:01
Thing is it seems your original script will work fine and seems perfect for the job. Does it not work? All you have to do is add "Project" to the sheet name of all teams. — TheMaster, Feb 11 '20 at 13:31

TheMaster · Accepted Answer · 2020-02-12T16:47:58.930

2

A simplified version of your script:

//@OnlyCurrentDoc
function merge() {
  const ss = SpreadsheetApp.getActive();
  const arr = ss
    .getSheets()
    .filter(s => !s.getName().includes('Master'))//exclude Master sheet
    .flatMap(s => s.getDataRange().getValues());//map sheet to values and flatten it
  ss.getSheetByName('Master')
    .getRange(1, 1, arr.length, arr[0].length)
    .setValues(arr);
}

Modified

to exclude master sheet instead of including all project sheets
to use v8 es6 syntax and optimizations

edited Feb 12 '20 at 16:47

answered Feb 11 '20 at 13:48

TheMaster

45,448
6
62
85

Note: You need to [enable v8](https://developers.google.com/apps-script/guides/v8-runtime#enabling_the_v8_runtime) before testing. – TheMaster Feb 11 '20 at 13:53

Cooper · Answer 2 · 2020-02-11T03:24:23.560

These are the main functions.

dailysheets.gs:

function archiveDaily() {
  const mshsr=4;
  const ss=SpreadsheetApp.getActive();
  const msh=ss.getSheetByName('Master');
  const dss=SpreadsheetApp.openById(getGlobal('MasterSheetsLogId'));//open master archive
  const ts=Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MM/dd/yy HH:mm:ss")
  msh.copyTo(dss).setName(ts);//copies current master sheet to master archive not sure if you would want this but I would
  if(msh.getLastRow()-mshsr+1>0) {
    msh.getRange(mshsr,1,msh.getLastRow()-mshsr+1,msh.getLastColumn()).clearContent();
  }
  const nA=getGlobal('DailySheetNames').split(',');//sheet name store in Globals sheet as a hash table
  const dt=new Date();
  const td=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()).valueOf();//start of day
  const to=new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()+1).valueOf();//end of day
  const dshsr=4;
  nA.forEach(function(name,i){
    let dsh=ss.getSheetByName(name);
    let drg=dsh.getRange(dshsr,1,dsh.getLastRow()-dshsr+1,dsh.getLastColumn());
    let v=drg.getDisplayValues();
    let mv=[[dsh.getName(),'','','','','','','','','','','','','','','','']];//Display Sheetname on top of every group
    v.forEach(function(r,i){
      let idt=new Date(r[0]).valueOf();
      //Select rows that fall between midnights
      if(idt>td && idt<to) {
        mv.push(r);
      }
    });
    msh.getRange(msh.getLastRow()+1,1,mv.length,mv[0].length).setValues(mv);//Put each sheets data into master sheet
  });
}

function createDailyTrigger() {
  var ss=SpreadsheetApp.getActive();
  //This line keeps you  from creating more than one trigger
  if(!isTrigger('archiveDaily')) {
    ScriptApp.newTrigger('archiveDaily').timeBased().everyDays(1).atHour(23).create();
  }
}

These files are just some support files that I use a lot and I used a few things from them in this project.

triggers.gs:

//Filename: triggers.gs
function deleteTrigger(triggerName){
  var triggers=ScriptApp.getProjectTriggers();
  for (var i=0;i<triggers.length;i++){
    if (triggerName==triggers[i].getHandlerFunction()){
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
}

function isTrigger(funcName){
  var r=false;
  if(funcName){
    var allTriggers=ScriptApp.getProjectTriggers();
    for(var i=0;i<allTriggers.length;i++){
      if(funcName==allTriggers[i].getHandlerFunction()){
        r=true;
        break;
      }
    }
  }
  return r;
}

function deleteAllTriggers(){
  var triggers=ScriptApp.getProjectTriggers();
  for (var i=0;i<triggers.length; i++){
    ScriptApp.deleteTrigger(triggers[i]);
  }
}

function displayProjectTriggers() {
  var tA=ScriptApp.getProjectTriggers();
  var html="<style>th,td{border:1px solid black;padding:2px;margin:2px;}</style><table><tr><th>Handler Function</th><th>Trigger Type</th><th>Unique ID</th><td>&nbsp;</td></tr>";
  for(var i=0;i<tA.length;i++) {
    html+=Utilities.formatString('<tr><td>%s</td><td>%s</td><td>%s</td><td><input type="button" value="Delete" onClick="google.script.run.withSuccessHandler(function(){google.script.run.displayProjectTriggers();}).deleteTrigger(\'%s\');" /></td></tr>',tA[i].getHandlerFunction(),tA[i].getEventType(),tA[i].getUniqueId(),tA[i].getHandlerFunction());
  }
  html+='</table><br /><input type="button" value="Close" onClick="google.script.host.close();" />';
  var userInterface=HtmlService.createHtmlOutput(html).setWidth(800);
  SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Project Triggers');
}

globals.gs: I create a sheet named Globals where I store some sheet names and the id of the spreadsheet where I copy the master sheet to just before clearing the contents.

function getGlobals(){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Globals');
  var rg=sh.getRange(1,1,sh.getLastRow(),2);
  var vA=rg.getValues();
  var g={};
  for(var i=0;i<vA.length;i++){
    g[vA[i][0]]=vA[i][1];
  }
  return g;
}

function setGlobals(dfltObj){
  var dfltH=Object.keys(dfltObj).length;
  if(dfltObj){
    var ss=SpreadsheetApp.getActive();
    var sh=ss.getSheetByName('Globals');
    var rg=sh.getRange(1,1,dfltH,2);
    var vA=rg.getValues();
    for(var i=0;i<dfltH;i++){
      vA[i][1]=dfltObj[vA[i][0]];
    }
    rg.setValues(vA);
  }
}

function getGlobal(name){
  return getGlobals()[name];
}

function setGlobal(name,value){
  var curObj=getGlobals();
  if(!curObj.hasOwnProperty(name)) {
    var ss=SpreadsheetApp.getActive();
    var sh=ss.getSheetByName('Globals');
    sh.appendRow([name,value])
  }else{
    curObj[name]=value;
    setGlobals(curObj);
  }
}

function cleanGlobals() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Globals');
  if(sh.getLastColumn()>2) {
    sh.getRange(1,3,sh.getLastRow(),sh.getLastColumn()-2).clearContent();
  }
  var rg=sh.getRange(1,1,sh.getLastRow(),2);
  var vA=rg.getValues();
  for(var i=0;i<vA.length;i++) {
    if(!vA[i][0] || !vA[i][1]) {
      var userInterface=HtmlService.createHtmlOutput('Globals Sheet Requires Maintenance...Do it know.' + ' Check Row ' + Number(i + 1));
      SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Global Maintenance Required');
      break;
    }
  }
}

Codes.gs: I have a menu setup here.

function onOpen(){
  makeMenu();
}

function makeMenu(){
  SpreadsheetApp.getUi().createMenu('My Tools')
  .addItem('Archive','archiveDaily')
  .addItem('Display Project Triggers', 'displayProjectTriggers')
  .addItem('Create Daily Trigger', 'createDailyTrigger')
  .addToUi();
}

Image of one of the sheets:

csv for one of the data sheets:

1234567891011121314151617
012345678910111213141516
HDR1HDR2HDR3HDR4HDR5HDR6HDR7HDR8HDR9HDR10HDR11HDR12HDR13HDR14HDR15HDR16HDR17
Mon Feb 10 2020 06:00:00 GMT-0700 (Mountain Standard Time)12345678910111213141516
Mon Feb 10 2020 06:20:00 GMT-0700 (Mountain Standard Time)234567891011121314151617
Mon Feb 10 2020 06:40:00 GMT-0700 (Mountain Standard Time)3456789101112131415161718
Mon Feb 10 2020 07:00:00 GMT-0700 (Mountain Standard Time)45678910111213141516171819
Mon Feb 10 2020 07:20:00 GMT-0700 (Mountain Standard Time)567891011121314151617181920
Mon Feb 10 2020 07:40:00 GMT-0700 (Mountain Standard Time)6789101112131415161718192021
Mon Feb 10 2020 08:00:00 GMT-0700 (Mountain Standard Time)78910111213141516171819202122
Mon Feb 10 2020 08:20:00 GMT-0700 (Mountain Standard Time)891011121314151617181920212223
Mon Feb 10 2020 08:40:00 GMT-0700 (Mountain Standard Time)9101112131415161718192021222324
Mon Feb 10 2020 09:00:00 GMT-0700 (Mountain Standard Time)10111213141516171819202122232425
Mon Feb 10 2020 09:20:00 GMT-0700 (Mountain Standard Time)11121314151617181920212223242526
Mon Feb 10 2020 09:40:00 GMT-0700 (Mountain Standard Time)12131415161718192021222324252627
Mon Feb 10 2020 10:00:00 GMT-0700 (Mountain Standard Time)13141516171819202122232425262728
Mon Feb 10 2020 10:20:00 GMT-0700 (Mountain Standard Time)14151617181920212223242526272829
Mon Feb 10 2020 10:40:00 GMT-0700 (Mountain Standard Time)15161718192021222324252627282930
Mon Feb 10 2020 11:00:00 GMT-0700 (Mountain Standard Time)16171819202122232425262728293031
Mon Feb 10 2020 11:20:00 GMT-0700 (Mountain Standard Time)17181920212223242526272829303132
Mon Feb 10 2020 11:40:00 GMT-0700 (Mountain Standard Time)18192021222324252627282930313233

Image of the master sheet:

csv for Master Sheet:

1234567891011121314151617
012345678910111213141516
HDR1HDR2HDR3HDR4HDR5HDR6HDR7HDR8HDR9HDR10HDR11HDR12HDR13HDR14HDR15HDR16HDR17
Adam
Mon Feb 10 2020 06:00:00 GMT-0700 (Mountain Standard Time)12345678910111213141516
Mon Feb 10 2020 06:20:00 GMT-0700 (Mountain Standard Time)234567891011121314151617
Mon Feb 10 2020 06:40:00 GMT-0700 (Mountain Standard Time)3456789101112131415161718
Mon Feb 10 2020 07:00:00 GMT-0700 (Mountain Standard Time)45678910111213141516171819
Mon Feb 10 2020 07:20:00 GMT-0700 (Mountain Standard Time)567891011121314151617181920
Mon Feb 10 2020 07:40:00 GMT-0700 (Mountain Standard Time)6789101112131415161718192021
Mon Feb 10 2020 08:00:00 GMT-0700 (Mountain Standard Time)78910111213141516171819202122
Mon Feb 10 2020 08:20:00 GMT-0700 (Mountain Standard Time)891011121314151617181920212223
Mon Feb 10 2020 08:40:00 GMT-0700 (Mountain Standard Time)9101112131415161718192021222324
Mon Feb 10 2020 09:00:00 GMT-0700 (Mountain Standard Time)10111213141516171819202122232425
Mon Feb 10 2020 09:20:00 GMT-0700 (Mountain Standard Time)11121314151617181920212223242526
Mon Feb 10 2020 09:40:00 GMT-0700 (Mountain Standard Time)12131415161718192021222324252627
Mon Feb 10 2020 10:00:00 GMT-0700 (Mountain Standard Time)13141516171819202122232425262728
Mon Feb 10 2020 10:20:00 GMT-0700 (Mountain Standard Time)14151617181920212223242526272829
Mon Feb 10 2020 10:40:00 GMT-0700 (Mountain Standard Time)15161718192021222324252627282930
Mon Feb 10 2020 11:00:00 GMT-0700 (Mountain Standard Time)16171819202122232425262728293031
Mon Feb 10 2020 11:20:00 GMT-0700 (Mountain Standard Time)17181920212223242526272829303132
Mon Feb 10 2020 11:40:00 GMT-0700 (Mountain Standard Time)18192021222324252627282930313233
Bill
Mon Feb 10 2020 06:00:00 GMT-0700 (Mountain Standard Time)12345678910111213141516
Mon Feb 10 2020 06:20:00 GMT-0700 (Mountain Standard Time)234567891011121314151617
Mon Feb 10 2020 06:40:00 GMT-0700 (Mountain Standard Time)3456789101112131415161718
Mon Feb 10 2020 07:00:00 GMT-0700 (Mountain Standard Time)45678910111213141516171819
Mon Feb 10 2020 07:20:00 GMT-0700 (Mountain Standard Time)567891011121314151617181920
Mon Feb 10 2020 07:40:00 GMT-0700 (Mountain Standard Time)6789101112131415161718192021
Mon Feb 10 2020 08:00:00 GMT-0700 (Mountain Standard Time)78910111213141516171819202122
Mon Feb 10 2020 08:20:00 GMT-0700 (Mountain Standard Time)891011121314151617181920212223
Mon Feb 10 2020 08:40:00 GMT-0700 (Mountain Standard Time)9101112131415161718192021222324
Mon Feb 10 2020 09:00:00 GMT-0700 (Mountain Standard Time)10111213141516171819202122232425
Mon Feb 10 2020 09:20:00 GMT-0700 (Mountain Standard Time)11121314151617181920212223242526
Mon Feb 10 2020 09:40:00 GMT-0700 (Mountain Standard Time)12131415161718192021222324252627
Mon Feb 10 2020 10:00:00 GMT-0700 (Mountain Standard Time)13141516171819202122232425262728
Mon Feb 10 2020 10:20:00 GMT-0700 (Mountain Standard Time)14151617181920212223242526272829
Mon Feb 10 2020 10:40:00 GMT-0700 (Mountain Standard Time)15161718192021222324252627282930
Mon Feb 10 2020 11:00:00 GMT-0700 (Mountain Standard Time)16171819202122232425262728293031
Mon Feb 10 2020 11:20:00 GMT-0700 (Mountain Standard Time)17181920212223242526272829303132
Mon Feb 10 2020 11:40:00 GMT-0700 (Mountain Standard Time)18192021222324252627282930313233
Charlie
Mon Feb 10 2020 06:00:00 GMT-0700 (Mountain Standard Time)12345678910111213141516
Mon Feb 10 2020 06:20:00 GMT-0700 (Mountain Standard Time)234567891011121314151617
Mon Feb 10 2020 06:40:00 GMT-0700 (Mountain Standard Time)3456789101112131415161718
Mon Feb 10 2020 07:00:00 GMT-0700 (Mountain Standard Time)45678910111213141516171819
Mon Feb 10 2020 07:20:00 GMT-0700 (Mountain Standard Time)567891011121314151617181920
Mon Feb 10 2020 07:40:00 GMT-0700 (Mountain Standard Time)6789101112131415161718192021
Mon Feb 10 2020 08:00:00 GMT-0700 (Mountain Standard Time)78910111213141516171819202122
Mon Feb 10 2020 08:20:00 GMT-0700 (Mountain Standard Time)891011121314151617181920212223
Mon Feb 10 2020 08:40:00 GMT-0700 (Mountain Standard Time)9101112131415161718192021222324
Mon Feb 10 2020 09:00:00 GMT-0700 (Mountain Standard Time)10111213141516171819202122232425
Mon Feb 10 2020 09:20:00 GMT-0700 (Mountain Standard Time)11121314151617181920212223242526
Mon Feb 10 2020 09:40:00 GMT-0700 (Mountain Standard Time)12131415161718192021222324252627
Mon Feb 10 2020 10:00:00 GMT-0700 (Mountain Standard Time)13141516171819202122232425262728
Mon Feb 10 2020 10:20:00 GMT-0700 (Mountain Standard Time)14151617181920212223242526272829
Mon Feb 10 2020 10:40:00 GMT-0700 (Mountain Standard Time)15161718192021222324252627282930
Mon Feb 10 2020 11:00:00 GMT-0700 (Mountain Standard Time)16171819202122232425262728293031
Mon Feb 10 2020 11:20:00 GMT-0700 (Mountain Standard Time)17181920212223242526272829303132
Mon Feb 10 2020 11:40:00 GMT-0700 (Mountain Standard Time)18192021222324252627282930313233
Don
Mon Feb 10 2020 06:00:00 GMT-0700 (Mountain Standard Time)12345678910111213141516
Mon Feb 10 2020 06:20:00 GMT-0700 (Mountain Standard Time)234567891011121314151617
Mon Feb 10 2020 06:40:00 GMT-0700 (Mountain Standard Time)3456789101112131415161718
Mon Feb 10 2020 07:00:00 GMT-0700 (Mountain Standard Time)45678910111213141516171819
Mon Feb 10 2020 07:20:00 GMT-0700 (Mountain Standard Time)567891011121314151617181920
Mon Feb 10 2020 07:40:00 GMT-0700 (Mountain Standard Time)6789101112131415161718192021
Mon Feb 10 2020 08:00:00 GMT-0700 (Mountain Standard Time)78910111213141516171819202122
Mon Feb 10 2020 08:20:00 GMT-0700 (Mountain Standard Time)891011121314151617181920212223
Mon Feb 10 2020 08:40:00 GMT-0700 (Mountain Standard Time)9101112131415161718192021222324
Mon Feb 10 2020 09:00:00 GMT-0700 (Mountain Standard Time)10111213141516171819202122232425
Mon Feb 10 2020 09:20:00 GMT-0700 (Mountain Standard Time)11121314151617181920212223242526
Mon Feb 10 2020 09:40:00 GMT-0700 (Mountain Standard Time)12131415161718192021222324252627
Mon Feb 10 2020 10:00:00 GMT-0700 (Mountain Standard Time)13141516171819202122232425262728
Mon Feb 10 2020 10:20:00 GMT-0700 (Mountain Standard Time)14151617181920212223242526272829
Mon Feb 10 2020 10:40:00 GMT-0700 (Mountain Standard Time)15161718192021222324252627282930
Mon Feb 10 2020 11:00:00 GMT-0700 (Mountain Standard Time)16171819202122232425262728293031
Mon Feb 10 2020 11:20:00 GMT-0700 (Mountain Standard Time)17181920212223242526272829303132
Mon Feb 10 2020 11:40:00 GMT-0700 (Mountain Standard Time)18192021222324252627282930313233
Earl
Mon Feb 10 2020 06:00:00 GMT-0700 (Mountain Standard Time)12345678910111213141516
Mon Feb 10 2020 06:20:00 GMT-0700 (Mountain Standard Time)234567891011121314151617
Mon Feb 10 2020 06:40:00 GMT-0700 (Mountain Standard Time)3456789101112131415161718
Mon Feb 10 2020 07:00:00 GMT-0700 (Mountain Standard Time)45678910111213141516171819
Mon Feb 10 2020 07:20:00 GMT-0700 (Mountain Standard Time)567891011121314151617181920
Mon Feb 10 2020 07:40:00 GMT-0700 (Mountain Standard Time)6789101112131415161718192021
Mon Feb 10 2020 08:00:00 GMT-0700 (Mountain Standard Time)78910111213141516171819202122
Mon Feb 10 2020 08:20:00 GMT-0700 (Mountain Standard Time)891011121314151617181920212223
Mon Feb 10 2020 08:40:00 GMT-0700 (Mountain Standard Time)9101112131415161718192021222324
Mon Feb 10 2020 09:00:00 GMT-0700 (Mountain Standard Time)10111213141516171819202122232425
Mon Feb 10 2020 09:20:00 GMT-0700 (Mountain Standard Time)11121314151617181920212223242526
Mon Feb 10 2020 09:40:00 GMT-0700 (Mountain Standard Time)12131415161718192021222324252627
Mon Feb 10 2020 10:00:00 GMT-0700 (Mountain Standard Time)13141516171819202122232425262728
Mon Feb 10 2020 10:20:00 GMT-0700 (Mountain Standard Time)14151617181920212223242526272829
Mon Feb 10 2020 10:40:00 GMT-0700 (Mountain Standard Time)15161718192021222324252627282930
Mon Feb 10 2020 11:00:00 GMT-0700 (Mountain Standard Time)16171819202122232425262728293031
Mon Feb 10 2020 11:20:00 GMT-0700 (Mountain Standard Time)17181920212223242526272829303132
Mon Feb 10 2020 11:40:00 GMT-0700 (Mountain Standard Time)18192021222324252627282930313233
Francis
Mon Feb 10 2020 06:00:00 GMT-0700 (Mountain Standard Time)12345678910111213141516
Mon Feb 10 2020 06:20:00 GMT-0700 (Mountain Standard Time)234567891011121314151617
Mon Feb 10 2020 06:40:00 GMT-0700 (Mountain Standard Time)3456789101112131415161718
Mon Feb 10 2020 07:00:00 GMT-0700 (Mountain Standard Time)45678910111213141516171819
Mon Feb 10 2020 07:20:00 GMT-0700 (Mountain Standard Time)567891011121314151617181920
Mon Feb 10 2020 07:40:00 GMT-0700 (Mountain Standard Time)6789101112131415161718192021
Mon Feb 10 2020 08:00:00 GMT-0700 (Mountain Standard Time)78910111213141516171819202122
Mon Feb 10 2020 08:20:00 GMT-0700 (Mountain Standard Time)891011121314151617181920212223
Mon Feb 10 2020 08:40:00 GMT-0700 (Mountain Standard Time)9101112131415161718192021222324
Mon Feb 10 2020 09:00:00 GMT-0700 (Mountain Standard Time)10111213141516171819202122232425
Mon Feb 10 2020 09:20:00 GMT-0700 (Mountain Standard Time)11121314151617181920212223242526
Mon Feb 10 2020 09:40:00 GMT-0700 (Mountain Standard Time)12131415161718192021222324252627
Mon Feb 10 2020 10:00:00 GMT-0700 (Mountain Standard Time)13141516171819202122232425262728
Mon Feb 10 2020 10:20:00 GMT-0700 (Mountain Standard Time)14151617181920212223242526272829
Mon Feb 10 2020 10:40:00 GMT-0700 (Mountain Standard Time)15161718192021222324252627282930
Mon Feb 10 2020 11:00:00 GMT-0700 (Mountain Standard Time)16171819202122232425262728293031
Mon Feb 10 2020 11:20:00 GMT-0700 (Mountain Standard Time)17181920212223242526272829303132
Mon Feb 10 2020 11:40:00 GMT-0700 (Mountain Standard Time)18192021222324252627282930313233

awesome, will test shortly and get back to you thanks for this, I'm sure many others will benefit. — Umar.H, Feb 11 '20 at 09:43
I've added in all the scripts but dailysheets.gs errors at line 17 saying `missing ; before statement` I've added a demo sheet in the main post. where I've attempted to implement — Umar.H, Feb 11 '20 at 13:15
I'm sorry but I don't follow links off of SO any longer. If you want to post the csv for that page that would be great. — Cooper, Feb 11 '20 at 19:04
Thanks so much cooper, I tried my best to get this to work but it was beyond me - great bits of code tho it will definitely benefit others. Thanks! — Umar.H, Feb 12 '20 at 12:36

edmund chan kei yun · Answer 3 · 2020-02-12T04:32:43.487

The following script does the following:

1) Archive Daily regardless of the other tabs naming convention (only the master tab needs to be named correctly)

2) Ignores header rows in other tabs (presuming master have header row)

3) Everyday Trigger

4) Added function of archiving previous master in case scripts run before you accessed the data - only 1 day archive

The EverydayTrigger function creates the daily loop, and the MergeSheet function is the main script that merges the sheets. The explanations for each line of script is in the script itself.

function EverydayTrigger() {
  ScriptApp.newTrigger("MergeSheets")
  .timeBased()
  .everyDays(1)
  .create();
}

function MergeSheets() {
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var data = null;
  var RetrieveSheet = null;
  var PasteSheet = ss.getSheetByName("Master");

  /*
  // Use this if you are using the archiving option
  var PrevArchSheetName = PasteSheet.getRange(1,1000).getValue();

  if(PrevArchSheetName.length > 1){
    var PrevArchSheet = ss.getSheetByName(PrevArchSheetName); //Retrieve the stored Archive Sheet's Name
    ss.deleteSheet(PrevArchSheet);
  }
  */

  var sheets = ss.getSheets();//get all sheets regardless of naming conventions (allow you to expand your data source)

  /*
  //This portion can be used to allow for archiving of the previous day data in case you still need it.
  var ArchiveSheet = ss.insertSheet(new Date() + "Master");
  PasteSheet.getDataRange().copyTo(ArchiveSheet.getRange(1,1));
  PasteSheet.getRange(1,1000).setValue(ArchiveSheet.getName()); // stores the name of the sheet so as to delete it when the script run again
  */

  PasteSheet.getRange(2,1,PasteSheet.getLastRow(),PasteSheet.getLastColumn()).clear();//removes all old information

  for (var i =0; i<sheets.length; i++){
    RetrieveSheet = ss.getSheetByName(sheets[i].getName());
    if (RetrieveSheet.getName() != 'Master'){
      //data = RetrieveSheet.getDataRange(); //use this if you want the header row for each salesperson
      data = RetrieveSheet.getRange(2,1,RetrieveSheet.getLastRow(),RetrieveSheet.getLastColumn()); //presuming you dont want the header rows
      data.copyTo(PasteSheet.getRange(parseInt(PasteSheet.getLastRow())+1,1));
    }
  }
}

score 0 · Answer 4 · answered Jun 22 '18 at 11:34

0

Try the SheetGo add-on it will help to do the work in few clicks

have a look at this: https://blog.sheetgo.com/how-to-solve-with-sheetgo/merge-data-multiple-google-sheets/

answered Jun 22 '18 at 11:34

ronakruparel10

120
8

1

I'm wary of any add-ons due to data privacy. In the Post GDPR world we have to be a little stricter with our data. – Umar.H Jun 22 '18 at 11:45
https://stackoverflow.com/questions/37727196/how-can-i-merge-multiple-tabs-in-a-google-spreadsheet-using-google-app-script maybe this will help – ronakruparel10 Jun 22 '18 at 12:21

Google App Script to merge multiple sheets into a master

4 Answers4