Generate Google Docs from Sheets Data with Apps Script: Automated Document Templates

Most document-heavy businesses have the same problem in disguise. The raw data lives in a spreadsheet — client names, invoice amounts, job details, offer letter fields — and the finished document is supposed to look professional: correct formatting, company letterhead, the right layout. The gap between those two things is filled manually, usually by someone opening a template, copying and pasting, saving a new file, and doing it again for the next row.

For ten records that is tedious. For fifty it is a significant time cost. For five hundred it is not a viable process at all.

Google Apps Script closes this gap by letting you write a script that reads each row from a Google Sheet and produces a finished Google Doc — or a PDF filed in a Drive folder — for every record, automatically, in one run. You define the template once. The script handles every instance of data substitution. The output is a folder of correctly named, properly formatted documents with no human copying and pasting involved.

This tutorial covers the complete implementation: designing a Google Docs template with placeholders, reading row data from Sheets, using DocumentApp and body.replaceText() to perform the merge, exporting PDFs, saving documents to specific Drive folders, and batch processing multiple rows. Three practical examples are included at the end — an invoice generator, an offer letter generator, and a client report generator.

Step 1: Create a Google Docs Template with Placeholders

The template is a standard Google Doc that you design once. Everything that stays the same across all documents — your logo, company name, standard clauses, formatting — goes into the template as normal content. Variable content is replaced with placeholder text that the script will substitute for real data at runtime.

Placeholder convention

The most reliable placeholder format is double curly braces: {{FieldName}}. This format is easy to search for programmatically and unlikely to appear in normal document text by accident. A placeholder for a client's name would be written as {{ClientName}}. A placeholder for an invoice amount would be {{InvoiceAmount}}.

Keep placeholder names consistent with the column headers you plan to use in Google Sheets. If your spreadsheet has a column called ClientName, your template placeholder should be {{ClientName}}. Inconsistency here is the most common source of merge failures.

Template design tips

• Create the template in Google Docs format rather than importing from Word. Imported documents sometimes carry hidden formatting markup that interferes with replaceText() searches.
• Avoid placing placeholders inside text boxes, drawings, or shapes. Apps Script's body.replaceText() operates on the main document body. Headers and footers require a separate call using header.replaceText().
• Leave the template document unedited after setting it up, and store it in a Drive folder that is not part of your output destination. The script will make a copy of this template for each document — the original should remain untouched.

Sample template content

A simple invoice template might look like this in the Google Doc:

{{CompanyName}}
ABN: {{ABN}}

INVOICE

Invoice Number:  {{InvoiceNumber}}
Date:            {{InvoiceDate}}
Due Date:        {{DueDate}}

Bill To:
{{ClientName}}
{{ClientAddress}}

Description: {{ServiceDescription}}
Amount:      {{Amount}} (inc. GST)

Payment Terms: {{PaymentTerms}}
BSB/Account: {{BankDetails}}

Every piece of data that changes per document is a placeholder. Everything else — headings, labels, formatting — is fixed content in the template.

Step 2: Structure the Google Sheets Data Source

Open Google Sheets and create a spreadsheet where each row represents one document to generate. Row 1 must contain your column headers, and those headers should match the placeholder field names in your template (without the curly braces).

For the invoice example, a minimal column structure looks like this:

Keep your data clean before running any script:

• No blank rows between records
• One record per row
• Consistent formatting within each column — dates formatted the same way, amounts formatted with the currency symbol and decimal places you want to appear in the output
• No merged cells in the data range

The column order in the sheet does not matter to the script. What matters is that the header names match the placeholder names in your template.

Step 3: Write the Apps Script — DocumentApp Basics

Open the Google Sheet, go to Extensions > Apps Script, and delete the default empty function. The script editor is where you will write and run the automation.

Apps Script interacts with Google Docs through the DocumentApp service. The key methods you need for document generation are:

• DocumentApp.openById(id) — opens an existing Google Doc by its file ID
• doc.getBody() — returns the main body of the document
• body.replaceText(searchText, replaceText) — finds all instances of a text string and replaces them
• doc.saveAndClose() — saves and closes the document

For creating copies of a template and saving them to Drive, you use DriveApp:

• DriveApp.getFileById(id) — gets a file reference
• file.makeCopy(name, folder) — makes a copy with a new name in a specified folder
• DriveApp.getFolderById(id) — gets a folder reference

Here is a foundational function that opens a template, fills in a single set of values, and saves the output:

function generateSingleDocument() {
  // IDs from the Google Drive URL of each file/folder
  var TEMPLATE_ID = 'YOUR_TEMPLATE_DOC_ID';
  var OUTPUT_FOLDER_ID = 'YOUR_OUTPUT_FOLDER_ID';

  // Sample data for one document
  var data = {
    'ClientName': 'Mitchell Group',
    'InvoiceNumber': 'INV-001',
    'InvoiceDate': '19 Feb 2026',
    'Amount': '$4,800.00'
  };

  // Get the template file and the output folder
  var templateFile = DriveApp.getFileById(TEMPLATE_ID);
  var outputFolder = DriveApp.getFolderById(OUTPUT_FOLDER_ID);

  // Make a copy of the template named after the invoice number
  var newFileName = 'Invoice ' + data['InvoiceNumber'] + ' - ' + data['ClientName'];
  var newFile = templateFile.makeCopy(newFileName, outputFolder);

  // Open the copied document and get its body
  var doc = DocumentApp.openById(newFile.getId());
  var body = doc.getBody();

  // Replace each placeholder with real data
  for (var field in data) {
    body.replaceText('{{' + field + '}}', data[field]);
  }

  // Save and close the document
  doc.saveAndClose();

  Logger.log('Document created: ' + newFileName);
}

To get the file ID for your template, open the Google Doc in your browser and copy the long string of characters in the URL between /d/ and /edit. The folder ID is the string at the end of the Google Drive folder URL.

Step 4: Batch Generate Documents from Multiple Rows

The single-document function above is the building block. For batch processing, you wrap it in a loop that reads every row from the spreadsheet and generates one document per row.

function generateDocumentsFromSheet() {
  var TEMPLATE_ID = 'YOUR_TEMPLATE_DOC_ID';
  var OUTPUT_FOLDER_ID = 'YOUR_OUTPUT_FOLDER_ID';
  var SHEET_NAME = 'Sheet1'; // The tab name in your spreadsheet

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(SHEET_NAME);

  // Read all data including headers
  var data = sheet.getDataRange().getValues();
  var headers = data[0]; // Row 1 is column headers
  var rows = data.slice(1); // Everything from row 2 onwards is data

  var templateFile = DriveApp.getFileById(TEMPLATE_ID);
  var outputFolder = DriveApp.getFolderById(OUTPUT_FOLDER_ID);

  var generatedCount = 0;
  var errors = [];

  // Process each data row
  for (var i = 0; i < rows.length; i++) {
    var row = rows[i];

    // Skip completely empty rows
    if (row.join('').trim() === '') continue;

    // Build a field map: { ColumnHeader: CellValue }
    var fieldMap = {};
    for (var j = 0; j < headers.length; j++) {
      fieldMap[headers[j]] = row[j] !== undefined ? String(row[j]) : '';
    }

    try {
      // Use InvoiceNumber + ClientName as the output file name
      var fileName = 'Invoice ' + (fieldMap['InvoiceNumber'] || (i + 1)) +
                     ' - ' + (fieldMap['ClientName'] || 'Unknown');

      // Copy template and open it
      var newFile = templateFile.makeCopy(fileName, outputFolder);
      var doc = DocumentApp.openById(newFile.getId());
      var body = doc.getBody();

      // Replace all placeholders
      for (var field in fieldMap) {
        if (field !== '') {
          body.replaceText('{{' + field + '}}', fieldMap[field]);
        }
      }

      doc.saveAndClose();
      generatedCount++;

    } catch (e) {
      errors.push('Row ' + (i + 2) + ': ' + e.message);
    }
  }

  // Log a summary
  var summary = 'Generated ' + generatedCount + ' document(s).';
  if (errors.length > 0) {
    summary += ' Errors on ' + errors.length + ' row(s): ' + errors.join(' | ');
  }
  Logger.log(summary);

  SpreadsheetApp.getUi().alert(summary);
}

This script reads the entire sheet data in a single call (getDataRange().getValues()), which is far more efficient than reading one row at a time. It then loops through every data row, builds a field map from the headers, makes a copy of the template, and replaces all placeholders before saving.

The try...catch block around the document operations means one failed row does not stop the entire batch. Errors are collected and reported in the final summary alert.

Step 5: Generate PDFs and Save to Drive Folders

Google Docs stored in Drive is fine for internal documents. For invoices, offer letters, and formal client reports, PDF is the expected format — it preserves formatting, cannot be accidentally edited, and opens consistently on any device.

Converting a Google Doc to PDF in Apps Script uses the getAs() method:

function generatePDFsFromSheet() {
  var TEMPLATE_ID = 'YOUR_TEMPLATE_DOC_ID';
  var OUTPUT_FOLDER_ID = 'YOUR_OUTPUT_FOLDER_ID';
  var SHEET_NAME = 'Sheet1';

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(SHEET_NAME);
  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  var rows = data.slice(1);

  var templateFile = DriveApp.getFileById(TEMPLATE_ID);
  var outputFolder = DriveApp.getFolderById(OUTPUT_FOLDER_ID);

  var generatedCount = 0;
  var errors = [];

  for (var i = 0; i < rows.length; i++) {
    var row = rows[i];
    if (row.join('').trim() === '') continue;

    var fieldMap = {};
    for (var j = 0; j < headers.length; j++) {
      fieldMap[headers[j]] = row[j] !== undefined ? String(row[j]) : '';
    }

    try {
      var fileName = (fieldMap['InvoiceNumber'] || 'DOC-' + (i + 1)) +
                     ' - ' + (fieldMap['ClientName'] || 'Unknown');

      // Create a temporary Google Doc copy to perform the merge
      var tempFile = templateFile.makeCopy('TEMP_' + fileName, outputFolder);
      var doc = DocumentApp.openById(tempFile.getId());
      var body = doc.getBody();

      for (var field in fieldMap) {
        if (field !== '') {
          body.replaceText('{{' + field + '}}', fieldMap[field]);
        }
      }

      doc.saveAndClose();

      // Export the completed doc as a PDF blob
      var pdfBlob = DriveApp.getFileById(tempFile.getId())
                             .getAs('application/pdf');
      pdfBlob.setName(fileName + '.pdf');

      // Save the PDF to the output folder
      outputFolder.createFile(pdfBlob);

      // Delete the temporary Google Doc — keep the output folder clean
      tempFile.setTrashed(true);

      generatedCount++;

    } catch (e) {
      errors.push('Row ' + (i + 2) + ': ' + e.message);
    }
  }

  var summary = 'Generated ' + generatedCount + ' PDF(s) in the output folder.';
  if (errors.length > 0) {
    summary += ' Errors: ' + errors.join(' | ');
  }
  Logger.log(summary);
  SpreadsheetApp.getUi().alert(summary);
}

This pattern — copy the template, merge the data, export to PDF, delete the temporary Doc — keeps your output folder containing only PDFs and leaves no intermediate working files behind.

If you want to save PDFs into subfolders (for example, one subfolder per client or per month), replace outputFolder.createFile(pdfBlob) with logic that finds or creates the appropriate subfolder first, then creates the file inside it. The same approach used in the Drive organisation scripts applies here: use getFoldersByName() to check whether the subfolder exists and createFolder() to create it if not.

Practical Examples

Invoice Generator

A consultancy or trade business can maintain a simple Sheets invoice register. Each row is a new invoice with columns for invoice number, date, due date, client name, address, ABN, service description, amount, and payment terms. A Google Docs template formatted with the company's letterhead and layout contains the matching placeholders. Running generatePDFsFromSheet() produces a PDF invoice for every row in the register, named consistently and filed in a Drive folder ready to email.

For a more complete workflow, extend the script with MailApp.sendEmail() after PDF creation to email each invoice directly to the client address in a dedicated column.

Offer Letter Generator

HR teams recruiting at volume can store candidate details in Sheets — candidate name, role title, start date, salary, reporting manager, location. A formal offer letter template in Google Docs contains placeholders for each field. Running the batch generation script produces a personalised, correctly formatted offer letter for every candidate in the sheet. Output as Google Docs (rather than PDFs) when the letters will go through a review step before being sent; output as PDFs when they are ready for direct distribution.

This approach removes the risk of copy-paste errors that can create embarrassing discrepancies between offer letters — a wrong salary figure or incorrect start date copied from the wrong row.

Client Report Generator

A digital marketing agency or consulting firm that sends regular performance reports can template the report structure in Google Docs with sections for executive summary, metrics, recommendations, and next steps. Variable fields — client name, reporting period, key metrics, account manager name — come from Sheets. Running the generator produces a personalised report document for every client in the sheet, which can then be reviewed, updated with commentary, and exported to PDF for delivery.

For reports that require charts, note that body.replaceText() handles text substitution only. Embedding dynamic charts requires the Slides API or additional scripting. For text-heavy reports, the approach works well as-is.

Affiliate & Partner Programs

The document generation workflow in this guide runs entirely within Google Workspace — no third-party subscriptions, no per-document fees, no external dependencies. Apps Script, Google Docs, Google Sheets, and Google Drive are all included with every Google Workspace plan.

• Google Workspace: https://referworkspace.app.goo.gl/ — Google's official referral link for Google Workspace plans. Australian pricing starts from AUD $10.80 per user per month on Business Starter, with Business Standard (AUD $19.20/user/month) adding Shared Drives that make shared template libraries and output folder management significantly cleaner for teams. All plans include full Apps Script access, Google Docs, Sheets, and Drive with pooled storage.

If your organisation is generating significant document volumes — hundreds of invoices per month, offer letters across multiple hiring rounds, reports for a large client portfolio — Business Standard's pooled storage and Shared Drive support provide a much more sustainable infrastructure than individual My Drive accounts. The Shared Drive can hold both the template library and the output folders, with access managed at the drive level rather than file by file.

Conclusion

The pattern in this guide — a Google Docs template with placeholders, a Google Sheet as the data source, Apps Script to merge and file the output — is one of the most practical automations available in the Google Workspace ecosystem. It requires no third-party tools, no subscriptions beyond your existing Workspace plan, and no advanced programming knowledge beyond what this tutorial covers.

The three scripts in this guide handle the full range of output requirements: individual Google Docs for documents that need review before finalisation, PDFs for documents that are ready to send, and batch processing for any volume of records.

Start with one use case — the one where manual document production is currently costing the most time. Set up the template, structure the Sheets data, run the script with ten rows as a test, and confirm the output is correct before scaling to your full data set. Once the workflow is running, it is repeatable indefinitely with zero incremental effort per document.

Every hour your team currently spends copying and pasting data into document templates is time the script can recover. Over a year, for a business generating regular formal documents, the cumulative saving is substantial.

Need help with your Google Workspace setup? Contact our team for a free consultation.