1

I'm trying to send a PDF file via email. I saw a code here on how to send an email and it kinda works, but it generates all the visible sheets (hidden sheet is not included) in the PDF file. I already modified it to generate the active sheet but all the data on that sheet is still visible. I have a named range and it's called "print_area_1". Is it possible if I only want to show the "print_area_1" part on the PDF file?

Here's my modified code for it.

function sendSheetToPdfwithA1MailAdress(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var shName = sh.getName()
  sendSpreadsheetToPdf(0, shName, sh.getRange('Q12').getValue(), sh.getRange('D12').getValue() , "This is it !");
}
function sendSpreadsheetToPdf(sheetNumber, pdfName, email,subject, htmlbody, url, sheet, range) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var spreadsheetId = ss.getId()  
  var url_base = ss.getUrl().replace(/edit$/,'');

  var rangeParam = ''
  var sheetParam = ''
  if (range) {
    rangeParam = 
      + '&r1=' + (range.getRow() - 1)
      + '&r2=' + range.getLastRow()
      + '&c1=' + (range.getColumn() - 1)
      + '&c2=' + range.getLastColumn()
  }
  if (sheet) {
    sheetParam = '&gid=' + sheet.getSheetId()
  }
 
  var url_ext = 'export?exportFormat=pdf&format=pdf'
      + '&size=folio'
      + '&portrait=true'
      + '&fitw=true'
      + '&top_margin=0.5'              
      + '&bottom_margin=0.5'          
      + '&left_margin=0.5'             
      + '&right_margin=0.5'        
      + '&sheetnames=false&printtitle=false&pagenumbers=false'
      + '&gridlines=true'
      + '&fzr=false'
      + sheetParam
      + rangeParam;

  var options = {
    headers: {
      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
    }
  }

  var response = UrlFetchApp.fetch(url_base + url_ext, options);
  var blob = response.getBlob().setName(pdfName + '.pdf');
  if (email) {
    var mailOptions = {
      attachments:blob, htmlBody:htmlbody
    }
MailApp.sendEmail(
      email, 
      subject+" (" + pdfName +")", 
      "html content only", 
      mailOptions);
  }
}
cjvdg
  • 497
  • 2
  • 15
  • 1
    You need to adjust your range accordingly and use `getRangeByName` . I believe [this answer](https://stackoverflow.com/a/12633583/1527780) and [this](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getRangeByName(String)) will put you on the right track. – marikamitsos Nov 10 '20 at 03:14
  • 1
    @marikamitsos I made it work! Thanks to you! – cjvdg Nov 10 '20 at 03:22
  • Glad I could help. You can also post your modified code [as an answer](https://stackoverflow.com/help/self-answer) so others can be helped. – marikamitsos Nov 10 '20 at 03:26
  • @marikamitsos will do. Thanks! – cjvdg Nov 10 '20 at 03:29

1 Answers1

1

I just added this script / variable to make it work.

var range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName('print_area_1');

Here's the full code:

function sendSheetToPdfwithA1MailAdress(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var shName = sh.getName()
  sendSpreadsheetToPdf(0, shName, sh.getRange('Q12').getValue(), sh.getRange('D12').getValue() , "This is it !");
}
function sendSpreadsheetToPdf(sheetNumber, pdfName, email,subject, htmlbody, url, sheet, range) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var spreadsheetId = ss.getId()  
  var url_base = ss.getUrl().replace(/edit$/,'');

  var rangeParam = ''
  var sheetParam = ''
  if (range) {
    rangeParam = 
      + '&r1=' + (range.getRow() - 1)
      + '&r2=' + range.getLastRow()
      + '&c1=' + (range.getColumn() - 1)
      + '&c2=' + range.getLastColumn()
  }
  if (sheet) {
    sheetParam = '&gid=' + sheet.getSheetId()
  }
 
  var url_ext = 'export?exportFormat=pdf&format=pdf'
      + '&size=folio'
      + '&portrait=true'
      + '&fitw=true'
      + '&top_margin=0.5'              
      + '&bottom_margin=0.5'          
      + '&left_margin=0.5'             
      + '&right_margin=0.5'        
      + '&sheetnames=false&printtitle=false&pagenumbers=false'
      + '&gridlines=true'
      + '&fzr=false'
      + sheetParam
      + rangeParam;

  var options = {
    headers: {
      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
    }
  }

  var response = UrlFetchApp.fetch(url_base + url_ext, options);
  var blob = response.getBlob().setName(pdfName + '.pdf');
  if (email) {
    var mailOptions = {
      attachments:blob, htmlBody:htmlbody
    }
MailApp.sendEmail(
      email, 
      subject+" (" + pdfName +")", 
      "html content only", 
      mailOptions);
  }
}
cjvdg
  • 497
  • 2
  • 15