Can no longer produce PDF from Google Sheets spreadsheet for some of the users

Question

For almost a year we have been using the below code to export a Google Sheets sheet (held in theSheetName) to a PDF named as specified in InboundID.

This last week, one at a time various users can no longer produce the PDF. I get a failure at the line containing "var newFile = DriveApp.createFile(blob);" with the error being:

"Conversion from text/html to application/pdf failed."

And sure enough, the UrlFetchApp.fetch is returning HTML instead of a PDF. Again, only for some users. Does anyone have any thoughts as to why my users might be seeing this?

function sendPDFToDrive(theSheetName, InboundID) 
{
  var theSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var theSpreadsheetId = theSpreadSheet.getId();
  var thisSheetId = theSpreadSheet.getSheetByName(theSheetName).getSheetId();  
  var url_base = theSpreadSheet.getUrl().replace(/edit$/,'');
  var theOutFileName = "GASFILE_M_" + (Math.floor(Math.random() * 8997) + 1000) + '.pdf'
  //export as pdf
  var url_ext = 'export?exportFormat=pdf&format=pdf'   
      + (thisSheetId ? ('&gid=' + thisSheetId) : ('&id=' + theSpreadsheetId)) 
      // following parameters are optional...
      + '&size=A4'      // paper size
      + '&scale=2' // 1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
      + '&portrait=true'    // orientation, false for landscape
      + '&horizontal_alignment=CENTER'  //LEFT/CENTER/RIGHT
      + '&fitw=true'        // fit to width, false for actual size
      + '&sheetnames=false&printtitle=false&pagenumbers=false'  //hide optional headers and footers
      + '&gridlines=true'  // hide gridlines
      + '&printnotes=false' // don't show notes
      + '&fzr=true';       // repeat row headers (frozen rows) on each page
  // Setup options
  var options = 
  {
    headers: 
    {
      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
    }
  }
  // Build the file
  var response = UrlFetchApp.fetch(url_base + url_ext, options);
  var blob = response.getBlob().setName(theOutFileName);
  var folder = DriveApp.getFolderById("ABC123FooBarID");
  var newFile = DriveApp.createFile(blob); //Create a new file from the blob
  newFile.setName(InboundID + ".pdf"); //Set the file name of the new file
  newFile.makeCopy(folder);
}

score 14 · Accepted Answer · answered Aug 14 '20 at 20:02

I was having this exact problem. After some debugging I saw that my URL was being created incorrectly.

My code was nearly identical to yours. Where I found the culprit was the following line:

var url_base = theSpreadSheet.getUrl().replace(/edit$/,'');

This was not actually clearing out the 'edit' to the end of the line like it had for years. I cannot say why this is, but the proof was in the logs. So, instead I crafted the url by hand:

var   url = 'https://docs.google.com/spreadsheets/d/'+SpreadsheetApp.getActiveSpreadsheet().getId()+'/';

This seemed to solve the problem. This is not a perfect futureproof resolution, because if Google changes how the URLs are crafted, this will fail. But it works for now.

I hope this helps. You can send the url your code is creating to logs and check them to see if you have the same issue I did.

score 6 · Answer 2 · answered Aug 19 '20 at 16:47

6

To expand on Jesse's accepted answer - the culprit is definitely in this line:

var url_base = theSpreadSheet.getUrl().replace(/edit$/,'');

The reason why the replace(/edit$/,'') call no longer clears out edit like before is because the URL returned by theSpreadSheet.getUrl() used to end with edit, but now returns a URL with additional parameters on the end - ouid=1111111111111111111111&urlBuilderDomain=yourdomain.com.

While rebuilding the URL entirely should also work, you can also patch the script with some small changes to the regular expression. Instead of looking for edit$ (meaning edit as the final characters in the string), you can look for edit + any additional characters like so:

var url_base = theSpreadSheet.getUrl().replace(/edit.*$/,'');

answered Aug 19 '20 at 16:47

bcolbert

86
3

3

your regex won't work for 'https://docs.google.com/spreadsheets/d/1Tc2WnuWmQZZYfXeditVUStHyNmUP5K9rkTiJXzTGoA/edit?param1=1&param2=2' – Kos Aug 29 '20 at 08:01
@Kos Why not?`````````````````````````````````` – TheMaster Aug 29 '20 at 12:49
1

@TheMaster because spreadsheet ids are always random letters and numbers, there could be `edit` in the id – Kos Aug 29 '20 at 18:20
@Kos Nice.`````````````````````````````````` – TheMaster Aug 29 '20 at 18:36
Good point, @Kos - in this case the regex could be modified to `.replace(/\/edit.*$/,'');` and the `url_ext` string modified to begin with the initial `/`. This still leaves an edge case where your spreadsheet ID begins with the characters `edit` which I suppose could be avoided by searching for the `?` after `edit` as well. – bcolbert Aug 31 '20 at 21:23
@bcolbert yeah, `edit` in the beginning of id is another edge case, hopefully can be fixed with regex, but there another way: `.split('/edit').slice(0, -1).join('/edit')` – Kos Aug 31 '20 at 22:01

score 1 · Answer 3 · answered Feb 03 '21 at 10:18

1

better also avoid using comma for the last property of the header pointing below:

-- 'Authorization': 'Bearer ' + ScriptApp.getOAuthToken(), --

answered Feb 03 '21 at 10:18

Emre Pişirici

29
2

Can no longer produce PDF from Google Sheets spreadsheet for some of the users

3 Answers3

Linked

Related