Email Specified Range in Current Active Sheet as PDF

user_15301424 0
Viewed 119

I want to email a specified range from the current active google sheet.There is an error with this code,it says TypeError: SpreadsheetApp.getActiveSpreadsheet(...).getRange(...).getAs is not a function Line 9

function sendReport() {
   SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Responses").hideSheet();
   SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form").activate();
  var message = {
    to: SpreadsheetApp.getActiveSheet().getRange('L2').getValue(),
    subject: "Monthly sales report",
    body: "Hi team,\n\nPlease find the monthly report attached.\n\nThank you,\nBob",
    name: "Bob",
    attachments: [SpreadsheetApp.getActiveSpreadsheet().getRange("Form!A5:H25").getAs(MimeType.PDF).setName("Monthly sales report")]
  }
  MailApp.sendEmail(message);
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Responses").activate();
}
google-apps-script spreadsheet google-sheets pdf email
1 Answers

You cannot convert a sheet content to pdf with getAs(MimeType.PDF) alone

A way to send a sheet range as a pdf attachment would be to convert it a pdf blob first, as perfomed by Alan Wells in the post linked by JPV.

Based on this conversion, you can modify your code as following:

var ss = SpreadsheetApp.getActiveSpreadsheet();

function sendReport() {
  var sheetTabNameToGet = "Form";
  var range = "A5:H25";
  var pdfBlob = exportRangeToPDf(range, sheetTabNameToGet);
  var message = {
    to: ss.getSheetByName(sheetTabNameToGet).getRange('L2').getValue(),
    subject: "Monthly sales report",
    body: "Hi team,\n\nPlease find the monthly report attached.\n\nThank you,\nBob",
    name: "Bob",
    attachments: [pdfBlob.setName("Monthly sales report")]
  }
  MailApp.sendEmail(message);
}


function exportRangeToPDf(range, sheetTabNameToGet) {
  var blob,exportUrl,options,pdfFile,response,sheetTabId,ssID,url_base;
  ssID = ss.getId();
  sh = ss.getSheetByName(sheetTabNameToGet);
  sheetTabId = sh.getSheetId();
  url_base = ss.getUrl().replace(/edit$/,'');
  exportUrl = url_base + 'export?exportFormat=pdf&format=pdf' +
    
    '&gid=' + sheetTabId + '&id=' + ssID +
      '&range=' + range + 
        '&size=A4' +     // paper size
          '&portrait=true' +   // orientation, false for landscape
            '&fitw=true' +       // fit to width, false for actual size
              '&sheetnames=true&printtitle=false&pagenumbers=true' + //hide optional headers and footers
                '&gridlines=false' + // hide gridlines
                  '&fzr=false';       // do not repeat row headers (frozen rows) on each page
  
  options = {
    headers: {
      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
    }
  }
  options.muteHttpExceptions = true;//Make sure this is always set
  response = UrlFetchApp.fetch(exportUrl, options);
  if (response.getResponseCode() !== 200) {
    console.log("Error exporting Sheet to PDF!  Response Code: " + response.getResponseCode());
    return;
    
  }  
  blob = response.getBlob();
  return blob;
}
user_115997890
Related