CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
JSDBroughton
JSDBroughton

Reputation: 4034

UrlFetch from Google Sheet exportLink['application/pdf'] not returning PDF data

I create and send a periodic email as an update from a Google Sheet. For various client reasons this is done 3 ways, as a link to the Sheet, and as attachments (PDF and XLSX).

This was working 'til recently. The XSLX attachment still works, but the PDF is no longer sent as a response to a UrlFetch to the file.exportLinks('application/pdf') url. No matter what the request headers it always returns as Content-Type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

Did something else undocumented change that I am missing here?

function exportAsPDF(spreadsheetId) {
  spreadsheetId = spreadsheetId || 'SECRET_ID';

  var file = Drive.Files.get(spreadsheetId),
      url = file.exportLinks['application/pdf'];

  url += '&format=pdf&size=7&fzr=true&portrait=true&fitw=true&gid=0&gridlines=false&printtitle=false&sheetnames=false&pagenum=UNDEFINED&attachment=true'

  var token = ScriptApp.getOAuthToken(),
      response = UrlFetchApp.fetch(url, {
          headers: {
              'Authorization': 'Bearer ' +  token
          }
      });

  var headers = response.getAllHeaders(); // revealing content-type returned isn't pdf
  var pdfBlob = response.getBlob().getAs('application/pdf');      
  var pdfString = pdfBlob.getDataAsString(); // this naturally throws an error

  return response.getBlob(); // this returns to the send mail script
}

Upvotes: 3

Views: 849

Answers (1)

Mogsdad
Mogsdad

Reputation: 45720

I'm able to get PDFs using the utility from Convert all sheets to PDF with Google Apps Script.

That working script modifies the spreadsheet's edit URL into an export URL, which looks like:

https://docs.google.com/spreadsheets/d/<%SS-ID%>/export?exportFormat=pdf...

The advanced Drive service gives an export URL formatted like:

https://docs.google.com/spreadsheets/export?id=<%SS-ID%>&exportFormat=pdf...

I'd expect the URL provided by exportLinks to be more reliable than the hack in the working script. Apparently, it's not.

This has been raised as Issue 5114. Star it to receive updates.

Upvotes: 2

Related Questions