How to Build a Hybrid Workflow: Google Sheets Intake, Word/PDF Output

Sub GenerateDocsFromCSV()
    Dim csvPath As String, imgFolder As String, outWordFolder As String, outPdfFolder As String
    Dim logPath As String, templatePath As String
    Dim fso As Object, ts As Object, wdApp As Object, wdDoc As Object
    Dim csvFile As Integer, line As String, parts() As String, headers() As String
    Dim rowNum As Long, i As Long

    ' Settings
    csvPath = "C:\Shared\Intake\export.csv"
    imgFolder = "C:\Shared\Images\"
    outWordFolder = "C:\Shared\Output\WORD\"
    outPdfFolder = "C:\Shared\Output\PDF\"
    templatePath = "C:\Templates\ReportTemplate.docx"
    logPath = "C:\Shared\Output\process_log.txt"

    ' Ensure output folders exist
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(outWordFolder) Then fso.CreateFolder outWordFolder
    If Not fso.FolderExists(outPdfFolder) Then fso.CreateFolder outPdfFolder

    Set ts = fso.OpenTextFile(logPath, 2, True)
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = False

    csvFile = FreeFile
    Open csvPath For Input As #csvFile
    rowNum = 0
    Do While Not EOF(csvFile)
        Line Input #csvFile, line
        rowNum = rowNum + 1
        parts = Split(line, ",")
        If rowNum = 1 Then
            headers = parts   ' store header row for placeholder mapping
            Continue Do
        End If
        If UBound(parts) < LBound(headers) Then
            ts.WriteLine "Row " & rowNum & ": column mismatch"
            Continue Do
        End If

        Set wdDoc = wdApp.Documents.Open(templatePath, ReadOnly:=False)

        ' Replace each {{Header}} token with the column value
        For i = LBound(parts) To UBound(parts)
            Dim placeholder As String
            placeholder = "{{" & Trim(headers(i)) & "}}"
            wdDoc.Content.Find.Execute FindText:=placeholder, ReplaceWith:=Trim(parts(i)), Replace:=2
        Next i

        ' Insert image if filename provided in the last column
        Dim imgName As String, imgPath As String
        imgName = Trim(parts(UBound(parts)))   ' assumes last column holds image name
        If imgName <> "" Then
            imgPath = imgFolder & imgName
            If fso.FileExists(imgPath) Then
                wdDoc.InlineShapes.AddPicture FileName:=imgPath, LinkToFile:=False, SaveWithDocument:=True
            Else
                ts.WriteLine "Row " & rowNum & ": missing image " & imgPath
            End If
        End If

        Dim docName As String, pdfName As String
        docName = outWordFolder & "Doc_" & rowNum & ".docx"
        pdfName = outPdfFolder & "Doc_" & rowNum & ".pdf"
        wdDoc.SaveAs2 docName, 16   ' wdFormatXMLDocument
        wdDoc.ExportAsFixedFormat OutputFileName:=pdfName, ExportFormat:=17   ' wdExportFormatPDF
        wdDoc.Close False
        ts.WriteLine "Row " & rowNum & ": success"
    Loop
    Close #csvFile
    wdApp.Quit
    ts.Close
    MsgBox "Document generation complete. See log at " & logPath, vbInformation
End Sub

function exportToCSV() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Intake'); // adjust name as needed
  var data = sheet.getDataRange().getValues();
  var csv = '';
  for (var i = 0; i < data.length; i++) {
    var row = data[i].join(',');
    csv += row + '\n';
  }
  // Save CSV to a shared Drive folder
  var folderId = 'YOUR_SHARED_DRIVE_FOLDER_ID'; // replace with actual folder ID
  var folder = DriveApp.getFolderById(folderId);
  var file = folder.createFile('export.csv', csv, MimeType.CSV);
  Logger.log('CSV exported to %s', file.getUrl());
  // Optional: create a JSON map of image filenames for easier lookup
  var json = {};
  var headers = data[0];
  var imgColIndex = headers.indexOf('Photo_Logo'); // example column name
  if (imgColIndex > -1) {
    for (var r = 1; r < data.length; r++) {
      var key = data[r][0]; // assume first column is a unique ID
      json[key] = data[r][imgColIndex];
    }
    var jsonFile = folder.createFile('imageMap.json', JSON.stringify(json), MimeType.JSON);
    Logger.log('Image map JSON created: %s', jsonFile.getUrl());
  }
}