Merge multiple Google Sheets into one with multiple tabs

Question

I have 30 separate sheets that need to be merged into one sheet with multiple tabs. I am able to do this using the below script, but it is creating a new sheet every time. When one or all of the 30 separate sheets are updated, I just want this script to update or rewrite the previously creates "Master Sheet".

    function mergeSheets() 
{

  /* Retrieve the desired folder */
  var myFolder = DriveApp.getFoldersByName("Email Groups").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("All Districts Email");

  /* 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); 
    }
  }      
}

I am looking for one sheet that contains a tab from every district. This will allow me to share one master sheet with all information that was updated separately.

Have you tried using the built in IMPORTRANGE() function instead? Once your "Master Sheet" is created there is no need to do anything else. This will update the sheet (near) real time with each edit to the imported files. — Karl_S
Thank you very much, I never thought of using that. I does require me to create 30 separate tabs and ranges, but it will work. — Jeremy Hunter
You could always write a script to create the tabs and add the formula. ;-) — Karl_S

player0 player0 · Accepted Answer · 2019-04-17T20:41:07

={IMPORTRANGE("ID1", "Sheet1!A1:B10"),
  IMPORTRANGE("ID1", "Sheet2!A1:B10"),
  IMPORTRANGE("ID1", "Sheet3!A1:B10"),
  IMPORTRANGE("ID2", "Sheet1!A1:B10"),
  IMPORTRANGE("ID2", "Sheet2!A1:B10"),
  IMPORTRANGE("ID3", "Sheet8!A1:B10"),
  IMPORTRANGE("ID4", "Sheet7!A1:B10"),
  IMPORTRANGE("ID4", "Sheet9!A1:B10")}

etc...

also note, that every IMPORTRANGE formula with unique ID needs to be pasted separately first to allow access

Merge multiple Google Sheets into one with multiple tabs

1 Answers