google-apps-scriptgoogle-sheetsgmailurlfetchgoogle-sheets-export-url

Google Sheets Email specific sheets as CSV


I have been looking around the forums and I've been unable to find a solution to this, I'm trying to email multiple sheets as separate attachments in the same email, so far I'm able to email as attachment only one, but can't seem to find the way to select multiple sheets to add to the attachments, below my code:


  function sendTimelist() {

var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Configure").getRange("B2");

    var email = emailRange.getValues();


  var emailAddress = email;
  var mailReplyTo = email;
  var subject = 'Weekly Email Alerts';
  var message = '\bHi team\b,\n\nPlease find the Data of Sheets.\n\nThank you,\.';

  
  
  
  
  var attachmentName = 'Test.csv';
  var fileId = "Ac12154d4s5dsd87s8d";
  var timelist = UrlFetchApp.fetch(
    "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + fileId + "&exportFormat=csv",
    {
      "headers": {Authorization: "Bearer " + ScriptApp.getOAuthToken()},
      "muteHttpExceptions": true
    }
  ).getBlob().setName(attachmentName);
  var optAdvancedArgs = {replyTo: mailReplyTo, name: attachmentName, attachments: [timelist] };

  GmailApp.sendEmail(emailAddress, subject, message, optAdvancedArgs);
}

Any ideas? Thanks.


Solution

  • I have a very complicated script that does this but I have extracted and changed this so that it will suit your purpose and export type etc...

    function sendPdf() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sh1 = ss.getSheetByName('Sheet1');//<<--------<<-----DEFINE YOUR FIRST SHEET
      var sh2 = ss.getSheetByName('Sheet2');//<<--------<<-----DEFINE YOUR SECOND SHEET
      var exportName1 = "Test1";//<<--------<<------<<--------DEFINE THE 1st ATTACHMENT NAME
      var exportName2 = "Test2";//<<--------<<------<<--------DEFINE THE 2nd ATTACHMENT NAME
      var url1 = 'https://docs.google.com/spreadsheets/d/'+ss.getId()+'/export?exportFormat=csv&format=csv'
      + '&size=A4'                           
      + '&portrait=false'                     
      + '&fitw=true' 
      + '&top_margin=0.60'              
      + '&bottom_margin=0.60'          
      + '&left_margin=0.60'             
      + '&right_margin=0.60'  
      + '&sheetnames=false&printtitle=false' 
      + '&pagenumbers=false&gridlines=false' 
      + '&fzr=false'                         
      + '&gid='+sh1.getSheetId();
      var url2 = 'https://docs.google.com/spreadsheets/d/'+ss.getId()+'/export?exportFormat=csv&format=csv'
      + '&size=A4'                           
      + '&portrait=false'                     
      + '&fitw=true' 
      + '&top_margin=0.60'              
      + '&bottom_margin=0.60'          
      + '&left_margin=0.60'             
      + '&right_margin=0.60'  
      + '&sheetnames=false&printtitle=false' 
      + '&pagenumbers=false&gridlines=false' 
      + '&fzr=false'                         
      + '&gid='+sh2.getSheetId();
      var response1 = UrlFetchApp.fetch(url1, {
        headers: {
          'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken()}
      });
      var response2 = UrlFetchApp.fetch(url2, {
        headers: {
          'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken()}
      });
      var blob1 = response1.getBlob().setName(exportName1);
      var blob2 = response2.getBlob().setName(exportName2);
      MailApp.sendEmail({to: 'email@email.com', //<<--------<<------EMAIL TO SEND TO
                         subject: 'Send Multiple Sheets',//<<-------EMAIL SUBJECT
                         message: 'See attached',//<<--------<<-----EMAIL BODY - I WOULD GENERALLY USE htmlBody BUT IN THIS CASE EDITED TO NOT GET TOO CONFUSING
                         name: 'Send Sheets',//<<--------<<------<<-NAME THAT APPEARS WHEN EMAIL SENT
                         replyTo: 'no-reply@email.com',//<<--------<WHERE A REPLY TO THE EMAIL WILL GO
                         attachments: [{
                           fileName: exportName1,
                           content: blob1.getBytes(),
                           mimeType: "text/csv"},{
                           fileName: exportName2,
                           content: blob2.getBytes(),
                           mimeType: "text/csv"}],
                        });
    }
    

    This could probably be edited to be more neat using for() etc but this keeps it simple to start with. To add more sheets it is pretty much copy and paste the codes and then change relevant var's etc