Merging multiple google sheets into one sheet faster

Question

I have a google sheet where I have around 50-60 more tabs. All the data in those sheets is in the same format, I just need to merge together into a master spreadsheet. I tried the second answer here but it throws me an error with the excess time limit. It is pretty slow because it pastes every row - so it exceeds the time limit. I am trying to use the getValues and setValues function to do it, but I have issues figuring out how to append after the lastRow and lastColumn. Here is the code -

var ss = SpreadsheetApp.getActiveSpreadsheet();
var All = ss.insertSheet("MergedMasterSheet");
function TotalsSheet() {
   var sheets = ss.getSheets();
   for (var i=0; i < sheets.length; i++) 
    {
     var sheet = sheets[i];
     var range = sheet.getDataRange();
     var values = range.getValues();
     var lastrow = All.getLastRow();
     var lastcolumn = All.getLastColumn();
    // All.getRange().setValues(values) here is the problem with the 
    getRange() function
  } 
 }

use the size of `values` to help you know the size of the range to acquire. `values` is a JavaScript Array. You will want to use a 4-parameter version of `Sheet#getRange` when writing the contents of `values` to the desired sheet. — tehhowch, Jan 20 '19 at 08:09

JPV · Accepted Answer · 2019-01-17T15:16:23.217

you can write all the data to one array and then write this array to the "MergedMasterSheet". See if this works:

function TotalsSheet() {
var ss, merged, array;
ss = SpreadsheetApp.getActiveSpreadsheet();
merged = ss.getSheetByName("MergedMasterSheet")

//create sheet if it doesn't exist, else clear all content
merged = !merged ? ss.insertSheet("MergedMasterSheet") : merged.clear();

//create a 2D-array containing all data of all sheets, except the sheet for the merged data
ss.getSheets().forEach(function (sh, i) {
    if(sh.getName() !== "MergedMasterSheet") {
    array = (i == 0) ? sh.getDataRange().getValues() : array.concat(sh.getDataRange().getValues())
    }
})

//filter out rows with no value in first column
array = array.filter(function (r) {
    return r[0];
})

//write everything to the merged sheet in one call
merged.getRange(1, 1, array.length, array[0].length).setValues(array)
}

Merging multiple google sheets into one sheet faster

1 Answers1