Google Forms → Google Sheets → Word/PDF Workflow for Field Intake

Sub GenerateReport()
    On Error GoTo ErrHandler
    Dim ws As Worksheet, lastRow As Long, rng As Range
    Dim wdApp As Object, wdDoc As Object
    Dim pdfPath As String
    '--- Locate the most recent ready row ---
    Set ws = ThisWorkbook.Sheets("Ready")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    Set rng = ws.Rows(lastRow)
    '--- Start Word ---
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = False
    Set wdDoc = wdApp.Documents.Open("C:\Templates\FieldReport.docx")
    '--- Populate bookmarks safely ---
    If wdDoc.Bookmarks.Exists("Name") Then wdDoc.Bookmarks("Name").Range.Text = rng.Cells(1, 1).Value
    If wdDoc.Bookmarks.Exists("Site") Then wdDoc.Bookmarks("Site").Range.Text = rng.Cells(1, 2).Value
    '--- Add additional fields as needed ---
    '--- Export as PDF ---
    pdfPath = "C:\Reports\" & Format(Now, "yyyymmdd_hhnnss") & ".pdf"
    wdDoc.ExportAsFixedFormat OutputFileName:=pdfPath, ExportFormat:=17
    wdDoc.Close SaveChanges:=False
    wdApp.Quit
    MsgBox "Report generated: " & pdfPath, vbInformation
    Exit Sub
ErrHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
    If Not wdDoc Is Nothing Then wdDoc.Close SaveChanges:=False
    If Not wdApp Is Nothing Then wdApp.Quit
End Sub

function onFormSubmit(e) {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var readySheet = ss.getSheetByName('Ready');
    if (!readySheet) {
      readySheet = ss.insertSheet('Ready');
    }
    var values = e.values; // Includes timestamp as first column
    readySheet.appendRow(values);
    // Mark the row as queued for VBA processing
    var lastRow = readySheet.getLastRow();
    readySheet.getRange(lastRow, values.length + 1).setValue('Queued');
    // Simple audit log entry
    Logger.log('Form submission added to Ready sheet at row %d', lastRow);
  } catch (err) {
    // Notify the admin if something goes wrong
    MailApp.sendEmail(Session.getEffectiveUser().getEmail(),
      'Form‑to‑VBA workflow error',
      'Error processing form submission: ' + err.message);
  }
}