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 — Wicket
– Wicket, Commented Aug 24, 2020 at 15:13

Marios · Accepted Answer · 2020-08-24 19:00:03Z

1

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).

edited Aug 24, 2020 at 19:00

answered Aug 24, 2020 at 15:34

Marios

27.5k9 gold badges40 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sunny Jayadev Over a year ago

Marios, thank you for the solution. 1)But, this code is appending data along with the Header Column of each sheet which is not required. It has to copy header only once and skip for all the other sheets. 2) Also, this code is creating new Google Sheet file every time with name "MergedNew" with the appended data and not updating the previously created file. It has to refresh the file with new appended data instead creating new file everytime.

Marios Over a year ago

@SunnyJayadev check my updated answer. I made all the changes you mentioned. Please accept the solution if it answered your question.

Sunny Jayadev Over a year ago

this code is working fine only when the sheet with the name "B" is in second place in Marios, the order of sheets. Is it possible to pick the sheet name so that, it need not be in a particular order of the sheets? Also, while appending the data from multiple sheets, it is including blank rows as well. Is it possible to skip the blank rows? Please help me with these 2 enhancements to the above code.

Marios Over a year ago

@SunnyJayadev unfortunately stackoverflow does not allow follow up questions. Please post a new question regarding your issue and I will be very much happy to help you out .

Collectives™ on Stack Overflow

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

1 Answer 1

Solution:

Explanation:

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Solution:

Explanation:

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related