How to Merge Multiple Spreadsheet files in Google Drive

Handling multiple Google Spreadsheet containing data of similar work becomes a headache, but you can easily merge multiple Spreadsheet into on in Google drive with its import feature. You can either join spreadsheet files by adding new sheets, one after one or use a simple script to add all spreadsheet at once. We will discuss both methods here. The import options for merging sheets is very easy but takes lots of time and script method requires you to put all files you want to merge into a folder and run the script.
I have recently started working with Google Spreadsheet and liking it because of easy to use and on the go feature. Google Drive helps to work on the same file on multiple devices that increases your productivity, all you need is Google Drive's app on your Laptop, Tablet, and Smartphone. I don’t have drive app installed on my laptop because I prefer using it with Google Chrome and my files are regularly updated compared to Drive app that syncs your work time to time.

Merging Multiple Spreadsheet One by One

If you are going to merge more than a couple of files than using import feature can be a headache because it takes time and repetitive work each time. In the case of combining 1-2 excels, files is not a problem.

• To start importing, you need to create a new spreadsheet.
  
• Now click on “Files” option.
• Choose “Import” then select a file from browser menu and click on select.
• Now you can opt to add a new sheet or replace data in the current sheet.

You can refresh the page once importing is finished to get updated preview of the spreadsheet. This method can consume lots of time if you are going to merge more than two, three files, you can try script method to combine all spreadsheet in a folder into one just by running a small code.

Combining multiple worksheets into one with a script

First of all create a folder and put all spreadsheet file you want to merge into one. Now create a new spreadsheet and go to Tools > Script Editor. Now copy and paste below script in newly opened script page.

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

After copy page saves and run the script. It will ask you for file permission, allow and let the script do its job. It will automatically merge all spreadsheet file available in that folder. (code source)