How to Automate PDF Generation from Google Sheets: The Complete Zero-Cost Architecture Guide

========================================================================
OpsSpire Technologies
Mumbai, Maharashtra, India | contact@opsspire.in
========================================================================

INVOICE #: {{Invoice_Number}}
Issue Date: {{Issue_Date}}
Due Date:   {{Due_Date}}

BILLED TO:
{{Client_Name}}
Attention: {{Client_Email}}

------------------------------------------------------------------------
DESCRIPTION OF WORK                            TOTAL
------------------------------------------------------------------------
{{Description}}                                {{Amount_Due}}
------------------------------------------------------------------------

Payment Terms: Net 30 days. Thank you for your partnership!
========================================================================

/**
 * Zero-Cost PDF Automation Engine
 * Designed for OpsSpire Technologies Tutorials
 */

const CONFIG = {
  TEMPLATE_DOC_ID: 'YOUR_GOOGLE_DOC_TEMPLATE_ID',
  OUTPUT_FOLDER_ID: 'YOUR_GOOGLE_DRIVE_FOLDER_ID',
  SHEET_NAME: 'Sheet1',
  HEADER_ROW: 1,
  COLUMNS: {
    INVOICE_NUM: 0,   // Col A
    CLIENT_NAME: 1,   // Col B
    CLIENT_EMAIL: 2,  // Col C
    ISSUE_DATE: 3,    // Col D
    DUE_DATE: 4,      // Col E
    DESCRIPTION: 5,   // Col F
    AMOUNT: 6,        // Col G
    STATUS: 7,        // Col H
    PDF_URL: 8        // Col I
  }
};

/**
 * Adds an interactive execution button to Google Sheets menu bar
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('🚀 Document Engine')
    .addItem('Generate Pending PDFs', 'batchGeneratePDFs')
    .addToUi();
}

/**
 * Loops through sheet, generates PDFs for pending rows, logs results
 */
function batchGeneratePDFs() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
  if (!sheet) {
    SpreadsheetApp.getUi().alert(`Sheet "${CONFIG.SHEET_NAME}" not found.`);
    return;
  }

  const dataRange = sheet.getDataRange();
  const displayValues = dataRange.getDisplayValues();
  const templateFile = DriveApp.getFileById(CONFIG.TEMPLATE_DOC_ID);
  const targetFolder = DriveApp.getFolderById(CONFIG.OUTPUT_FOLDER_ID);

  let processedCount = 0;

  for (let i = CONFIG.HEADER_ROW; i < displayValues.length; i++) {
    const row = displayValues[i];
    const status = row[CONFIG.COLUMNS.STATUS].trim();

    // Check if row has an ID and is not already processed
    if (row[CONFIG.COLUMNS.INVOICE_NUM] !== '' && status !== 'Generated') {
      try {
        const invoiceNum = row[CONFIG.COLUMNS.INVOICE_NUM];
        const clientName = row[CONFIG.COLUMNS.CLIENT_NAME];

        // Match placeholder keys to spreadsheet row columns
        const tags = {
          '{{Invoice_Number}}': invoiceNum,
          '{{Client_Name}}': clientName,
          '{{Client_Email}}': row[CONFIG.COLUMNS.CLIENT_EMAIL],
          '{{Issue_Date}}': row[CONFIG.COLUMNS.ISSUE_DATE],
          '{{Due_Date}}': row[CONFIG.COLUMNS.DUE_DATE],
          '{{Description}}': row[CONFIG.COLUMNS.DESCRIPTION],
          '{{Amount_Due}}': row[CONFIG.COLUMNS.AMOUNT]
        };

        const pdfUrl = createPDF(templateFile, targetFolder, tags, `Invoice_${invoiceNum}_${clientName}`);

        // Update sheet with Generated status and drive link
        sheet.getRange(i + 1, CONFIG.COLUMNS.STATUS + 1).setValue('Generated');
        sheet.getRange(i + 1, CONFIG.COLUMNS.PDF_URL + 1).setValue(pdfUrl);
        processedCount++;

      } catch (err) {
        Logger.log(`Error at row ${i + 1}: ${err.message}`);
        sheet.getRange(i + 1, CONFIG.COLUMNS.STATUS + 1).setValue(`Error: ${err.message}`);
      }
    }
  }

  SpreadsheetApp.getUi().alert(`Complete! Generated ${processedCount} new PDFs.`);
}

/**
 * Clones template, replaces tokens, renders PDF binary, cleans up
 */
function createPDF(templateFile, targetFolder, tags, fileName) {
  const tempDoc = templateFile.makeCopy(`${fileName}_TEMP`, targetFolder);
  const openDoc = DocumentApp.openById(tempDoc.getId());
  const body = openDoc.getBody();

  for (const [key, value] of Object.entries(tags)) {
    body.replaceText(key, value || '');
  }

  openDoc.saveAndClose();

  const pdfBlob = tempDoc.getAs('application/pdf');
  pdfBlob.setName(`${fileName}.pdf`);

  const finalPDF = targetFolder.createFile(pdfBlob);
  tempDoc.setTrashed(true);

  return finalPDF.getUrl();
}