Merging 140 separate google sheets into one

Question

I have 140 separate sheets and counting to merge into one separate sheet. The usual importrange or arrays etc are too much for the number of sheets I have to merge.

The heading row on each sheet is the same so I just need all the data in each sheet from row 3 onwards to be copied into the new master sheet. The amount of data in each is variable. I don't mind copying everything including the headings as I can delete them manually with a filter.

I found this code (https://ctrlq.org/code/19980-merge-google-spreadsheets) which I ran and I keep getting the error - Missing ) after argument list. (line 4, file "Code")

I set the SOURCE to the folder key where all the files I want to merge are kept. I'm new to writing scripts:

function mergeSheets() {

  /* Retrieve the desired folder */
  var myFolder = DriveApp.getFoldersByName(SOURCE).next();

  /* Get all spreadsheets that resided on that folder */
  var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet");

  /* Create the new spreadsheet that you store other sheets */  
  var newSpreadSheet = SpreadsheetApp.create("Merged Sheets");

  /* Iterate over the spreadsheets over the folder */
  while(spreadSheets.hasNext()) {

    var sheet = spreadSheets.next();

    /* Open the spreadsheet */
    var spreadSheet = SpreadsheetApp.openById(sheet.getId());

    /* Get all its sheets */
    for(var y in spreadSheet.getSheets()) {

      /* Copy the sheet to the new merged Spread Sheet */
      spreadSheet.getSheets()[y].copyTo(newSpreadSheet); 
    }
  }      
}

Could anyone tell me what the error means? I'm not missing any ). I suspect the problem is how I've defined the SOURCE?

Thanks

Is this the code as you have it in your code or is it the literal copy of the example? I can't seem to replicate your issue. — Robin Gertenbach
From what you have said, I don't think this code does what you expect. This is the documentation for this use of copyTo(newSpreadsheet) developers.google.com/apps-script/reference/spreadsheet/… It copies a tab/tabs i.e. sheet(s) from disparate spreadsheets into one spreadsheet (but still the same quantity of tabs, but now just in one spreadsheet). You need to define how you are going to locate all the 140 spreadsheets and their tabs. Are they all in one folder ... and are they the only files in that folder? Does each ss only contain the tab you need to copy? — David Tew
@RobinGertenbach I have the code as I have pasted it above except for SOURCE I have the key for the google drive folder where the 140 files are stored — Taps
@DavidTew its 140 files all in one folder with one tab that I want to copy into the one master file — Taps

LTi-GT LTi-GT · Accepted Answer · 2016-02-19T10:29:11

Try the first part something like this and try to figure it out from here.

var folder= DriveApp.getFolderById('Your_folderID');
var files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);//this is the right getFilesByType string
     while (files.hasNext()) {
       var sheet = SpreadsheetApp.open(files.next());
       var spreadSheet = SpreadsheetApp.openById(sheet.getId());
}
var ss = SpreadsheetApp.getActiveSpreadsheet();
var newSpreadSheet = ss.getActiveSheet();

Is it just one spreadsheet with 140 sheets or are the sheets divided over multiple spreadsheets? You might want to set a range as well when you say copyTo. something like

for(var y in spreadSheet.getSheets()) {

  var Allsheets = spreadSheet.getSheets[y]();
  var data = Allsheets.getDataRange().getValues();{
            var target = new Array();
            for(n=0;n<data.length;++n){
                target.push(data[n]);
        }
    }
    newSpreadsheet.getRange(2,1,target.length,target[0].length).setValues(target);
 }

Merging 140 separate google sheets into one

1 Answers