Skip to content
Skip to chapter

Chapter 21

Workspace services

12 min read

Export PDFs from Sheets and Docs

Export a Google Doc with getAs(MimeType.PDF) or export a single Google Sheet tab and range with layout parameters, flush(), and Drive checkpoints.

By the end

Choose between DriveApp.File.getAs(MimeType.PDF) and a spreadsheet export URL, flush pending Docs or Sheets edits before rendering, validate HTTP status and PDF header bytes, and save one single-tab PDF to a private Drive folder without creating duplicates on rerun.

Chapter navigation

Export a finished Google Doc or Google Sheet into a dated, read-only PDF snapshot stored in a private Drive folder, without rendering stale edits or creating duplicate files on repeat runs. Before starting, you need HTTP response checks from Fetch data from web APIs, folder and file ID rules from DriveApp files, folders, and scopes, and Script Properties checkpoints from Generate a Docs report from a template.

Apps Script offers two ways to produce a PDF, and picking the wrong one is a common source of surprises. Calling DriveApp.getFileById(id).getAs(MimeType.PDF) converts a whole Google Doc, Slides presentation, or Spreadsheet in one built-in call, using default export settings and including every visible tab of a workbook. Exporting a single spreadsheet tab, hiding gridlines, switching between portrait and landscape, or clipping a rectangular cell window requires fetching the spreadsheet's /export URL with UrlFetchApp.fetch and an OAuth bearer token.

Choose between File.getAs and the spreadsheet export URL

Compare what each mechanism controls and what permissions it requires before writing code:

CapabilityDriveApp.File.getAs(MimeType.PDF)Spreadsheet /export?format=pdf via UrlFetchApp
Source file typesGoogle Docs, Google Slides, Google Sheets (all visible tabs), DrawingsGoogle Sheets
Single tab selectionNo. Includes every visible tab in the workbookYes, via gid= (sheet.getSheetId())
Rectangular cell windowNoYes, via r1, c1, r2, c2
Orientation, paper size, gridlines, frozen headersUses the file's saved page setupControlled per request (portrait, size, fitw, gridlines, fzr)
Required OAuth scopesdrive.readonly (to convert to an in-memory Blob) or drive (to also save in Drive)script.external_request, spreadsheets.readonly or spreadsheets, plus drive when saving to a Drive folder
Contract stabilityOfficial documented File.getAs APIWeb export URL parameters rather than a versioned REST API, so responses require strict status, MIME, and %PDF- byte checks

Export a Google Doc with File.getAs(MimeType.PDF)

When your source is a Google Doc (such as the site report built in Generate a Docs report from a template), DocumentApp already owns page margins, headings, and page breaks inside the document itself. Converting that document to a PDF requires no UrlFetchApp call and no query parameters:

Source
/**
 * Flushes pending DocumentApp edits if open, converts the Google Doc to a PDF
 * blob, and writes it into a private Drive folder.
 *
 * @param {string} documentId
 * @param {string} folderId
 * @param {string} pdfFileName
 * @returns {DriveApp.File}
 */
function saveGoogleDocAsPdf(documentId, folderId, pdfFileName) {
  const docFile = DriveApp.getFileById(documentId);
  const folder = DriveApp.getFolderById(folderId);
  const pdfBlob = docFile.getAs(MimeType.PDF).setName(pdfFileName);
  return folder.createFile(pdfBlob);
}

MimeType.PDF evaluates to the string 'application/pdf'. Calling docFile.getAs(MimeType.PDF) returns an in-memory Blob without altering the source Google Doc, and folder.createFile(pdfBlob) writes that Blob into the chosen Drive folder as a new PDF file.

File.getAs(MimeType.PDF) works the same way on Google Slides presentations (producing one PDF page per non-skipped slide, after calling presentation.saveAndClose() if the script edited slides in the same run) and on multi-tab Google Sheets when you genuinely want a whole-workbook packet. On a Spreadsheet, File.getAs(MimeType.PDF) renders every visible tab in tab-bar order and omits hidden tabs (sheet.hideSheet()), though hiding and unhiding tabs on a shared spreadsheet to export one tab mutates the live view for collaborators and breaks concurrent viewers. Use the /export URL instead whenever you want one tab or cell window without touching sheet visibility.

If the same script execution edited the Google Doc through DocumentApp right before calling getAs(MimeType.PDF), you must close and flush the document first:

Source
const doc = DocumentApp.openById(documentId);
doc.getTabs()[0].asDocumentTab().getBody().appendParagraph('Approved for release.');
doc.saveAndClose(); // Required before DriveApp exports the file!

const pdfBlob = DriveApp.getFileById(documentId).getAs(MimeType.PDF);

warning

Both SpreadsheetApp and DocumentApp buffer writes in memory so consecutive method calls do not pay a network round trip on every line. Drive's PDF converter and the Google Sheets /export server read the committed file state stored on Google's servers, not buffered edits still sitting in your running script.

  • Before exporting a spreadsheet tab (whether through File.getAs(MimeType.PDF) or UrlFetchApp.fetch), call SpreadsheetApp.flush() so pending setValues(), formula recalculations, number formats, and filter updates finish writing.

  • Before exporting a Google Doc edited in the same run, call doc.saveAndClose(). Do not edit the doc handle again after saveAndClose() without reopening it via DocumentApp.openById(documentId).

Omitting flush() or saveAndClose() produces a valid PDF that silently shows the previous run's numbers or text placeholders.

Set up the private practice spreadsheet and folder

The chapter project focuses on the single-tab, range-clipped spreadsheet export workflow, where parameter encoding, bearer-token scoping, response validation, and Script Properties checkpoints all come together.

  1. In My Drive, create an empty private folder named PDF export practice outputs, open it, and copy the folder ID after /folders/ in its browser URL.

  2. Outside that folder, create a new Google Sheet named Harbor garden materials and rename its first tab Summary.

  3. Paste sample.csv below into Summary!A1, select column A, choose Data > Split text to columns, and pick Comma as the separator. Confirm that Summary!A1:E7 holds seven rows by five columns (Subtotal totals 700.50 in E7).

  4. Type Internal draft note: verify bolt supplier into cell G2 and add a second tab named Scratch with Do not export this tab in Scratch!A1 so you can verify range and tab clipping.

  5. Copy the spreadsheet ID between /spreadsheets/d/ and /edit in the spreadsheet URL.

  6. At script.google.com, create a new standalone project named Sheet PDF export practice and replace Code.gs with the source below.

  7. Open Project Settings, check Show "appsscript.json" manifest file in editor, replace appsscript.json with the included manifest, and save both files.

  8. In Code.gs, paste your spreadsheet ID into PDF_EXPORT.spreadsheetId and your folder ID into PDF_EXPORT.destinationFolderId. Keep approveExport: false for the dry-run preview.

sample.csv
Line Item,Category,Quantity,Unit Cost,Subtotal
Harbor bench slats,Lumber,12,18.50,222.00
Galvanized anchor bolts,Hardware,24,2.75,66.00
Drip emitter tubing,Irrigation,3,34.00,102.00
Cedar planter trim,Lumber,8,21.00,168.00
Pathway gravel bags,Masonry,15,9.50,142.50
Total,,,,700.50

Download sample.csv
Code.gs
const PDF_EXPORT = {
  spreadsheetId: 'REPLACE_WITH_SPREADSHEET_ID',
  destinationFolderId: 'REPLACE_WITH_DESTINATION_FOLDER_ID',
  sheetName: 'Summary',
  outputFileName: 'Harbor garden - materials summary.pdf',
  landscape: false,
  // Clip the PDF to A1:E7 (rows 1..7, columns 1..5 -> r1=0, c1=0, r2=7, c2=5).
  useRangeWindow: true,
  startRow: 1,
  startColumn: 1,
  numRows: 7,
  numColumns: 5,
  // Set approveExport to true only after inspecting previewSheetPdfExport.
  approveExport: false
};

/**
 * @typedef {{
 *   status: 'pending'|'complete',
 *   spreadsheetId: string,
 *   sheetId: number,
 *   sheetName: string,
 *   destinationFolderId: string,
 *   outputFileName: string,
 *   exportUrl: string,
 *   attemptedAt: string,
 *   outputFileId: string,
 *   byteLength: number
 * }} SheetPdfExportRecord
 */

const PDF_EXPORT_RECORD_KEY = 'SHEET_PDF_EXPORT';
const EXPECTED_SUMMARY_HEADERS = ['Line Item', 'Category', 'Quantity', 'Unit Cost', 'Subtotal'];
// ASCII bytes for "%PDF-" at the start of every valid PDF document.
const PDF_MAGIC_BYTES = [37, 80, 68, 70, 45];

/**
 * Dry-run preview. Validates the source spreadsheet, Summary tab, destination
 * Drive folder, and export query parameters without fetching or saving a PDF.
 *
 * @returns {void}
 */
function previewSheetPdfExport() {
  validatePdfExportSettings_();
  const source = inspectSourceSheet_();
  const destination = getUntrashedDestinationFolder_();
  const plan = buildSheetPdfExportPlan_(source.spreadsheet.getId(), source.sheet.getSheetId());

  Logger.log(
    'Source spreadsheet: "' +
      source.spreadsheet.getName() +
      '" (ID: ' +
      source.spreadsheet.getId() +
      '); tab: "' +
      source.sheet.getName() +
      '" (gid=' +
      source.sheet.getSheetId() +
      ').'
  );
  Logger.log(
    'Destination folder: "' + destination.getName() + '" (ID: ' + destination.getId() + ').'
  );
  Logger.log('Planned output file: ' + PDF_EXPORT.outputFileName);
  Logger.log('Window description: ' + plan.windowDescription);
  Logger.log('Export URL: ' + plan.exportUrl);
  showSheetPdfExportRecord();
  Logger.log('Preview complete. No HTTP export request or Drive file write occurred.');
}

/**
 * Exports the configured Sheet tab (and optional cell window) to a PDF in the
 * destination Drive folder once, recording the saved file ID in Script Properties.
 *
 * @returns {void}
 */
function exportApprovedSheetTabPdf() {
  validatePdfExportSettings_();
  if (PDF_EXPORT.approveExport !== true) {
    throw new Error(
      'Run previewSheetPdfExport first, then set PDF_EXPORT.approveExport to true when ready.'
    );
  }

  const lock = LockService.getScriptLock();
  lock.waitLock(5000);
  try {
    const previous = readPdfExportRecord_();
    if (previous !== null) {
      requireMatchingExportSettings_(previous);
      if (previous.status !== 'complete') {
        throw new Error(
          'An earlier PDF export attempt is pending. Inspect the destination folder and showSheetPdfExportRecord before retrying.'
        );
      }
      const existingFile = DriveApp.getFileById(previous.outputFileId);
      if (existingFile.isTrashed()) {
        throw new Error(
          'Recorded PDF file ' +
            previous.outputFileId +
            ' is in Trash. Restore it or run clearSheetPdfExportRecord after setting approveExport to false.'
        );
      }
      Logger.log(
        'Already exported. Reusing recorded Drive file ID: ' +
          existingFile.getId() +
          ' (' +
          previous.byteLength +
          ' bytes).'
      );
      Logger.log('Saved PDF URL: ' + existingFile.getUrl());
      return;
    }

    const source = inspectSourceSheet_();
    const destination = getUntrashedDestinationFolder_();
    const existingByName = destination.getFilesByName(PDF_EXPORT.outputFileName);
    if (existingByName.hasNext()) {
      throw new Error(
        'Destination folder already contains "' +
          PDF_EXPORT.outputFileName +
          '". Inspect it before exporting.'
      );
    }

    // Commit any pending spreadsheet edits so the PDF renders current cell values.
    SpreadsheetApp.flush();

    const plan = buildSheetPdfExportPlan_(source.spreadsheet.getId(), source.sheet.getSheetId());
    /** @type {SheetPdfExportRecord} */
    const record = {
      status: 'pending',
      spreadsheetId: source.spreadsheet.getId(),
      sheetId: source.sheet.getSheetId(),
      sheetName: source.sheet.getName(),
      destinationFolderId: destination.getId(),
      outputFileName: PDF_EXPORT.outputFileName,
      exportUrl: plan.exportUrl,
      attemptedAt: new Date().toISOString(),
      outputFileId: '',
      byteLength: 0
    };
    savePdfExportRecord_(record);

    const response = UrlFetchApp.fetch(plan.exportUrl, {
      headers: {
        Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
      },
      muteHttpExceptions: true
    });

    const pdfBlob = validatePdfResponse_(response);
    pdfBlob.setName(PDF_EXPORT.outputFileName);
    const createdFile = destination.createFile(pdfBlob);

    record.status = 'complete';
    record.outputFileId = createdFile.getId();
    record.byteLength = pdfBlob.getBytes().length;
    savePdfExportRecord_(record);

    Logger.log(
      'Saved PDF "' +
        createdFile.getName() +
        '" (ID: ' +
        record.outputFileId +
        ', ' +
        record.byteLength +
        ' bytes).'
    );
    Logger.log('Saved PDF URL: ' + createdFile.getUrl());
  } finally {
    lock.releaseLock();
  }
}

/**
 * Logs the current SHEET_PDF_EXPORT checkpoint stored in Script Properties.
 *
 * @returns {void}
 */
function showSheetPdfExportRecord() {
  const raw = PropertiesService.getScriptProperties().getProperty(PDF_EXPORT_RECORD_KEY);
  Logger.log(PDF_EXPORT_RECORD_KEY + ': ' + (raw === null ? 'No recorded PDF export.' : raw));
}

/**
 * Deletes the saved SHEET_PDF_EXPORT checkpoint during practice cleanup after
 * approveExport has been reset to false. Does not delete Drive files.
 *
 * @returns {void}
 */
function clearSheetPdfExportRecord() {
  if (PDF_EXPORT.approveExport !== false) {
    throw new Error('Set PDF_EXPORT.approveExport back to false before clearing the export record.');
  }
  const lock = LockService.getScriptLock();
  lock.waitLock(5000);
  try {
    PropertiesService.getScriptProperties().deleteProperty(PDF_EXPORT_RECORD_KEY);
    Logger.log('Cleared ' + PDF_EXPORT_RECORD_KEY + ' from Script Properties.');
  } finally {
    lock.releaseLock();
  }
}

/**
 * Builds the docs.google.com /export URL strictly from a validated spreadsheet ID
 * and explicit encoded query parameters.
 *
 * @param {string} spreadsheetId
 * @param {number} sheetId
 * @returns {{ exportUrl: string, windowDescription: string }}
 */
function buildSheetPdfExportPlan_(spreadsheetId, sheetId) {
  requireDriveItemId_(spreadsheetId, 'spreadsheetId');

  /** @type {Array<[string, string]>} */
  const entries = [
    ['format', 'pdf'],
    ['gid', String(sheetId)],
    ['size', 'letter'],
    ['portrait', String(!PDF_EXPORT.landscape)],
    ['fitw', 'true'],
    ['gridlines', 'false'],
    ['sheetnames', 'false'],
    ['pagenumbers', 'false'],
    ['fzr', 'true']
  ];

  let windowDescription = 'Full tab (all populated cells on "' + PDF_EXPORT.sheetName + '")';
  if (PDF_EXPORT.useRangeWindow) {
    // Export window parameters use 0-indexed start coordinates and exclusive end coordinates.
    const r1 = PDF_EXPORT.startRow - 1;
    const c1 = PDF_EXPORT.startColumn - 1;
    const r2 = r1 + PDF_EXPORT.numRows;
    const c2 = c1 + PDF_EXPORT.numColumns;
    entries.push(['r1', String(r1)]);
    entries.push(['c1', String(c1)]);
    entries.push(['r2', String(r2)]);
    entries.push(['c2', String(c2)]);
    windowDescription =
      'Clipped cell window rows ' +
      PDF_EXPORT.startRow +
      '..' +
      (PDF_EXPORT.startRow + PDF_EXPORT.numRows - 1) +
      ', columns ' +
      PDF_EXPORT.startColumn +
      '..' +
      (PDF_EXPORT.startColumn + PDF_EXPORT.numColumns - 1) +
      ' (r1=' +
      r1 +
      ', c1=' +
      c1 +
      ', r2=' +
      r2 +
      ', c2=' +
      c2 +
      ')';
  }

  const queryString = entries
    .map(function (pair) {
      return encodeURIComponent(pair[0]) + '=' + encodeURIComponent(pair[1]);
    })
    .join('&');

  const exportUrl =
    'https://docs.google.com/spreadsheets/d/' +
    encodeURIComponent(spreadsheetId) +
    '/export?' +
    queryString;

  return {
    exportUrl: exportUrl,
    windowDescription: windowDescription
  };
}

/**
 * Verifies HTTP 200, application/pdf content type, and %PDF- magic header bytes.
 *
 * @param {UrlFetchApp.HTTPResponse} response
 * @returns {Blob}
 */
function validatePdfResponse_(response) {
  const statusCode = response.getResponseCode();
  if (statusCode !== 200) {
    const preview = response.getContentText().slice(0, 160).replace(/\s+/g, ' ');
    throw new Error('Spreadsheet PDF export failed with HTTP ' + statusCode + ': ' + preview);
  }

  const blob = response.getBlob();
  const contentType = blob.getContentType() || '';
  if (contentType !== MimeType.PDF) {
    throw new Error(
      'Expected content type ' + MimeType.PDF + ', received "' + contentType + '".'
    );
  }

  const bytes = blob.getBytes();
  if (bytes.length < PDF_MAGIC_BYTES.length) {
    throw new Error('Export response is shorter than a valid PDF header.');
  }
  for (let index = 0; index < PDF_MAGIC_BYTES.length; index += 1) {
    if (bytes[index] !== PDF_MAGIC_BYTES[index]) {
      throw new Error('Export response does not start with %PDF- header bytes.');
    }
  }
  return blob;
}

/**
 * @returns {{ spreadsheet: SpreadsheetApp.Spreadsheet, sheet: SpreadsheetApp.Sheet }}
 */
function inspectSourceSheet_() {
  const spreadsheet = SpreadsheetApp.openById(PDF_EXPORT.spreadsheetId);
  const sheet = spreadsheet.getSheetByName(PDF_EXPORT.sheetName);
  if (!sheet) {
    throw new Error(
      'Tab "' +
        PDF_EXPORT.sheetName +
        '" was not found in spreadsheet ' +
        PDF_EXPORT.spreadsheetId +
        '.'
    );
  }
  const minRows = PDF_EXPORT.useRangeWindow
    ? PDF_EXPORT.startRow + PDF_EXPORT.numRows - 1
    : 2;
  const minCols = PDF_EXPORT.useRangeWindow
    ? PDF_EXPORT.startColumn + PDF_EXPORT.numColumns - 1
    : EXPECTED_SUMMARY_HEADERS.length;
  if (sheet.getLastRow() < minRows || sheet.getLastColumn() < minCols) {
    throw new Error(
      'Tab "' +
        PDF_EXPORT.sheetName +
        '" needs at least ' +
        minRows +
        ' rows and ' +
        minCols +
        ' columns. Import sample.csv into Summary!A1 first.'
    );
  }
  const header = sheet.getRange(1, 1, 1, EXPECTED_SUMMARY_HEADERS.length).getValues()[0];
  const headerMatches = EXPECTED_SUMMARY_HEADERS.every(function (expected, index) {
    return String(header[index]).trim() === expected;
  });
  if (!headerMatches) {
    throw new Error(
      'Summary!A1:E1 must match headers: ' + EXPECTED_SUMMARY_HEADERS.join(', ') + '.'
    );
  }
  return { spreadsheet: spreadsheet, sheet: sheet };
}

/**
 * @returns {DriveApp.Folder}
 */
function getUntrashedDestinationFolder_() {
  const folder = DriveApp.getFolderById(PDF_EXPORT.destinationFolderId);
  if (folder.isTrashed()) {
    throw new Error('Destination Drive folder is in Trash. Restore it or choose an active folder.');
  }
  return folder;
}

/**
 * @returns {void}
 */
function validatePdfExportSettings_() {
  requireDriveItemId_(PDF_EXPORT.spreadsheetId, 'spreadsheetId');
  requireDriveItemId_(PDF_EXPORT.destinationFolderId, 'destinationFolderId');
  if (PDF_EXPORT.spreadsheetId === PDF_EXPORT.destinationFolderId) {
    throw new Error('spreadsheetId and destinationFolderId must refer to different Drive items.');
  }
  if (typeof PDF_EXPORT.sheetName !== 'string' || PDF_EXPORT.sheetName.trim() === '') {
    throw new Error('sheetName must be a non-empty string.');
  }
  if (
    typeof PDF_EXPORT.outputFileName !== 'string' ||
    !PDF_EXPORT.outputFileName.endsWith('.pdf') ||
    PDF_EXPORT.outputFileName.length < 5 ||
    PDF_EXPORT.outputFileName.length > 100
  ) {
    throw new Error('outputFileName must be a .pdf file name of at most 100 characters.');
  }
  if (PDF_EXPORT.useRangeWindow) {
    const coords = [
      PDF_EXPORT.startRow,
      PDF_EXPORT.startColumn,
      PDF_EXPORT.numRows,
      PDF_EXPORT.numColumns
    ];
    const validCoords = coords.every(function (num) {
      return Number.isInteger(num) && num >= 1 && num <= 200;
    });
    if (!validCoords) {
      throw new Error('Range window coordinates must be integers between 1 and 200.');
    }
  }
}

/**
 * Rejects placeholders, full URLs, query characters, or path segments so bearer
 * tokens are never sent outside https://docs.google.com/spreadsheets/d/<ID>/export.
 *
 * @param {string} value
 * @param {string} label
 * @returns {void}
 */
function requireDriveItemId_(value, label) {
  if (
    typeof value !== 'string' ||
    value.startsWith('REPLACE_') ||
    value.length < 10 ||
    value.length > 200 ||
    !/^[A-Za-z0-9_-]+$/.test(value)
  ) {
    throw new Error(
      'Set PDF_EXPORT.' + label + ' to a Drive item ID (letters, digits, _ or -), never a full URL.'
    );
  }
}

/**
 * @param {SheetPdfExportRecord} record
 * @returns {void}
 */
function savePdfExportRecord_(record) {
  PropertiesService.getScriptProperties().setProperty(PDF_EXPORT_RECORD_KEY, JSON.stringify(record));
}

/**
 * @returns {SheetPdfExportRecord|null}
 */
function readPdfExportRecord_() {
  const raw = PropertiesService.getScriptProperties().getProperty(PDF_EXPORT_RECORD_KEY);
  if (raw === null) {
    return null;
  }
  /** @type {SheetPdfExportRecord} */
  let record;
  try {
    record = JSON.parse(raw);
  } catch (error) {
    throw new Error('Saved SHEET_PDF_EXPORT property is not valid JSON. Inspect before clearing.');
  }
  if (
    !record ||
    typeof record !== 'object' ||
    (record.status !== 'pending' && record.status !== 'complete') ||
    typeof record.spreadsheetId !== 'string' ||
    typeof record.destinationFolderId !== 'string' ||
    typeof record.outputFileName !== 'string' ||
    typeof record.outputFileId !== 'string'
  ) {
    throw new Error('Saved SHEET_PDF_EXPORT property has an unexpected shape. Inspect before clearing.');
  }
  return record;
}

/**
 * @param {SheetPdfExportRecord} record
 * @returns {void}
 */
function requireMatchingExportSettings_(record) {
  if (
    record.spreadsheetId !== PDF_EXPORT.spreadsheetId ||
    record.destinationFolderId !== PDF_EXPORT.destinationFolderId ||
    record.sheetName !== PDF_EXPORT.sheetName ||
    record.outputFileName !== PDF_EXPORT.outputFileName
  ) {
    throw new Error(
      'PDF_EXPORT settings differ from the recorded export in SHEET_PDF_EXPORT. Restore settings or clear the record intentionally.'
    );
  }
}

Download Code.gs
appsscript.json
{
  "timeZone": "Etc/UTC",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive",
    "https://www.googleapis.com/auth/script.external_request"
  ]
}

Download appsscript.json

Preview the export URL and cell window before writing

With approveExport still set to false, select previewSheetPdfExport in the toolbar and click Run, reviewing and approving the three requested scopes (spreadsheets, drive, and script.external_request).

Check Execution log to confirm five details before any PDF is fetched or written:

Source
Source spreadsheet: "Harbor garden materials" (ID: ...); tab: "Summary" (gid=0).
Destination folder: "PDF export practice outputs" (ID: ...).
Planned output file: Harbor garden - materials summary.pdf
Window description: Clipped cell window rows 1..7, columns 1..5 (r1=0, c1=0, r2=7, c2=5)
Export URL: https://docs.google.com/spreadsheets/d/.../export?format=pdf&gid=0&size=letter&portrait=true&fitw=true&gridlines=false&sheetnames=false&pagenumbers=false&fzr=true&r1=0&c1=0&r2=7&c2=5
SHEET_PDF_EXPORT: No recorded PDF export.
Preview complete. No HTTP export request or Drive file write occurred.

Confirm in Drive that PDF export practice outputs is still empty. If you accidentally run exportApprovedSheetTabPdf while approveExport is false, the script stops immediately with Run previewSheetPdfExport first, then set PDF_EXPORT.approveExport to true when ready.

Understand spreadsheet export parameters and 0-indexed windows

DriveApp.File.getAs and ScriptApp.getOAuthToken are documented Apps Script methods, whereas the query parameters accepted by https://docs.google.com/spreadsheets/d/<ID>/export mirror Google Sheets' browser File > Download > PDF dialog rather than a versioned REST specification. Treat those parameters as web export options whose output you verify on every run:

ParameterValues used in practiceEffect on the rendered PDF
formatpdfRequests PDF output instead of xlsx, csv, or tsv.
gidStringified integer from sheet.getSheetId()Exports only that tab (0 is typically the initial tab in a new sheet, while added tabs receive larger integer IDs). Omitting gid exports all visible tabs.
sizeletter, a4, legalSets the page dimensions.
portraittrue (portrait) or false (landscape)Controls page orientation (String(!PDF_EXPORT.landscape) in Code.gs).
fitwtrue or falseWhen true, scales columns to fit one page wide instead of splitting wide tables across horizontal page breaks.
gridlinesfalse or trueHides default cell gridlines when false while keeping explicit cell borders.
sheetnames and pagenumbersfalse or trueControls whether Google Sheets prints the workbook and tab title header and page numbers.
fzrtrue or falseRepeats frozen header rows (sheet.setFrozenRows(1)) at the top of each page when a table spans multiple pages (fzc does the same for frozen left columns).
top_margin, bottom_margin, left_margin, right_marginDecimal inches (such as 0.50 or 0.75)Sets page margins in inches when all four margin parameters are supplied together.
horizontal_alignment, vertical_alignmentLEFT, CENTER, RIGHT and TOP, MIDDLE, BOTTOMPositions a narrow table on the page when fitw does not fill the full printable width or height.
r1, c1, r2, c2Non-negative integers (requires gid)Clips output to a rectangular cell window using 0-indexed inclusive start (r1, c1) and 0-indexed exclusive end (r2, c2).

Convert 1-based Range coordinates to r1, c1, r2, c2

Pay close attention to the coordinate conversion in buildSheetPdfExportPlan_:

Source
const r1 = PDF_EXPORT.startRow - 1;
const c1 = PDF_EXPORT.startColumn - 1;
const r2 = r1 + PDF_EXPORT.numRows;
const c2 = c1 + PDF_EXPORT.numColumns;

In SpreadsheetApp, rows and columns start at 1: Summary!A1:E7 starts at startRow = 1, startColumn = 1, and spans numRows = 7, numColumns = 5. In the /export query string, row and column indexes start at 0, and r2 and c2 mark the first row and column excluded from the export (matching Array.prototype.slice(start, end)):

  • r1 = 0 and r2 = 7 export 0-based rows 0, 1, 2, 3, 4, 5, 6 (sheet rows 1 through 7).

  • c1 = 0 and c2 = 5 export 0-based columns 0, 1, 2, 3, 4 (columns A through E), excluding G2 (c = 6).

  • For a range starting at B3:F12 (startRow = 3, startColumn = 2, numRows = 10, numColumns = 5), the formulas produce r1 = 2, c1 = 1, r2 = 12, c2 = 6.

Every parameter key and value is passed through encodeURIComponent(...) before joining with '&' so no character in a parameter can inject an extra query separator.

Scope bearer tokens and lock down URL construction

To fetch a private spreadsheet's /export URL, UrlFetchApp.fetch sends an HTTP Authorization header carrying ScriptApp.getOAuthToken():

Source
const response = UrlFetchApp.fetch(plan.exportUrl, {
  headers: {
    Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
  },
  muteHttpExceptions: true
});

warning

ScriptApp.getOAuthToken() returns a live OAuth 2.0 access token carrying every scope authorized for the current script project (spreadsheets, drive, and any Gmail, Docs, or Calendar scopes your project also requests). Two security and authorization rules follow directly from how that token works:

  • Never build the /export URL from an unvalidated URL string or cell value. If a user or sheet cell supplies 'https://attacker.example/collect?' and the script passes that URL to UrlFetchApp.fetch with Authorization: 'Bearer ' + ScriptApp.getOAuthToken(), the script hands the executing user's Google access token to attacker.example. requireDriveItemId_ enforces /^[A-Za-z0-9_-]+$/ on spreadsheetId and interpolates encodeURIComponent(spreadsheetId) into the fixed prefix 'https://docs.google.com/spreadsheets/d/'. For deployed scripts that fetch only spreadsheet PDFs, you can also declare "urlFetchWhitelist": ["https://docs.google.com/spreadsheets/d/"] in appsscript.json so the Apps Script runtime blocks UrlFetchApp requests to any other origin.

  • spreadsheets.currentonly is rejected by /export. The container-only scope https://www.googleapis.com/auth/spreadsheets.currentonly works inside SpreadsheetApp methods on a bound sheet, whereas docs.google.com/spreadsheets/d/<ID>/export is an external Google HTTP endpoint that returns 401 or 403 for a currentonly token. If a script only fetches /export using a known spreadsheetId and gid without calling SpreadsheetApp write methods or openById, https://www.googleapis.com/auth/spreadsheets.readonly is the narrowest scope /export accepts (see authorization scopes). When the script also calls SpreadsheetApp.openById(...) or writes cells before calling SpreadsheetApp.flush(), list https://www.googleapis.com/auth/spreadsheets as in appsscript.json above.

Validate PDF responses and checkpoint the saved Drive file

Passing muteHttpExceptions: true to UrlFetchApp.fetch prevents Apps Script from throwing an opaque exception on 4xx or 5xx responses, allowing validatePdfResponse_ to inspect HTTPResponse step by step:

  1. HTTP status 200: response.getResponseCode() !== 200 throws an error with the status code and body prefix (401 or 403 for missing access, or 429 for export rate limits).

  2. MimeType.PDF content type: blob.getContentType() !== MimeType.PDF stops the run if a misconfigured ID or proxy returns an HTML sign-in page (text/html) with HTTP 200.

  3. %PDF- magic header bytes: every valid PDF document begins with the five ASCII bytes 37, 80, 68, 70, 45 (%PDF-). Checking bytes[0..4] confirms the payload begins with a real PDF header.

Run the approved export and verify idempotency

  1. In Code.gs, change approveExport: false to approveExport: true, save, select exportApprovedSheetTabPdf, and click Run.

  2. Open the Drive URL printed in Execution log to inspect Harbor garden - materials summary.pdf inside PDF export practice outputs.

  3. Confirm that only Summary!A1:E7 appears on portrait letter paper without cell gridlines, excluding Scratch and G2's internal note.

  4. Run showSheetPdfExportRecord to inspect SHEET_PDF_EXPORT in PropertiesService.getScriptProperties(), including status: "complete", outputFileId, and byteLength.

  5. Run exportApprovedSheetTabPdf a second time. Because SHEET_PDF_EXPORT records status: "complete" and an untrashed Drive file, it logs Already exported. Reusing recorded Drive file ID: ... without creating a duplicate PDF.

LockService.getScriptLock() wraps the check-export-save sequence with waitLock(5000) and finally { lock.releaseLock(); } so two overlapping executions cannot both observe previous === null and save two identical PDFs. Saving status: 'pending' before UrlFetchApp.fetch ensures that if an execution times out after destination.createFile(pdfBlob) succeeds, the next run pauses and asks you to check PDF export practice outputs rather than creating a second file silently.

note

Spreadsheet /export requests and File.getAs(MimeType.PDF) conversions share both Apps Script's UrlFetchApp and Drive quotas (service quotas) and Google's undocumented per-user export rate limit on docs.google.com. Calling /export inside a loop across dozens of tabs or rows frequently triggers HTTP 429 Too Many Requests (and calling UrlFetchApp.fetch inside a loop also serializes network latency, as explained in service call inside loop). Export one consolidated summary tab when possible, checkpoint each completed file ID in PropertiesService so a retry skips already-exported items, and check pdfBlob.getBytes().length before passing a PDF to downstream mail services, since GmailApp and MailApp reject message attachments above 25 MB.

Common failures and cleanup

SymptomCause and fix
Set PDF_EXPORT.spreadsheetId to a Drive item ID...You left REPLACE_WITH_... in Code.gs or pasted the entire https://docs.google.com/... URL instead of the ID string between /d/ and /edit.
Spreadsheet PDF export failed with HTTP 401 or HTTP 403Either the signed-in user lacks view access to the spreadsheet, or appsscript.json restricted oauthScopes to spreadsheets.currentonly. Keep https://www.googleapis.com/auth/spreadsheets (or spreadsheets.readonly when no write or openById calls exist) in appsscript.json.
Expected content type application/pdf, received "text/html"The export URL pointed to an HTML interstitial or sign-in page instead of a rendered PDF. Confirm the spreadsheet ID is a native Google Sheet (not an uploaded .xlsx file that has not been converted to Google Sheets) and check gid.
Exported PDF shows yesterday's numbers or blank cells written moments earlierEnsure SpreadsheetApp.flush() runs after the last setValues() call and before UrlFetchApp.fetch, or doc.saveAndClose() runs before docFile.getAs(MimeType.PDF).
Rightmost column or bottom total row is cut off when useRangeWindow is trueRemember r2 and c2 are 0-indexed exclusive bounds (r2 = (startRow - 1) + numRows, c2 = (startColumn - 1) + numColumns). Setting c2 = 4 instead of 5 excludes column E (Subtotal).
Spreadsheet PDF export failed with HTTP 429Too many rapid /export calls hit Google's export rate limit. Wait a minute before retrying, and rely on the SHEET_PDF_EXPORT checkpoint rather than re-exporting unchanged tabs.

When you finish practicing:

  1. Set PDF_EXPORT.approveExport back to false in Code.gs, save, and run clearSheetPdfExportRecord to remove SHEET_PDF_EXPORT from Script Properties.

  2. Move Harbor garden - materials summary.pdf, the Harbor garden materials spreadsheet, the PDF export practice outputs folder, and the Sheet PDF export practice project to Trash.

Exercise: switch to landscape and freeze row 1

Test how layout parameters change the rendered PDF without duplicating files uncontrollably:

  1. In Harbor garden materials, select row 1 of Summary and choose View > Freeze > 1 row (or call sheet.setFrozenRows(1)).

  2. In Code.gs, set approveExport: false, run clearSheetPdfExportRecord, then change landscape: true and outputFileName: 'Harbor garden - materials landscape.pdf'.

  3. Run previewSheetPdfExport and confirm the logged URL now includes portrait=false and fzr=true.

  4. Set approveExport: true, save, and run exportApprovedSheetTabPdf once. Open the new Drive URL to verify landscape page orientation, then reset approveExport: false and clean up your practice files.

Next, read Create Gmail drafts from labeled messages to stage drafts with saved checkpoints before any email leaves your account.

Project files

Complete source files for this chapter's examples.

Preview and export a single Sheet tab window as a checkpointed PDF

Code.gs
const PDF_EXPORT = {
  spreadsheetId: 'REPLACE_WITH_SPREADSHEET_ID',
  destinationFolderId: 'REPLACE_WITH_DESTINATION_FOLDER_ID',
  sheetName: 'Summary',
  outputFileName: 'Harbor garden - materials summary.pdf',
  landscape: false,
  // Clip the PDF to A1:E7 (rows 1..7, columns 1..5 -> r1=0, c1=0, r2=7, c2=5).
  useRangeWindow: true,
  startRow: 1,
  startColumn: 1,
  numRows: 7,
  numColumns: 5,
  // Set approveExport to true only after inspecting previewSheetPdfExport.
  approveExport: false
};

/**
 * @typedef {{
 *   status: 'pending'|'complete',
 *   spreadsheetId: string,
 *   sheetId: number,
 *   sheetName: string,
 *   destinationFolderId: string,
 *   outputFileName: string,
 *   exportUrl: string,
 *   attemptedAt: string,
 *   outputFileId: string,
 *   byteLength: number
 * }} SheetPdfExportRecord
 */

const PDF_EXPORT_RECORD_KEY = 'SHEET_PDF_EXPORT';
const EXPECTED_SUMMARY_HEADERS = ['Line Item', 'Category', 'Quantity', 'Unit Cost', 'Subtotal'];
// ASCII bytes for "%PDF-" at the start of every valid PDF document.
const PDF_MAGIC_BYTES = [37, 80, 68, 70, 45];

/**
 * Dry-run preview. Validates the source spreadsheet, Summary tab, destination
 * Drive folder, and export query parameters without fetching or saving a PDF.
 *
 * @returns {void}
 */
function previewSheetPdfExport() {
  validatePdfExportSettings_();
  const source = inspectSourceSheet_();
  const destination = getUntrashedDestinationFolder_();
  const plan = buildSheetPdfExportPlan_(source.spreadsheet.getId(), source.sheet.getSheetId());

  Logger.log(
    'Source spreadsheet: "' +
      source.spreadsheet.getName() +
      '" (ID: ' +
      source.spreadsheet.getId() +
      '); tab: "' +
      source.sheet.getName() +
      '" (gid=' +
      source.sheet.getSheetId() +
      ').'
  );
  Logger.log(
    'Destination folder: "' + destination.getName() + '" (ID: ' + destination.getId() + ').'
  );
  Logger.log('Planned output file: ' + PDF_EXPORT.outputFileName);
  Logger.log('Window description: ' + plan.windowDescription);
  Logger.log('Export URL: ' + plan.exportUrl);
  showSheetPdfExportRecord();
  Logger.log('Preview complete. No HTTP export request or Drive file write occurred.');
}

/**
 * Exports the configured Sheet tab (and optional cell window) to a PDF in the
 * destination Drive folder once, recording the saved file ID in Script Properties.
 *
 * @returns {void}
 */
function exportApprovedSheetTabPdf() {
  validatePdfExportSettings_();
  if (PDF_EXPORT.approveExport !== true) {
    throw new Error(
      'Run previewSheetPdfExport first, then set PDF_EXPORT.approveExport to true when ready.'
    );
  }

  const lock = LockService.getScriptLock();
  lock.waitLock(5000);
  try {
    const previous = readPdfExportRecord_();
    if (previous !== null) {
      requireMatchingExportSettings_(previous);
      if (previous.status !== 'complete') {
        throw new Error(
          'An earlier PDF export attempt is pending. Inspect the destination folder and showSheetPdfExportRecord before retrying.'
        );
      }
      const existingFile = DriveApp.getFileById(previous.outputFileId);
      if (existingFile.isTrashed()) {
        throw new Error(
          'Recorded PDF file ' +
            previous.outputFileId +
            ' is in Trash. Restore it or run clearSheetPdfExportRecord after setting approveExport to false.'
        );
      }
      Logger.log(
        'Already exported. Reusing recorded Drive file ID: ' +
          existingFile.getId() +
          ' (' +
          previous.byteLength +
          ' bytes).'
      );
      Logger.log('Saved PDF URL: ' + existingFile.getUrl());
      return;
    }

    const source = inspectSourceSheet_();
    const destination = getUntrashedDestinationFolder_();
    const existingByName = destination.getFilesByName(PDF_EXPORT.outputFileName);
    if (existingByName.hasNext()) {
      throw new Error(
        'Destination folder already contains "' +
          PDF_EXPORT.outputFileName +
          '". Inspect it before exporting.'
      );
    }

    // Commit any pending spreadsheet edits so the PDF renders current cell values.
    SpreadsheetApp.flush();

    const plan = buildSheetPdfExportPlan_(source.spreadsheet.getId(), source.sheet.getSheetId());
    /** @type {SheetPdfExportRecord} */
    const record = {
      status: 'pending',
      spreadsheetId: source.spreadsheet.getId(),
      sheetId: source.sheet.getSheetId(),
      sheetName: source.sheet.getName(),
      destinationFolderId: destination.getId(),
      outputFileName: PDF_EXPORT.outputFileName,
      exportUrl: plan.exportUrl,
      attemptedAt: new Date().toISOString(),
      outputFileId: '',
      byteLength: 0
    };
    savePdfExportRecord_(record);

    const response = UrlFetchApp.fetch(plan.exportUrl, {
      headers: {
        Authorization: 'Bearer ' + ScriptApp.getOAuthToken()
      },
      muteHttpExceptions: true
    });

    const pdfBlob = validatePdfResponse_(response);
    pdfBlob.setName(PDF_EXPORT.outputFileName);
    const createdFile = destination.createFile(pdfBlob);

    record.status = 'complete';
    record.outputFileId = createdFile.getId();
    record.byteLength = pdfBlob.getBytes().length;
    savePdfExportRecord_(record);

    Logger.log(
      'Saved PDF "' +
        createdFile.getName() +
        '" (ID: ' +
        record.outputFileId +
        ', ' +
        record.byteLength +
        ' bytes).'
    );
    Logger.log('Saved PDF URL: ' + createdFile.getUrl());
  } finally {
    lock.releaseLock();
  }
}

/**
 * Logs the current SHEET_PDF_EXPORT checkpoint stored in Script Properties.
 *
 * @returns {void}
 */
function showSheetPdfExportRecord() {
  const raw = PropertiesService.getScriptProperties().getProperty(PDF_EXPORT_RECORD_KEY);
  Logger.log(PDF_EXPORT_RECORD_KEY + ': ' + (raw === null ? 'No recorded PDF export.' : raw));
}

/**
 * Deletes the saved SHEET_PDF_EXPORT checkpoint during practice cleanup after
 * approveExport has been reset to false. Does not delete Drive files.
 *
 * @returns {void}
 */
function clearSheetPdfExportRecord() {
  if (PDF_EXPORT.approveExport !== false) {
    throw new Error('Set PDF_EXPORT.approveExport back to false before clearing the export record.');
  }
  const lock = LockService.getScriptLock();
  lock.waitLock(5000);
  try {
    PropertiesService.getScriptProperties().deleteProperty(PDF_EXPORT_RECORD_KEY);
    Logger.log('Cleared ' + PDF_EXPORT_RECORD_KEY + ' from Script Properties.');
  } finally {
    lock.releaseLock();
  }
}

/**
 * Builds the docs.google.com /export URL strictly from a validated spreadsheet ID
 * and explicit encoded query parameters.
 *
 * @param {string} spreadsheetId
 * @param {number} sheetId
 * @returns {{ exportUrl: string, windowDescription: string }}
 */
function buildSheetPdfExportPlan_(spreadsheetId, sheetId) {
  requireDriveItemId_(spreadsheetId, 'spreadsheetId');

  /** @type {Array<[string, string]>} */
  const entries = [
    ['format', 'pdf'],
    ['gid', String(sheetId)],
    ['size', 'letter'],
    ['portrait', String(!PDF_EXPORT.landscape)],
    ['fitw', 'true'],
    ['gridlines', 'false'],
    ['sheetnames', 'false'],
    ['pagenumbers', 'false'],
    ['fzr', 'true']
  ];

  let windowDescription = 'Full tab (all populated cells on "' + PDF_EXPORT.sheetName + '")';
  if (PDF_EXPORT.useRangeWindow) {
    // Export window parameters use 0-indexed start coordinates and exclusive end coordinates.
    const r1 = PDF_EXPORT.startRow - 1;
    const c1 = PDF_EXPORT.startColumn - 1;
    const r2 = r1 + PDF_EXPORT.numRows;
    const c2 = c1 + PDF_EXPORT.numColumns;
    entries.push(['r1', String(r1)]);
    entries.push(['c1', String(c1)]);
    entries.push(['r2', String(r2)]);
    entries.push(['c2', String(c2)]);
    windowDescription =
      'Clipped cell window rows ' +
      PDF_EXPORT.startRow +
      '..' +
      (PDF_EXPORT.startRow + PDF_EXPORT.numRows - 1) +
      ', columns ' +
      PDF_EXPORT.startColumn +
      '..' +
      (PDF_EXPORT.startColumn + PDF_EXPORT.numColumns - 1) +
      ' (r1=' +
      r1 +
      ', c1=' +
      c1 +
      ', r2=' +
      r2 +
      ', c2=' +
      c2 +
      ')';
  }

  const queryString = entries
    .map(function (pair) {
      return encodeURIComponent(pair[0]) + '=' + encodeURIComponent(pair[1]);
    })
    .join('&');

  const exportUrl =
    'https://docs.google.com/spreadsheets/d/' +
    encodeURIComponent(spreadsheetId) +
    '/export?' +
    queryString;

  return {
    exportUrl: exportUrl,
    windowDescription: windowDescription
  };
}

/**
 * Verifies HTTP 200, application/pdf content type, and %PDF- magic header bytes.
 *
 * @param {UrlFetchApp.HTTPResponse} response
 * @returns {Blob}
 */
function validatePdfResponse_(response) {
  const statusCode = response.getResponseCode();
  if (statusCode !== 200) {
    const preview = response.getContentText().slice(0, 160).replace(/\s+/g, ' ');
    throw new Error('Spreadsheet PDF export failed with HTTP ' + statusCode + ': ' + preview);
  }

  const blob = response.getBlob();
  const contentType = blob.getContentType() || '';
  if (contentType !== MimeType.PDF) {
    throw new Error(
      'Expected content type ' + MimeType.PDF + ', received "' + contentType + '".'
    );
  }

  const bytes = blob.getBytes();
  if (bytes.length < PDF_MAGIC_BYTES.length) {
    throw new Error('Export response is shorter than a valid PDF header.');
  }
  for (let index = 0; index < PDF_MAGIC_BYTES.length; index += 1) {
    if (bytes[index] !== PDF_MAGIC_BYTES[index]) {
      throw new Error('Export response does not start with %PDF- header bytes.');
    }
  }
  return blob;
}

/**
 * @returns {{ spreadsheet: SpreadsheetApp.Spreadsheet, sheet: SpreadsheetApp.Sheet }}
 */
function inspectSourceSheet_() {
  const spreadsheet = SpreadsheetApp.openById(PDF_EXPORT.spreadsheetId);
  const sheet = spreadsheet.getSheetByName(PDF_EXPORT.sheetName);
  if (!sheet) {
    throw new Error(
      'Tab "' +
        PDF_EXPORT.sheetName +
        '" was not found in spreadsheet ' +
        PDF_EXPORT.spreadsheetId +
        '.'
    );
  }
  const minRows = PDF_EXPORT.useRangeWindow
    ? PDF_EXPORT.startRow + PDF_EXPORT.numRows - 1
    : 2;
  const minCols = PDF_EXPORT.useRangeWindow
    ? PDF_EXPORT.startColumn + PDF_EXPORT.numColumns - 1
    : EXPECTED_SUMMARY_HEADERS.length;
  if (sheet.getLastRow() < minRows || sheet.getLastColumn() < minCols) {
    throw new Error(
      'Tab "' +
        PDF_EXPORT.sheetName +
        '" needs at least ' +
        minRows +
        ' rows and ' +
        minCols +
        ' columns. Import sample.csv into Summary!A1 first.'
    );
  }
  const header = sheet.getRange(1, 1, 1, EXPECTED_SUMMARY_HEADERS.length).getValues()[0];
  const headerMatches = EXPECTED_SUMMARY_HEADERS.every(function (expected, index) {
    return String(header[index]).trim() === expected;
  });
  if (!headerMatches) {
    throw new Error(
      'Summary!A1:E1 must match headers: ' + EXPECTED_SUMMARY_HEADERS.join(', ') + '.'
    );
  }
  return { spreadsheet: spreadsheet, sheet: sheet };
}

/**
 * @returns {DriveApp.Folder}
 */
function getUntrashedDestinationFolder_() {
  const folder = DriveApp.getFolderById(PDF_EXPORT.destinationFolderId);
  if (folder.isTrashed()) {
    throw new Error('Destination Drive folder is in Trash. Restore it or choose an active folder.');
  }
  return folder;
}

/**
 * @returns {void}
 */
function validatePdfExportSettings_() {
  requireDriveItemId_(PDF_EXPORT.spreadsheetId, 'spreadsheetId');
  requireDriveItemId_(PDF_EXPORT.destinationFolderId, 'destinationFolderId');
  if (PDF_EXPORT.spreadsheetId === PDF_EXPORT.destinationFolderId) {
    throw new Error('spreadsheetId and destinationFolderId must refer to different Drive items.');
  }
  if (typeof PDF_EXPORT.sheetName !== 'string' || PDF_EXPORT.sheetName.trim() === '') {
    throw new Error('sheetName must be a non-empty string.');
  }
  if (
    typeof PDF_EXPORT.outputFileName !== 'string' ||
    !PDF_EXPORT.outputFileName.endsWith('.pdf') ||
    PDF_EXPORT.outputFileName.length < 5 ||
    PDF_EXPORT.outputFileName.length > 100
  ) {
    throw new Error('outputFileName must be a .pdf file name of at most 100 characters.');
  }
  if (PDF_EXPORT.useRangeWindow) {
    const coords = [
      PDF_EXPORT.startRow,
      PDF_EXPORT.startColumn,
      PDF_EXPORT.numRows,
      PDF_EXPORT.numColumns
    ];
    const validCoords = coords.every(function (num) {
      return Number.isInteger(num) && num >= 1 && num <= 200;
    });
    if (!validCoords) {
      throw new Error('Range window coordinates must be integers between 1 and 200.');
    }
  }
}

/**
 * Rejects placeholders, full URLs, query characters, or path segments so bearer
 * tokens are never sent outside https://docs.google.com/spreadsheets/d/<ID>/export.
 *
 * @param {string} value
 * @param {string} label
 * @returns {void}
 */
function requireDriveItemId_(value, label) {
  if (
    typeof value !== 'string' ||
    value.startsWith('REPLACE_') ||
    value.length < 10 ||
    value.length > 200 ||
    !/^[A-Za-z0-9_-]+$/.test(value)
  ) {
    throw new Error(
      'Set PDF_EXPORT.' + label + ' to a Drive item ID (letters, digits, _ or -), never a full URL.'
    );
  }
}

/**
 * @param {SheetPdfExportRecord} record
 * @returns {void}
 */
function savePdfExportRecord_(record) {
  PropertiesService.getScriptProperties().setProperty(PDF_EXPORT_RECORD_KEY, JSON.stringify(record));
}

/**
 * @returns {SheetPdfExportRecord|null}
 */
function readPdfExportRecord_() {
  const raw = PropertiesService.getScriptProperties().getProperty(PDF_EXPORT_RECORD_KEY);
  if (raw === null) {
    return null;
  }
  /** @type {SheetPdfExportRecord} */
  let record;
  try {
    record = JSON.parse(raw);
  } catch (error) {
    throw new Error('Saved SHEET_PDF_EXPORT property is not valid JSON. Inspect before clearing.');
  }
  if (
    !record ||
    typeof record !== 'object' ||
    (record.status !== 'pending' && record.status !== 'complete') ||
    typeof record.spreadsheetId !== 'string' ||
    typeof record.destinationFolderId !== 'string' ||
    typeof record.outputFileName !== 'string' ||
    typeof record.outputFileId !== 'string'
  ) {
    throw new Error('Saved SHEET_PDF_EXPORT property has an unexpected shape. Inspect before clearing.');
  }
  return record;
}

/**
 * @param {SheetPdfExportRecord} record
 * @returns {void}
 */
function requireMatchingExportSettings_(record) {
  if (
    record.spreadsheetId !== PDF_EXPORT.spreadsheetId ||
    record.destinationFolderId !== PDF_EXPORT.destinationFolderId ||
    record.sheetName !== PDF_EXPORT.sheetName ||
    record.outputFileName !== PDF_EXPORT.outputFileName
  ) {
    throw new Error(
      'PDF_EXPORT settings differ from the recorded export in SHEET_PDF_EXPORT. Restore settings or clear the record intentionally.'
    );
  }
}

appsscript.json

Download file
appsscript.json
{
  "timeZone": "Etc/UTC",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/drive",
    "https://www.googleapis.com/auth/script.external_request"
  ]
}

sample.csv

Download file
sample.csv
Line Item,Category,Quantity,Unit Cost,Subtotal
Harbor bench slats,Lumber,12,18.50,222.00
Galvanized anchor bolts,Hardware,24,2.75,66.00
Drip emitter tubing,Irrigation,3,34.00,102.00
Cedar planter trim,Lumber,8,21.00,168.00
Pathway gravel bags,Masonry,15,9.50,142.50
Total,,,,700.50