Search code examples
pdfgoogle-apps-scriptgoogle-sheetsgmailhttp-status-code-400

Why does this script send a corrupted PDF?

I've rewritten my code to match the other answers for similar issues, but it has not fixed the problem with my script. It sends the email with a pdf attachment and I don't get any errors, but I cannot open the file. I've tried multiple solutions posted on stackoverflow.

What could be causing the pdf to be corrupted after it is sent? I've restructured my url, but no luck.

function emailPDF() {

SpreadsheetApp.flush();

var ss = SpreadsheetApp.openByUrl('url')
var sheet = ss.getSheetByName('Reviews Due');

var row = 2;
var col = 2;

var test = sheet.getRange(row,col).getValue();

if (test == "New Employee Reviews"){

var token = ScriptApp.getOAuthToken();
var params = {
  headers: {
    'Authorization': 'Bearer ' + token,
  },
  'muteHttpExceptions' : true
};

var sheetId = sheet.getSheetId();

var url_base = "https://docs.google.com/spreadsheets/d/" + ss.getId() + "/" + "export?";

var url_ext = 'exportFormat=pdf'
+ '&format=pdf'
+ '&gid=' + sheetId
+ '&size=letter'
+ '&portrait=false'
+ '&fitw=true'
+ '&sheetnames=false'
+ '&printtitle=false'
+ '&pagenumbers=false'
+ '&gridlines=false'
+ 'fzr=false';

var response = UrlFetchApp.fetch(url_base + url_ext, params);
var blob = response.getBlob().setName("Reviews Due" + ".pdf");

Here's the url for the spreadsheet: https://docs.google.com/spreadsheets/d/1wmnw9Nl6B0e8IgVDgTEtGLzWhh1ssSgCLiOMsgs6Z64/edit#gid=1633156004

Here's the url when I export the sheet as a pdf: file:///C:/Users/gwaldo/Downloads/Master%20Progression%20Schedule%20-%20Reviews%20Due.pdf

When I changed the blob variable to include getAs() here is the pdf that it exported:non corrupted pdf, but no data

Logger.log(url_base + url_ext) = https://docs.google.com/spreadsheets/d/1wmnw9Nl6B0e8IgVDgTEtGLzWhh1ssSgCLiOMsgs6Z64/export?Format=pdf&format=pdf&gid=1633156004&size=letter&portrait=false&fitw=true&sheetnames=false&printtitle=false&pagenumbers=false&gridlines=falsefzr=false

%PDF-1.4%����40obj<</Type/Catalog/Names<<
/JavaScript30R>>/PageLabels<</Nums[0<</S/D/St1>>]>>
/Outlines20R/Pages10R>>endobj50obj<</Creator(��

<!DOCTYPE html><html lang="en"><head><meta name="description" content="Web 
word processing, presentations and spreadsheets"><meta name="viewport" 
content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum- 
scale=1.0, user-scalable=0"><link rel="shortcut icon" 
href="//ssl.gstatic.com/docs/common/drive_favicon1.ico"><title>Page Not 
Found</title><meta name="referrer" content="origin"><link 
href="//fonts.googleapis.com/css?family=Product+Sans" rel="stylesheet" 
type="text/css"><style nonce="L3gm1PN3u9lyxQbOyJWDIQ">
Solution

  • 400 Status code according to RFC2616 is

    10.4.1 400 Bad Request

    The request could not be understood by the server due to malformed syntax.

    It seems one of the query parameters' syntax is not understood or malformed. Just adding var url_ext='format=pdf' helped to isolate the problem. After testing each of the parameters by OP, It seems fzr=false caused the issues, because it was missing & before