Automating PDF creation from Google Sheets and saving to Drive

Other Questions Google drive
1

Hey everyone, I’m trying to set up a one-click solution to generate PDFs from my Google Sheet and save them to Drive. I’ve added an icon with a script, but I’m running into issues.

When I click the icon, I get this error:

Exception: Request failed for docs.google.com returned code 401. Truncated server response: body{height:100%;margin:0;width:100%}@media (max-height:350px){.button{font-size:10px}.button-...

Here’s a simplified version of my script:

function createPDFfromSheet() {
  const pdfName = 'Output.pdf';
  const sheet = SpreadsheetApp.getActiveSheet();
  const sheetId = sheet.getSheetId();
  const ssId = SpreadsheetApp.getActiveSpreadsheet().getId();

  const pdfUrl = `https://docs.google.com/spreadsheets/d/${ssId}/export?exportFormat=pdf&gid=${sheetId}&access_token=${ScriptApp.getOAuthToken()}`;
  const pdfBlob = UrlFetchApp.fetch(pdfUrl).getBlob().setName(pdfName);
  const pdfFile = DriveApp.createFile(pdfBlob);

  const downloadUrl = `https://drive.google.com/uc?export=download&id=${pdfFile.getId()}`;
  const htmlOutput = HtmlService.createHtmlOutput(`<script>window.open('${downloadUrl}', '_blank');</script>`);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Downloading PDF');
}

Can anyone help me figure out what’s going wrong? I just want to click and have the active cells turn into a PDF in my Drive. Thanks!

2

I’ve encountered similar issues before. The problem might be related to authorization scopes. Make sure you’ve included the necessary scopes in your project settings. Go to ‘Resources’ > ‘Cloud Platform project’ and add ‘https://www.googleapis.com/auth/drive’ and ‘https://www.googleapis.com/auth/spreadsheets’. Also, try using DriveApp.createFile(pdfBlob) instead of the URL fetch method. This approach tends to be more reliable for creating and saving PDFs directly to Drive. If you’re still facing issues, double-check your script’s execution settings and ensure you’re running it from the correct Google account with appropriate permissions.

3

heyy liamj, looks like ur having auth issues. try removing the access_token part from ur pdfUrl. just use:

https://docs.google.com/spreadsheets/d/${ssId}/export?exportFormat=pdf&gid=${sheetId}

that shud work without needing extra auth. lmk if u still have probs!

4

I’ve dealt with this exact problem before, and it can be frustrating. The 401 error suggests an authentication issue. One thing that worked for me was to use the SpreadsheetApp.getActiveSpreadsheet().getUrl() method instead of constructing the URL manually. This approach seems to handle authentication better.

Here’s a modified version of your script that might help:

function createPDFfromSheet() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const pdfName = sheet.getName() + '.pdf';
  const url = SpreadsheetApp.getActiveSpreadsheet().getUrl();
  const exportUrl = url.replace(/edit$/, 'export?format=pdf&gid=' + sheet.getSheetId());
  
  const blob = UrlFetchApp.fetch(exportUrl, {
    headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()}
  }).getBlob().setName(pdfName);
  
  DriveApp.createFile(blob);
  
  SpreadsheetApp.getUi().alert('PDF created and saved to Drive!');
}

This approach has been more reliable in my experience. Give it a shot and see if it resolves your issue.