Fix: Google Apps Script PDF Exports Using the Wrong Sheet Data
Stop exporting whichever tab or row happens to be active. Pin the spreadsheet, source sheet, template sheet, and business record explicitly; lock the shared template; flush pending writes; and verify the PDF response before saving it.
Quick answer: remove active-state dependencies
Wrong-sheet PDFs usually come from one of two hidden dependencies: the script reads the active tab or it exports a template that another execution has already changed.
Pin every identity
Open the spreadsheet by ID, resolve source and template sheets by configured names, select the source record by a unique RecordID, and put the template’s numeric sheet ID in the export URL.
Protect the shared template
Acquire a script lock before writing mapped cells. Flush the writes, verify the mapped values, export the exact template gid, then clear the template before releasing the lock.
Validate the response
Do not save every response as if it were a PDF. Check HTTP 200, MIME type, nontrivial size, and the %PDF signature first.
Reject existing filenames
Build the name from the stable record ID and check for an existing file before and after PDF export. A script lock cannot prevent another project from creating a same-name Drive file.
Find which identity changed
Log the spreadsheet ID, source sheet, source row, record ID, template sheet ID, and output filename for every export. The mismatch normally appears in one of these six places.
Wrong spreadsheet
A standalone script or copied project opens a different file than the one being reviewed. Configure the spreadsheet ID instead of assuming an active container.
Wrong source sheet
getActiveSheet() follows UI state. A trigger or another user can make that state unrelated to the intended source.
Wrong record after sorting
A stored row number points to a position, not a business record. Find exactly one matching RecordID at runtime.
Wrong template gid
The export URL points at another tab because gid was copied, hard-coded, or taken from the wrong Sheet object.
Stale template values
The script fetches the PDF before pending writes are committed. Call SpreadsheetApp.flush() and verify the template cells before export.
Concurrent overwrite
Two executions reuse one template sheet. The second record replaces cells while the first PDF is being rendered. Serialize that critical section with LockService.
Use one fail-closed export order
A repeatable sequence makes the record identity traceable from Google Sheets to the saved PDF.
1. Acquire the script lock
Stop overlapping executions before any shared template cell is cleared or written.
2. Resolve exact objects
Open the configured spreadsheet and require the named source and template sheets.
3. Find one RecordID
Read headers and displayed values, then reject zero or multiple matches.
4. Populate and verify
Clear only mapped template input cells, write the selected record, flush, and compare every target cell.
5. Export the template gid
Build the PDF request with templateSheet.getSheetId(), not the active tab or a copied URL fragment.
6. Validate, clear, and save
Check the PDF response, clear mapped inputs while holding the lock, recheck the filename, then create the Drive file.
Set up the Google Sheet contract
The code below is deliberately narrow. Configure these four objects before the first authorization run.
Records sheet
Use unique row-1 headers: RecordID, CustomerName, InvoiceDateDisplay, and AmountDisplay. RecordID must be unique and nonblank.
Export_Record_ID named range
Create one named cell that contains the exact record ID to export. The script reads this value instead of the active row or selection.
PdfTemplate sheet
Reserve input cells B4, B6, F4, and F6. The script clears and rewrites only these mapped cells.
IDs and export range
Paste the spreadsheet ID and output folder ID into CONFIG. The sample exports A1:H40; change the zero-based range only after a real print test.
This defect exists inside Google Sheets and Apps Script. Adding Excel VBA, CSV synchronization, or a second set of named ranges creates another state boundary without fixing the wrong gid or concurrent-template problem.
Google Apps Script: export one verified record from one exact sheet
Paste this into the Apps Script project attached to the Google Sheet, replace the two placeholder IDs, confirm the sheet names, field map, and export range, then run exportConfiguredRecordPdf once from the editor to authorize the required services.
No active sheet
The script uses openById, getSheetByName, and the template’s getSheetId().
No stored row number
It searches displayed values for exactly one requested RecordID.
No overlapping template writes
A script lock covers clear, write, flush, export, and cleanup.
Existing names are rejected
The script checks for an existing exact filename before preparation and again before saving; Drive does not enforce unique filenames globally.
No unverified response
The HTTP code, MIME type, size, and PDF signature are checked before Drive receives the file.
Clean before saving
Mapped inputs are cleared and flushed before the Drive PDF is created; finally retries cleanup after an earlier failure.
const CONFIG = Object.freeze({
spreadsheetId: 'PASTE_SPREADSHEET_ID',
sourceSheetName: 'Records',
templateSheetName: 'PdfTemplate',
outputFolderId: 'PASTE_OUTPUT_FOLDER_ID',
recordIdNamedRange: 'Export_Record_ID',
recordIdHeader: 'RecordID',
outputNamePrefix: 'Record_',
exportRange: Object.freeze({
firstRow: 0,
firstColumn: 0,
lastRowExclusive: 40,
lastColumnExclusive: 8,
}),
fieldMap: Object.freeze([
Object.freeze({ header: 'RecordID', targetCell: 'B4', required: true }),
Object.freeze({ header: 'CustomerName', targetCell: 'B6', required: true }),
Object.freeze({ header: 'InvoiceDateDisplay', targetCell: 'F4', required: false }),
Object.freeze({ header: 'AmountDisplay', targetCell: 'F6', required: false }),
]),
});
function exportConfiguredRecordPdf() {
const lock = LockService.getScriptLock();
lock.waitLock(30000);
let templateSheet = null;
let templateWasPrepared = false;
try {
validateConfig_();
const spreadsheet = SpreadsheetApp.openById(CONFIG.spreadsheetId);
const sourceSheet = requireSheet_(spreadsheet, CONFIG.sourceSheetName);
templateSheet = requireSheet_(spreadsheet, CONFIG.templateSheetName);
if (sourceSheet.getSheetId() === templateSheet.getSheetId()) {
throw new Error('The source sheet and PDF template sheet must be different sheets.');
}
const controlRange = spreadsheet.getRangeByName(CONFIG.recordIdNamedRange);
if (!controlRange) {
throw new Error(
'Named range not found: ' + CONFIG.recordIdNamedRange,
);
}
if (controlRange.getNumRows() !== 1 || controlRange.getNumColumns() !== 1) {
throw new Error('The RecordID named range must contain exactly one cell.');
}
const recordId = String(controlRange.getDisplayValue()).trim();
if (!recordId) {
throw new Error(
'Named range ' + CONFIG.recordIdNamedRange + ' is blank.',
);
}
const record = readUniqueRecord_(sourceSheet, recordId);
const outputFileName = buildOutputFileName_(recordId);
const outputFolder = DriveApp.getFolderById(CONFIG.outputFolderId);
if (outputFolder.getFilesByName(outputFileName).hasNext()) {
throw new Error(
'Output file already exists; nothing was overwritten: ' + outputFileName,
);
}
templateWasPrepared = true;
writeTemplate_(templateSheet, record);
SpreadsheetApp.flush();
Utilities.sleep(500);
verifyTemplate_(templateSheet, record);
const pdfBlob = fetchPdfBlob_(
spreadsheet.getId(),
templateSheet.getSheetId(),
outputFileName,
);
// Clean the shared template before committing a new Drive file.
// If cleanup fails, finally retries it and no PDF is committed here.
clearTemplate_(templateSheet);
SpreadsheetApp.flush();
templateWasPrepared = false;
// Check again after the export request. This still does not create
// a cross-project or cross-user uniqueness guarantee in Drive.
if (outputFolder.getFilesByName(outputFileName).hasNext()) {
throw new Error('Output filename appeared during export: ' + outputFileName);
}
const outputFile = outputFolder.createFile(pdfBlob);
console.log(
JSON.stringify({
recordId,
sourceSheet: sourceSheet.getName(),
sourceRow: record.rowNumber,
templateSheet: templateSheet.getName(),
templateSheetId: templateSheet.getSheetId(),
outputFileId: outputFile.getId(),
outputFileName,
}),
);
return outputFile.getUrl();
} finally {
try {
if (templateSheet && templateWasPrepared) {
clearTemplate_(templateSheet);
SpreadsheetApp.flush();
}
} finally {
if (lock.hasLock()) {
lock.releaseLock();
}
}
}
}
function validateConfig_() {
const placeholderPattern = /^PASTE_/;
if (placeholderPattern.test(CONFIG.spreadsheetId)) {
throw new Error('Set CONFIG.spreadsheetId before running the export.');
}
if (placeholderPattern.test(CONFIG.outputFolderId)) {
throw new Error('Set CONFIG.outputFolderId before running the export.');
}
if (!CONFIG.fieldMap.length) {
throw new Error('CONFIG.fieldMap must contain at least one mapping.');
}
const headers = new Set();
const targets = new Set();
for (const mapping of CONFIG.fieldMap) {
if (!mapping.header || !mapping.targetCell) {
throw new Error('Every fieldMap item needs header and targetCell values.');
}
if (headers.has(mapping.header)) {
throw new Error('Duplicate fieldMap header: ' + mapping.header);
}
if (targets.has(mapping.targetCell)) {
throw new Error('Duplicate fieldMap target cell: ' + mapping.targetCell);
}
headers.add(mapping.header);
targets.add(mapping.targetCell);
}
if (!headers.has(CONFIG.recordIdHeader)) {
throw new Error(
'fieldMap must include the configured record ID header: ' +
CONFIG.recordIdHeader,
);
}
}
function requireSheet_(spreadsheet, sheetName) {
const sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
throw new Error('Sheet not found: ' + sheetName);
}
return sheet;
}
function readUniqueRecord_(sourceSheet, requestedRecordId) {
const lastRow = sourceSheet.getLastRow();
const lastColumn = sourceSheet.getLastColumn();
if (lastRow < 2 || lastColumn < 1) {
throw new Error('The source sheet has no data rows.');
}
const displayValues = sourceSheet
.getRange(1, 1, lastRow, lastColumn)
.getDisplayValues();
const headers = displayValues[0].map((value) => String(value).trim());
const headerIndex = buildUniqueHeaderIndex_(headers);
for (const mapping of CONFIG.fieldMap) {
if (!headerIndex.has(mapping.header)) {
throw new Error('Required source header missing: ' + mapping.header);
}
}
const recordIdIndex = headerIndex.get(CONFIG.recordIdHeader);
const matches = [];
const seenRecordIds = new Set();
for (let index = 1; index < displayValues.length; index += 1) {
const currentRecordId = String(displayValues[index][recordIdIndex]).trim();
if (!currentRecordId) {
throw new Error('RecordID is blank at source row ' + (index + 1));
}
if (seenRecordIds.has(currentRecordId)) {
throw new Error('Duplicate RecordID at source row ' +
(index + 1) + ': ' + currentRecordId);
}
seenRecordIds.add(currentRecordId);
if (currentRecordId === requestedRecordId) {
matches.push({ rowNumber: index + 1, rowValues: displayValues[index] });
}
}
if (matches.length === 0) {
throw new Error('RecordID not found: ' + requestedRecordId);
}
if (matches.length > 1) {
throw new Error('RecordID is not unique: ' + requestedRecordId);
}
const valuesByHeader = new Map();
for (const mapping of CONFIG.fieldMap) {
const value = String(matches[0].rowValues[headerIndex.get(mapping.header)]);
if (mapping.required && !value.trim()) {
throw new Error(
'Required value is blank at row ' +
matches[0].rowNumber +
', header ' +
mapping.header,
);
}
valuesByHeader.set(mapping.header, value);
}
return {
rowNumber: matches[0].rowNumber,
valuesByHeader,
};
}
function buildUniqueHeaderIndex_(headers) {
const headerIndex = new Map();
headers.forEach((header, index) => {
if (!header) {
return;
}
if (headerIndex.has(header)) {
throw new Error('Duplicate source header: ' + header);
}
headerIndex.set(header, index);
});
return headerIndex;
}
function writeTemplate_(templateSheet, record) {
const targetCells = CONFIG.fieldMap.map((mapping) => mapping.targetCell);
templateSheet.getRangeList(targetCells).clearContent();
for (const mapping of CONFIG.fieldMap) {
const value = record.valuesByHeader.get(mapping.header);
templateSheet.getRange(mapping.targetCell).setValue(value);
}
}
function verifyTemplate_(templateSheet, record) {
for (const mapping of CONFIG.fieldMap) {
const expected = record.valuesByHeader.get(mapping.header);
const actual = templateSheet.getRange(mapping.targetCell).getDisplayValue();
if (actual !== expected) {
throw new Error(
'Template verification failed at ' +
mapping.targetCell +
' for header ' +
mapping.header +
'. Expected "' +
expected +
'", found "' +
actual +
'".',
);
}
}
}
function clearTemplate_(templateSheet) {
const targetCells = CONFIG.fieldMap.map((mapping) => mapping.targetCell);
templateSheet.getRangeList(targetCells).clearContent();
}
function fetchPdfBlob_(spreadsheetId, sheetId, outputFileName) {
const range = CONFIG.exportRange;
const parameters = {
format: 'pdf',
size: 'A4',
portrait: 'true',
fitw: 'true',
sheetnames: 'false',
printtitle: 'false',
pagenum: 'UNDEFINED',
gridlines: 'false',
fzr: 'false',
attachment: 'true',
top_margin: '0.50',
bottom_margin: '0.50',
left_margin: '0.50',
right_margin: '0.50',
gid: String(sheetId),
r1: String(range.firstRow),
c1: String(range.firstColumn),
r2: String(range.lastRowExclusive),
c2: String(range.lastColumnExclusive),
};
const query = Object.entries(parameters)
.map(([key, value]) =>
encodeURIComponent(key) + '=' + encodeURIComponent(value),
)
.join('&');
const url =
'https://docs.google.com/spreadsheets/d/' +
encodeURIComponent(spreadsheetId) +
'/export?' +
query;
const response = UrlFetchApp.fetch(url, {
method: 'get',
headers: {
Authorization: 'Bearer ' + ScriptApp.getOAuthToken(),
},
muteHttpExceptions: true,
});
const responseCode = response.getResponseCode();
if (responseCode !== 200) {
throw new Error(
'PDF export failed with HTTP ' +
responseCode +
': ' +
response.getContentText().slice(0, 300),
);
}
const blob = response.getBlob().setName(outputFileName);
const bytes = blob.getBytes();
const signature = bytes
.slice(0, 4)
.map((value) => String.fromCharCode(value & 255))
.join('');
if (blob.getContentType() !== 'application/pdf') {
throw new Error('The export response is not a PDF: ' + blob.getContentType());
}
if (bytes.length < 100 || signature !== '%PDF') {
throw new Error('The export response does not contain a valid PDF header.');
}
return blob;
}
function buildOutputFileName_(recordId) {
const safeRecordId = String(recordId)
.trim()
.replace(/\\/g, '_')
.split('/').join('_')
.replace(/[:*?"<>|]/g, '_')
.replace(/\s+/g, ' ')
.slice(0, 100);
if (!safeRecordId || safeRecordId === '.' || safeRecordId === '..') {
throw new Error('RecordID cannot produce a safe output filename.');
}
return CONFIG.outputNamePrefix + safeRecordId + '.pdf';
}The code verifies source identity, mapped template values, and the returned PDF envelope. It does not parse PDF text or prove visual layout. getDisplayValues() follows the spreadsheet locale, so use explicit display columns when output punctuation must remain fixed across regions. Drive allows duplicate filenames; the two name checks and script lock protect only this script’s workflow, not external writers or other Apps Script projects.
Official references: Google’s PDF generation sample, Spreadsheet and sheet IDs, Lock Service, SpreadsheetApp.flush(), Range.getDisplayValues(), and HTTPResponse checks.
Run six failure tests before enabling a trigger
Use a test copy and a test Drive folder. A successful button click is not enough evidence for an automated PDF workflow.
Change the active tab
Open another sheet before running. The export must still use the configured Records and PdfTemplate sheets.
Sort the source rows
The requested RecordID must still resolve to the correct customer after its row number changes.
Duplicate the RecordID
The run must stop before writing the template or creating a Drive file.
Start two runs
The second execution must wait for the script lock instead of overwriting the first record’s template values.
Reuse the filename
The script must stop on an existing PDF filename. This prevents duplicates within the tested workflow, not collisions with unrelated Drive writers.
Review the actual PDF
Confirm RecordID, customer, dates, amounts, pagination, and filename in the saved file—not only in the template sheet.
Use a local Factory workflow when Google export is no longer the right boundary
DocxForge Pro Factory fits many Excel rows, one standard Word template, and one separate DOCX or PDF per row. It does not connect directly to Google Sheets or run Apps Script. Export or approve the source as an Excel workbook, validate that handoff, and then run the local batch.
Keep the Google fix for Google output
If the workflow must stay in Google Sheets, repair IDs, locking, flush order, and PDF verification in Apps Script.
Use Factory for a local batch
Choose Factory when each approved Excel row should create its own file from one standard Word template.
Define the handoff
Freeze the approved Google data into a reviewed Excel source. Do not imply a live Google Sheets connector.
Review real outputs
The local path still needs duplicate-ID checks, naming rules, template testing, and DOCX/PDF review.
Factory: inspect the approved Excel source and generate the batch
Frequently asked questions
Short answers about wrong-sheet exports, stale template values, concurrency, and the local Factory alternative.
Why does Apps Script export the sheet I last viewed?
The script probably depends on getActiveSheet(), the current selection, or a stale gid. A trigger has no reliable relationship to the tab a user expects. Open the spreadsheet by ID, resolve the source and template explicitly, and export the template’s actual getSheetId().
Why is the template correct but the PDF contains older values?
Pending spreadsheet writes may not have reached the export request, or another execution may have overwritten the shared template. Hold a script lock, write and verify the template, call SpreadsheetApp.flush(), and only then fetch the PDF.
Why select a record by RecordID instead of row number?
Rows move when users sort, insert, or delete data. A unique RecordID identifies the business record independently of its current position. The script should stop if the ID is missing or duplicated.
Does LockService prevent every possible edit collision?
It prevents concurrent executions in the same Apps Script project from entering the protected section. It does not stop a person or a different project from editing the template, so keep the template restricted and verify its mapped cells immediately before export.
Does checking the PDF header prove the document is visually correct?
No. The HTTP status, MIME type, and %PDF signature show that a PDF response was returned. A reviewer must still inspect identity fields, layout, pagination, formulas, images, and the final filename.
When is DocxForge Pro Factory the relevant alternative?
Factory fits a local Windows workflow where many Excel rows create separate files from one standard Word template. It does not read Google Sheets directly or execute Apps Script; export the approved source to Excel and validate that local handoff first.