Append multiple Google Sheets files into one Google Sheet using App Script

Question

I have 100 Google Sheet files in one folder of Google Drive. Each Google sheet file has 10 sheets (A,B,C,D,E,F,G,H,I,J). I wanted to append all 100 Google Sheet files into one Google Sheet appending data from "B" sheets of all the 100 files. All the sheets has same columns. I have tried the below code. But it is pulling all "B" sheets from 100 files in to one Google Sheet but not appending the data from sheets into one.

function myFunction() 
{
  var myFolder = DriveApp.getFolderById("1ZtxfMNDn3uFhCcdcp5unfmTwUIJ_3fZK");
  var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet");
  var newSpreadSheet = SpreadsheetApp.create("MergedNew");
  while(spreadSheets.hasNext()) 
  {
    var sheet = spreadSheets.next();
    var spreadSheet = SpreadsheetApp.openById(sheet.getId());
    for(var y in spreadSheet.getSheets()) 
    {
      spreadSheet.getSheets()[y].copyTo(newSpreadSheet); 
     }
  }      
}

Does this answer your question? Consolidate data into one master Google sheet using App Scripts — Rubén

soMario soMario · Accepted Answer · 2020-08-24T15:34:24

Solution:

function myFunction() {

  var myFolder = DriveApp.getFolderById("1ZtxfMNDn3uFhCcdcp5unfmTwUIJ_3fZK");
  var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet");
  
  var master_files = DriveApp.getFilesByName("MergedNew")
      
  if (master_files.hasNext()){
     var master_file=master_files.next();
     var newSpreadSheet = SpreadsheetApp.openById(master_file.getId());
     }
  else { 
    var newSpreadSheet = SpreadsheetApp.create("MergedNew");
    newSpreadSheet.getSheets()[0].setName('B data');
  }
  
  var bSheet = newSpreadSheet.getSheetByName('B data');
  while(spreadSheets.hasNext()) 
  {
    var sheet = spreadSheets.next();
    var spreadSheet = SpreadsheetApp.openById(sheet.getId());
    var sh = spreadSheet.getSheets()[1];
    var data = sh.getRange(2,1,sh.getMaxRows(),sh.getMaxColumns()).getValues();
    
    if (bSheet.getLastRow() == 0){
      var headers = sh.getRange(1,1,1,sh.getMaxColumns()).getValues();
      bSheet.getRange(1,1,1,sh.getMaxColumns()).setValues(headers);
    }
    
    bSheet.getRange(bSheet.getLastRow()+1,1,data.length,data[0].length).setValues(data);
  }      
}

Explanation:

We iterate through all spreadsheet files in a particular folder specified by a folderId.
The script will create a new Spreadsheet with the name MergedNew. If the latter exists, then it will just get the existing one. This spreadsheet will contain all the data of B sheets of all the spreadsheet files in the folder specified in the previous step.
It will then append the data of each B sheet into sheet B data of MergedNew.
The headers will only be copied to the master file the first time you run the script.

I assume here var sh = spreadSheet.getSheets()[1]; that sheet B in every file is at the second position. Namely, (A,B,C,D,E,F,G,H,I,J).

Append multiple Google Sheets files into one Google Sheet using App Script

1 Answers

Solution:

Explanation: