0

My code below for generate PDF attachment is working fine but they send all data in the spreadsheet.

Anyone can help to send only the specify spreadsheet like my code only sheet"Email" in stead of All sheets ? Thank you

function SendEmail() {

  try {
    var ss  = SpreadsheetApp.getActive();
    var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + ss.getId() + "&exportFormat=pdf";

    var params = {
      method      : "get",
      headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
    };

    var blob = UrlFetchApp.fetch(url, params).getBlob();
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Email')
    var subjectrange = sheet.getRange("A20");
    var subjectdata = subjectrange.getValues();
    var emailranges=sheet.getRange('E14');
    var emaildata=emailranges.getValue();
    var username=ss.getSheetId();
    blob.setName(ss.getName()+ ".pdf");
    var confirm = Browser.msgBox('Send Email Confirmation','Are you sure you want to send this mail for booking request ?', Browser.Buttons.OK_CANCEL);
    if(confirm=='ok') {
      MailApp.sendEmail(emaildata, subjectdata, " Attachment file is:  \n"  +subjectdata+ "- \n Kindly reply your booking to this email . \n Thank you - ADS Co., Ltd", {attachments: [blob]}) };} catch (f) {Logger.log(f.toString());
    }
}
Tu PHAN
  • 59
  • 6
  • 2
    Are these threads useful for your situation? https://stackoverflow.com/q/21997924 https://stackoverflow.com/q/44345093/7108653 By the way, when you provide your script, I would like to recommend to show it as a text. By this, the users can easy to copy and test and modify it. – Tanaike Jan 14 '21 at 09:14
  • 2
    `DO NOT post images of code, data, error messages, etc.` [how-to-ask](https://stackoverflow.com/help/how-to-ask) – myeongkil kim Jan 14 '21 at 10:11
  • 1
    Sorry for post images, i have post code correctly. – Tu PHAN Jan 14 '21 at 13:41

1 Answers1

1

Answer

If you don't want to export some sheets you can hide them. Furthermore, you can export a Spreadsheet with the method getBlob. Once you have made the export, you can undo the hide.

Small code assuming two sheets

var sheet = ss.getSheetByName('Unwanted Sheet')
sheet.hideSheet()
var blob = ss.getBlob()
blob.setName(ss.getName())
sheet.showSheet()

Full code working with many sheets

function exportSheet(sheetName) {    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() !== sheetName) {
      sheets[i].hideSheet()
    } 
  }
  var blob = ss.getBlob()
  blob.setName(ss.getName())
  for (var i = 0; i < sheets.length; i++) {
    sheets[i].showSheet()
  }
}

Some tips

  • You don't have to add .pdf to the blob name, it already understand that it is a pdf file and the extension will appear automatically
  • You can use GmailApp service instead of MailApp since it is more versatile and has more functionalities. The main reason to use MailApp is using that it doesn’t require the developer to be a Gmail user.

Reference

fullfine
  • 1,371
  • 1
  • 4
  • 11