What's the best way to convert all pages in a Google Sheets tab to PDF format?

Other Questions Google Sheets
1

I’m trying to figure out how to save a whole Google Sheets tab as a PDF file. Right now I have a script that works okay but it only grabs the first page. My sheet has two pages in one tab. I want to get everything just like when you use the regular download option. Here’s a simplified version of what I’m using:

function makePDF(spreadsheetId, activeSheet, fileName, saveFolder) {
  const url = buildExportUrl(spreadsheetId, activeSheet);
  const pdfData = fetchPdfData(url);
  return saveFolder.createFile(pdfData.setName(fileName));
}

function buildExportUrl(id, sheet) {
  // Set up URL with parameters for PDF export
  return `https://docs.google.com/spreadsheets/d/${id}/export?format=pdf&size=A4&fitw=true&gid=${sheet.getSheetId()}`;
}

function fetchPdfData(url) {
  const options = {
    method: 'GET',
    headers: { 'authorization': `Bearer ${ScriptApp.getOAuthToken()}` }
  };
  return UrlFetchApp.fetch(url, options).getBlob();
}

Any ideas on how to tweak this so it grabs all the pages? Thanks for any help!

2

hey, i’ve had luck with this before. try adding &portrait=true&fitw=true&gridlines=false to ur export URL. it usually grabs all pages for me. like this:

url = https://docs.google.com/spreadsheets/d/${id}/export?format=pdf&size=A4&portrait=true&fitw=true&gridlines=false&gid=${sheet.getSheetId()};

hope this helps!

3

I’ve encountered this issue before, and there’s a simpler solution that doesn’t require complex scripting. You can use the SpreadsheetApp.getActiveSpreadsheet().getUrl() method to get the spreadsheet’s URL, then modify it to generate a PDF of all pages.

Here’s a streamlined approach:

function getAllPagesPDF(spreadsheetId, sheetName, fileName, folderId) {
  const ss = SpreadsheetApp.openById(spreadsheetId);
  const sheet = ss.getSheetByName(sheetName);
  const url = ss.getUrl().replace(/edit$/, '') + 'export?format=pdf&gid=' + sheet.getSheetId();
  
  const blob = UrlFetchApp.fetch(url, {
    headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()}
  }).getBlob().setName(fileName);
  
  DriveApp.getFolderById(folderId).createFile(blob);
}

This method has consistently worked for me, capturing all pages without the need for additional parameters. It’s more reliable and efficient than manually constructing export URLs.

4

As someone who’s worked extensively with Google Sheets and Apps Script, I can share a solution that’s worked well for me. Instead of using the export URL method, which can be finicky with multi-page sheets, I’ve found success with the SpreadsheetApp.getActiveSpreadsheet().getAs() method.

Here’s a modified version of your script that should capture all pages:

function makePDF(spreadsheetId, sheetName, fileName, folderId) {
  const ss = SpreadsheetApp.openById(spreadsheetId);
  const sheet = ss.getSheetByName(sheetName);
  ss.setActiveSheet(sheet);
  
  const pdf = ss.getAs('application/pdf');
  pdf.setName(fileName);
  
  DriveApp.getFolderById(folderId).createFile(pdf);
}

This approach lets Google Sheets handle the PDF conversion, ensuring all pages are included. Just make sure to pass the sheet name instead of the sheet object, and use a folder ID instead of a folder object. It’s been reliable in my projects, even with complex multi-page sheets.