IDEAS PORTAL

Combine multiple gsheets into one

currently get report back with 162 google sheets.  Do not want to open each of them.

  • Avatar32.5fb70cce7410889e661286fd7f1897de Guest
  • Oct 10 2018
  • Archived
Connector
Future Connector Request
  • Attach files
  • Admin
    Peter Bilali commented
    October 12, 2018 13:49

    Hey Chris,

    How large is the report? There might be a technical limitation causing us to separate into multiple sheets.

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    Guest commented
    October 12, 2018 13:59

    Hi Peter,

     

    Report is 162 sheets, each has about 1000 entries

     I did get this from BetterIT apps script on slack.  Have had time to do anything with it yet.

     

    Thanks

     

    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); 
        }
      }      
    }
  • Admin
    Peter Bilali commented
    October 12, 2018 14:12

    Which BetterCloud report is it?

  • Avatar40.8f183f721a2c86cd98fddbbe6dc46ec9
    Guest commented
    October 12, 2018 14:21

    It what I get when I export the External Owned Documents

  • Admin
    Peter Bilali commented
    October 15, 2018 14:08

    Hey Chris, looks like this is a confirmed bug. I'll go ahead and close this since it should be remedied via normal bug priority work internally.

All new ideas are reviewed by our team before being made public. You can expect an update on your new idea within 48 hours.