Server error when trying to save range of Google sheet cells to PDF in Google Apps Script

Question

I have the following code, running in Google Apps Script. The code attempts to take a range of a spreadsheet and save it as a PDF:

var range = sheet.getRange("A586:K" + lastRow); 

// Get the currently active spreadsheet URL (link)
var ss = SpreadsheetApp.getActiveSpreadsheet();

var pdfBlob = DriveApp.getFileById(ss.getId()).getAs('application/pdf');
pdfBlob.setName('test.pdf');


// Save the PDF to your Google Drive.
var folder = DriveApp.getFolderById("1EJIzvW-oOnFfFOopiAAsudhbHsF5FGKz"); 
var file = folder.createFile(pdfBlob);

When I run the code, it runs for a while, then gives me the error:

We're sorry, a server error occurred. Please wait a bit and try again

Any tips on what might be going on?

score 1 · Answer 1 · edited Feb 18 '23 at 01:55

I believe your goal is as follows.

You want to export the specific range of a sheet as a PDF format using Google Apps Script.

In your script, range is not used. And, the whole sheet is exported. In this case, I think that your goal can be achieved by the query parameter. Ref When this is reflected in your script, how about the following modification?

Modified script:

function myFunction() {
  const sheetName = "Sheet1"; // Please set your sheet name.
  const folderId = "###";  // Please set your folder ID.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const range = sheet.getRange("A586:K" + sheet.getLastRow());
  const startRow = range.getRow() - 1;
  const endRow = startRow + range.getNumRows();
  const startCol = range.getColumn() - 1;
  const endCol = startCol + range.getNumColumns();
  const url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/export?format=pdf&gid=${sheet.getSheetId()}&r1=${startRow}&c1=${startCol}&r2=${endRow}&c2=${endCol}`;
  const res = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } });
  const folder = DriveApp.getFolderById(folderId);
  folder.createFile(res.getBlob().setName("sample.pdf"));
}

Server error when trying to save range of Google sheet cells to PDF in Google Apps Script

1 Answers1

Modified script:

References: