CODEWITHSUNDEEP
mergegoogle-apps-scriptgoogle-sheetsgoogle-apps
Maksim
Maksim

Reputation: 16931

Merge Google Spreadsheets in a folder with Google Apps Script

I'm trying to automate a process to merge spreadsheets. I've written two blog posts (here and here) describing how to do that using formulas. Now I'm trying to make this process to be much simpler by allowing people to merge spreadsheets inside the folder or pick what spreadsheets to merge automatically. If possible I would like to do all this using Google Apps Script. So I was wondering what is the best way to:

  1. get keys of all spreadsheets in the folder
  2. have a "file chooser" like window to pick spreadsheets that user need to merge

This two options will allow me to enable to automate the script that I have in the blog posts.

Upvotes: 0

Views: 4629

Answers (1)

br araujo
br araujo

Reputation: 1872

I guess that you can build your own interface. To help with the logic I put all the necessary functions to merge the shhets bellow:

You can see the live version here.

function myFunction() {

  /* Retrieve the desired folder */
  var myFolder = DriveApp.getFolderById("0B0kQD4hSd4KASUJKb2cya0NET1U");

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

Upvotes: 4

Related Questions