Google Apps Script - sending pdf in email. Error in attachment portion

Question

I have a script that sends an email and my code is not letting setname (in gs:14) for the pdf email attachment. Would someone be able to help me out here? The error code states: "TypeError: Cannot read property 'setName' of undefined."

Thank you.

var ss = SpreadsheetApp.getActiveSpreadsheet();

function sendReport1() {
  var sheetTabNameToGet = "Weekly Email";
  var range = "A1:O91";
  var name = ss.getSheetByName(sheetTabNameToGet).getRange("B2").getValue();
  var d = Utilities.formatDate(new Date(), "GMT+1", "MM/dd/yyyy");
  var pdfBlob = exportRangeToPDf(range,sheetTabNameToGet);
  var message = {
    to: ss.getSheetByName(sheetTabNameToGet).getRange('P1').getValue(),
    subject: "Your Weekly Metrics Report | " +d,
    body: "Hello "+name+",\n\nPlease find attached your individual metrics report for last week.\n\nIf you have any questions, feel free to reply all to this email or come find any of your leaders to discuss any questions you may have!\n\nThank you,\n\nDetroit Leadership Team",
    name: "Detroit Leadership Team", 
    attachments: [pdfBlob.setName("Weekly Email")]
  }
  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=letter' +     // paper size
          '&portrait=true' +   // orientation, false for landscape
            '&fitw=true' +       // fit to width, false for actual size
             '&top_margin=0.75' +             
               '&bottom_margin=0.75' +         
                '&left_margin=0.25' +            
                 '&right_margin=0.25' +
                  '&scale=0.43' +
                   '&sheetnames=false&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;
}

score 0 · Answer 1 · answered Aug 24 '21 at 09:10

Issue:

You are using an invalid value for the export parameter scale.

Explanation:

The error you are getting means pdfBlob is undefined, so the function exportRangeToPDf is not returning anything. This means, based on the function code, that the response code from the UrlFetch call is not 200, and the execution is entering the if block and returning undefined:

if (response.getResponseCode() !== 200) {
  console.log("Error exporting Sheet to PDF!  Response Code: " + response.getResponseCode());
  return;
}

Therefore, this method is not exporting the spreadsheet correctly:

response = UrlFetchApp.fetch(exportUrl, options);

And that is due to the scale property, which, as you can see on this answer, can currently only have four values (beware, all these export parameters are not officially documented, so they could stop working without warning):

1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page

Since you wrote scale=0.43, this is an invalid value and the spreadsheet is not exported. Please change scale to one of the accepted values (e.g. scale=1). Once you do that, the file should be exported successfully and the email should be sent.

Google Apps Script - sending pdf in email. Error in attachment portion

1 Answers1

Issue:

Explanation: