Automating PDF creation from Google Sheets and saving to Drive

Other Questions Google drive
1

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 image as an icon and linked it to a script, but I’m running into issues.

When I click the icon, I get an error about a failed request. The code 401 pops up, mentioning something about examining the full response.

Here’s a simplified version of what I’m attempting:

function createPDFFromSheet() {
  const pdfName = 'MyReport.pdf';
  const activeSheet = SpreadsheetApp.getActiveSheet();
  const sheetId = activeSheet.getSheetId();

  // Generate PDF URL
  const pdfUrl = `https://docs.google.com/spreadsheets/${SpreadsheetApp.getActiveSpreadsheet().getId()}/export?format=pdf&gid=${sheetId}&token=${ScriptApp.getOAuthToken()}`;

  // Fetch and save PDF
  const pdfBlob = UrlFetchApp.fetch(pdfUrl).getBlob().setName(pdfName);
  const pdfFile = DriveApp.createFile(pdfBlob);

  // Show download dialog
  const downloadHtml = HtmlService.createHtmlOutput(`<script>window.open('${pdfFile.getDownloadUrl()}', '_blank');</script>`);
  SpreadsheetApp.getUi().showModalDialog(downloadHtml, 'Downloading PDF');
}

Any ideas on why this isn’t working? I’m aiming to streamline the process of creating PDFs from my sheet data.

2

hey ethant, try removin the token from your pdf url. google auth can be tricky. also double-check your project apis. if it still fails, maybe explore client-side pdf libs like html2canvas or jspdf. good luck!

3

I encountered a similar issue when automating PDF creation from Sheets. The 401 error usually indicates an authentication problem. Have you verified that your script has the necessary permissions? Go to Script Editor > Settings > Authorization and check if it’s authorized correctly.

Another potential fix is to use the SpreadsheetApp.getActiveSpreadsheet().getUrl() method instead of manually constructing the URL. This approach often bypasses authentication issues.

If those don’t work, consider using the advanced Drive service. It provides more robust methods for file creation and might resolve your problem. You’ll need to enable it in the Apps Script dashboard first.

Let me know if any of these suggestions help resolve your issue.

4

I’ve dealt with similar PDF automation challenges before. One approach that worked for me was using the Google Sheets API directly instead of constructing URLs. It’s a bit more complex but offers better reliability.

Here’s a snippet that might help:

function createPDFFromSheet() {
  const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  const sheetId = SpreadsheetApp.getActiveSheet().getSheetId();
  
  const request = Sheets.Spreadsheets.get(spreadsheetId, {
    ranges: [SpreadsheetApp.getActiveSheet().getSheetName()],
    fields: 'sheets.properties'
  });
  
  const blob = DriveApp.getFileById(spreadsheetId).getBlob().getAs('application/pdf');
  DriveApp.createFile(blob);
}

This method bypasses the URL construction entirely. Remember to enable the Sheets API in your project settings. If you’re still having issues, check your script’s execution logs for more detailed error messages. They often provide crucial clues for troubleshooting.