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//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.' ); } }