CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
Jeremy Hunter
Jeremy Hunter

Reputation: 13

Merge multiple Google Sheets into one with multiple tabs

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.

Upvotes: 0

Views: 234

Answers (1)

player0
player0

Reputation: 1

={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

Upvotes: 0

Related Questions