Google Sheets → Google Docs → PDF Workflow for Internal Memos

Option Explicit

Sub GenerateMemosFromExcel()
    Dim xlApp As Application
    Dim xlWb As Workbook
    Dim xlWs As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim wdApp As Object ' Word.Application
    Dim wdDoc As Object ' Word.Document
    Dim templatePath As String
    Dim outputFolder As String
    Dim pdfPath As String
    Dim memoID As String
    Dim memoDate As String
    Dim recipient As String
    Dim subject As String
    Dim bodyText As String

    '--- Configuration ---
    templatePath = "C:\Templates\MemoTemplate.docx"
    outputFolder = "C:\Memos\PDFs"

    Set xlApp = Application
    Set xlWb = xlApp.ActiveWorkbook
    Set xlWs = xlWb.Sheets(1)

    lastRow = xlWs.Cells(xlWs.Rows.Count, "A").End(xlUp).Row

    If Dir(outputFolder, vbDirectory) = "" Then MkDir outputFolder

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

    For i = 2 To lastRow 'Assume headers in row 1
        memoID = xlWs.Cells(i, "A").Value
        memoDate = xlWs.Cells(i, "B").Value
        recipient = xlWs.Cells(i, "C").Value
        subject = xlWs.Cells(i, "D").Value
        bodyText = xlWs.Cells(i, "E").Value

        Set wdDoc = wdApp.Documents.Open(templatePath, ReadOnly:=False)
        With wdDoc.Content.Find
            .ClearFormatting
            .Replacement.ClearFormatting
            .Text = "{{MemoID}}"
            .Replacement.Text = memoID
            .Execute Replace:=2
            .Text = "{{Date}}"
            .Replacement.Text = memoDate
            .Execute Replace:=2
            .Text = "{{Recipient}}"
            .Replacement.Text = recipient
            .Execute Replace:=2
            .Text = "{{Subject}}"
            .Replacement.Text = subject
            .Execute Replace:=2
            .Text = "{{Body}}"
            .Replacement.Text = bodyText
            .Execute Replace:=2
        End With

        pdfPath = outputFolder & "\Memo_" & memoID & ".pdf"
        wdDoc.ExportAsFixedFormat OutputFileName:=pdfPath, ExportFormat:=17 'wdExportFormatPDF
        wdDoc.Close SaveChanges:=False
    Next i

    wdApp.Quit
    Set wdDoc = Nothing
    Set wdApp = Nothing
    MsgBox "Memo PDFs generated in " & outputFolder, vbInformation
End Sub

function generateMemos() {
  const SHEET = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const DATA = SHEET.getDataRange().getValues();
  const TEMPLATE_ID = 'YOUR_DOC_TEMPLATE_ID'; // Docs template file ID
  const OUTPUT_FOLDER_ID = 'YOUR_OUTPUT_FOLDER_ID'; // Drive folder for PDFs
  const outputFolder = DriveApp.getFolderById(OUTPUT_FOLDER_ID);

  // Skip header row (index 0)
  for (let i = 1; i < DATA.length; i++) {
    const row = DATA[i];
    const memoId = row[0];
    const memoDate = row[1];
    const recipient = row[2];
    const subject = row[3];
    const body = row[4];

    // Make a copy of the template
    const copy = DriveApp.getFileById(TEMPLATE_ID).makeCopy(`Memo_${memoId}`, outputFolder);
    const doc = DocumentApp.openById(copy.getId());
    const bodyElement = doc.getBody();

    // Replace placeholders
    bodyElement.replaceText('{{MemoID}}', memoId);
    bodyElement.replaceText('{{Date}}', memoDate);
    bodyElement.replaceText('{{Recipient}}', recipient);
    bodyElement.replaceText('{{Subject}}', subject);
    bodyElement.replaceText('{{Body}}', body);

    doc.saveAndClose();

    // Export as PDF
    const pdfBlob = copy.getAs(MimeType.PDF);
    pdfBlob.setName(`Memo_${memoId}.pdf`);
    outputFolder.createFile(pdfBlob);

    // Optionally delete the intermediate Doc copy
    DriveApp.getFileById(copy.getId()).setTrashed(true);
  }

  SpreadsheetApp.getUi().alert('All memos have been generated as PDFs in the target folder.');
}