CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
murph
murph

Reputation: 115

Save sheets as individual PDFs: "You must include your API token when making API requests."

In Google Sheets, I want to save each sheet from a workbook as a separate PDF. I found the following script on the Google Docs Help forum

function saveSheetsAsPDF() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var url = ss.getUrl().replace(/edit$/,'');
  var parents = DriveApp.getFileById(ss.getId()).getParents();
  if (parents.hasNext()) {var folder = parents.next();}
  else {folder = DriveApp.getRootFolder();}
  var sheets = ss.getSheets();
  for (var i=0; i<sheets.length; i++) {
    var url_ext = 'export?exportFormat=pdf&format=pdf&gid=' + sheets[i].getSheetId()
    +'&size=letter&portrait=true&fitw=true&sheetnames=false'
    +'&printtitle=false&pagenumbers=false&gridlines=false&fzr=false';
    var options = {headers:{'Authorization':'Bearer '+ScriptApp.getOAuthToken()}}
    var response = UrlFetchApp.fetch(url + url_ext, options);
    var blob = response.getBlob().setName(ss.getName()+' - '+sheets[i].getName()+'.pdf');
    folder.createFile(blob);
  }
};

This script works--but only for the first six sheets in the workbook. After saving the sixth sheet as a PDF, it returns this error:

<?xml version="1.0" encoding="UTF-8"?> <intervals personid=""
 status="Unauthorized" code="401"><error><code>9</code><message>User
 bad auth</message><verbose><item>You must include your API token when
 making API requests.</item></verbose></error></intervals>

I'm puzzled as to why it works perfectly for six sheets, then stops.

Ideas?

Upvotes: 1

Views: 160

Answers (2)

murph
murph

Reputation: 115

Solved: Apparently, this is a "too many requests" issue, as suggested by @Will. I added the following delay at the end of the loop:

Utilities.sleep(6000);

Now the script will save all 17 sheets in the workbook as individual PDFs. I don't know how many requests over what period is "too many," but the 6-second delay cured the problem (a 3-second delay was not enough).

Thanks, all, for your input.

Upvotes: 0

Serge Hendrickx
Serge Hendrickx

Reputation: 1416

You could use another strategy and just open the sheet, copy it to a new (temporary) spreadsheet, and export it to pdf. A similair strategy is used here: https://gist.github.com/ixhd/3660885

Warning: this code is outdated, you would need DriveApp instead of DocsList and a few other adjustments, but you could use it to get an idea of the general flow you'd need to follow.

Upvotes: 1

Related Questions