Google Sheets PDF Scripts vs Word-Based Report Generation

Sub GenerateReports()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wdApp As Object ' Word.Application
    Dim wdDoc As Object ' Word.Document
    Dim lastRow As Long
    Dim i As Long
    Dim tmplPath As String
    Dim outFolder As String
    Dim imgFolder As String
    Dim pdfPath As String

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    tmplPath = "C:\Templates\ReportTemplate.docx"
    outFolder = "C:\Reports\PDF"
    imgFolder = "C:\Reports\Images"

    ' Ensure output folder exists
    If Dir(outFolder, vbDirectory) = "" Then MkDir outFolder

    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = False

    For i = 2 To lastRow ' Assuming header row
        Set wdDoc = wdApp.Documents.Open(tmplPath, ReadOnly:=False)
        ' Replace text placeholders
        wdDoc.Bookmarks("ClientName").Range.Text = ws.Cells(i, "B").Value
        wdDoc.Bookmarks("ReportDate").Range.Text = ws.Cells(i, "C").Value
        ' Insert images if files exist
        Dim logoPath As String
        logoPath = imgFolder & "\" & ws.Cells(i, "D").Value
        If Dir(logoPath) <> "" Then
            wdDoc.Bookmarks("photo_logo").Range.InlineShapes.AddPicture FileName:=logoPath, LinkToFile:=False, SaveWithDocument:=True
        End If
        Dim sigPath As String
        sigPath = imgFolder & "\" & ws.Cells(i, "E").Value
        If Dir(sigPath) <> "" Then
            wdDoc.Bookmarks("photo_signature").Range.InlineShapes.AddPicture FileName:=sigPath, LinkToFile:=False, SaveWithDocument:=True
        End If
        ' Export to PDF
        pdfPath = outFolder & "\" & ws.Cells(i, "B").Value & "_Report.pdf"
        wdDoc.ExportAsFixedFormat OutputFileName:=pdfPath, ExportFormat:=17 ' wdExportFormatPDF
        wdDoc.Close SaveChanges:=False
    Next i

    wdApp.Quit
    Set wdDoc = Nothing
    Set wdApp = Nothing
    MsgBox "Report generation complete.", vbInformation
End Sub

function generatePDFs() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Data');
  var data = sheet.getDataRange().getValues();
  var outputFolder = DriveApp.getFolderById('YOUR_OUTPUT_FOLDER_ID'); // Replace with your folder ID

  for (var i = 1; i < data.length; i++) { // Skip header row
    var row = data[i];
    var tempSheet = ss.insertSheet('Temp_' + i);
    // Copy header and current row to temp sheet for clean PDF layout
    tempSheet.getRange('A1').setValue('Client');
    tempSheet.getRange('B1').setValue('Date');
    tempSheet.getRange('C1').setValue('Amount');
    tempSheet.getRange('A2').setValue(row[1]); // Client name column B
    tempSheet.getRange('B2').setValue(row[2]); // Date column C
    tempSheet.getRange('C2').setValue(row[3]); // Amount column D

    // Export as PDF
    var pdfBlob = tempSheet.getAs('application/pdf').setName(row[1] + '_Report.pdf');
    outputFolder.createFile(pdfBlob);
    // Clean up temporary sheet
    ss.deleteSheet(tempSheet);
  }
  SpreadsheetApp.flush();
  Logger.log('PDF generation complete.');
}