Google Sheets Email specific sheets as CSV

Question

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.

Does this answer your question? [Download link for Google Spreadsheets CSV export - with Multiple Sheets](https://stackoverflow.com/questions/33713084/download-link-for-google-spreadsheets-csv-export-with-multiple-sheets) — Rubén, Jul 18 '20 at 21:47
Related [Force download link on a google docs spreadsheet](https://stackoverflow.com/q/6058146/1595451) — Rubén, Jul 18 '20 at 22:04
Thank you for your answers, but those are related to downloading the CSV, I'm trying to send an email with multiple sheets as attachments, those suggested questions are related to generating a download link. — Excelsson, Jul 18 '20 at 23:08
Based on the research it creates HTTPS requests, which is used to include the attachment in the email, right? — Excelsson, Jul 18 '20 at 23:32
The ideas are on the links provided previously. Summary: You have to use `gid` parameter to set which sheet will be exported otherwise the first sheet will be exported. — Rubén, Jul 18 '20 at 23:55

score 1 · Accepted Answer · edited Jul 19 '20 at 17:31

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

Google Sheets Email specific sheets as CSV

1 Answers1