0

As the title says, I have a script that worked perfectly for over 6 months, then just stopped converting the files to PDFs. The last working one I show is Aug 6, 2020, then the next after that was Aug 31, 2020, which came out as a text/HTML file and everyone since has been a text/HTML file.

Here is an example of what I am doing. My actual code creates a new spreadsheet, saves the PDF then deletes the spreadsheet, as when I was creating this, I had read that that was necessary. Not sure if it is still the same, clearly something changed. This example doesn't save the file, but shows the blob type, which is still coming out as text/HTML.

Code.gs

function testPDF(){
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('M2');
  var url = ss.getUrl();
  var sheetId = sheet.getSheetId();

  //remove the trailing 'edit' from the url
  url = url.replace(/edit$/, '');

  //additional parameters for exporting the sheet as a pdf
  var url_ext = 'export?exportFormat=pdf&format=pdf' + //export as pdf
    //below parameters are optional...
    '&portrait=false' + //orientation, false for landscape
    '&scale=4' + //4 = fit to page
    '&gid=' + sheetId; //the sheet's Id
    

  var token = ScriptApp.getOAuthToken();

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

  var theBlob = response.getBlob().setName("M2");
  
  Logger.log(theBlob.getContentType());
}

So what changed in August that made this stop working?

Rubén
  • 34,714
  • 9
  • 70
  • 166
NMALM
  • 378
  • 2
  • 19

1 Answers1

1

I found the answer here: Can no longer produce PDF from Google Sheets spreadsheet for some of the users

Apparently,

//remove the trailing 'edit' from the url
  url = url.replace(/edit$/, '');

was no longer removing everything after the edit, so I followed @jesse-fox and @bcolbert's advice and changed it to the following:

url = url.replace(/edit.*$/,'');

and it works perfectly!

NMALM
  • 378
  • 2
  • 19