Help needed: Downloading PDF from Google Drive using Google Sheets App Script

Other Questions Google drive
1

I’m stuck trying to get a PDF from my Google Drive using a button in Google Sheets. I’ve put a file ID in a cell and want to use it to download the file when I click the button. I found some code online but it’s not working. When I click the button, I get an error saying the server had a problem.

Here’s what I’ve tried:

function grabFile() {
  let sheet = SpreadsheetApp.getActive().getSheetByName('Downloads');
  let fileId = sheet.getRange('B2').getValue();

  let html = '<html><body onload="document.getElementById(\'dl\').click()">';
  html += '<a id="dl" href="https://drive.google.com/uc?export=download&id=' + fileId + '">Downloading...</a>';
  html += '</body></html>';

  return HtmlService.createHtmlOutput(html);
}

function triggerDownload() {
  return HtmlService.createHtmlOutput(grabFile());
}

Can anyone help me figure out what’s wrong? Thanks!

2

hey mate, i’ve had this issue before. the problem is google’s security stuff. try using the DriveApp service instead. here’s a quick fix:

function getFile() {
  var ss = SpreadsheetApp.getActive();
  var fileId = ss.getSheetByName('Downloads').getRange('B2').getValue();
  return DriveApp.getFileById(fileId).getBlob();
}

function startDownload() {
  var ui = SpreadsheetApp.getUi();
  ui.showModalDialog(HtmlService.createHtmlOutput(
    '<script>google.script.run.withSuccessHandler(function(blob) {'+
    'google.script.host.close();'+
    'window.open(blob.getUrl());'+
    '}).getFile();</script>'
  ), 'Downloading...');
}

this should work. lmk if u need more help!

3

As someone who’s worked extensively with Google Apps Script, I can see a couple of issues with your current approach. First, the grabFile() function is creating HTML, but it’s not actually downloading anything server-side. It’s just generating a link that the browser is expected to click.

For security reasons, Google Sheets can’t directly trigger downloads like this. Instead, you’ll need to use the DriveApp service to get the file content and then serve it as a blob.

Here’s a modified version that should work:

function downloadPDF() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Downloads');
  var fileId = sheet.getRange('B2').getValue();
  var file = DriveApp.getFileById(fileId);
  var content = file.getBlob();
  
  return content.setName(file.getName());
}

function triggerDownload() {
  var html = '<script>google.script.run.withSuccessHandler(function(blob) {'+
             'google.script.host.close();'+
             'window.open(blob.getUrl(), "_blank");'+
             '}).downloadPDF();</script>';
  var output = HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showModalDialog(output, 'Downloading...');
}

This should download the PDF when you click the button. Let me know if you run into any issues!

4

I’ve encountered similar issues when working with Google Apps Script. The problem lies in how you’re trying to initiate the download. Google’s security measures prevent direct downloads from scripts.

Instead, try using the DriveApp service to fetch the file and convert it to a blob. Then, serve this blob directly to the user. Here’s a modified version of your code that should work:

function downloadFile() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Downloads');
  var fileId = sheet.getRange('B2').getValue();
  var file = DriveApp.getFileById(fileId);
  var content = file.getBlob();
  
  return content;
}

function triggerDownload() {
  var html = '<script>google.script.run.withSuccessHandler(function(content) {'+
             'var link = document.createElement(\"a\");'+
             'link.href = content.getDataAsString();'+
             'link.download = \"' + file.getName() + '\";'+
             'link.click();'+
             '}).downloadFile();</script>';
  
  SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutput(html), 'Downloading...');
}

This approach should bypass the security restrictions and initiate the download. Give it a try and let us know if it resolves your issue.