Auto-Generate Google Docs Reports from Sheets Data Using Apps Script

Generating formatted reports from spreadsheet data is a repetitive task that Apps Script handles extremely well. Whether you need one report or hundreds, the same script can do it all.

The approach

Create a template Google Doc with placeholders like {{name}}, {{amount}}, {{date}}.
The script reads each row from a Sheet.
For each row, it copies the template, replaces placeholders with real values, and saves the new Doc.

Step 1 — Create your template Doc

In Google Docs, create a document that looks like your final report. Use double-curly-brace placeholders for dynamic content:

Monthly Expense Report

Submitted by: {{name}}
Department: {{department}}
Date: {{date}}
Total Amount: ${{amount}}

Notes: {{notes}}

Copy the document ID from the URL (the long string between /d/ and /edit).

Step 2 — Read Sheet data and generate reports

function generateReports() {
  const TEMPLATE_ID = 'YOUR_TEMPLATE_DOC_ID';
  const FOLDER_ID = 'YOUR_OUTPUT_FOLDER_ID'; // Google Drive folder for output docs

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const headers = data[0]; // First row = column headers
  const rows = data.slice(1); // Remaining rows = data

  const outputFolder = DriveApp.getFolderById(FOLDER_ID);

  rows.forEach((row, i) => {
    // Build a map of placeholder -> value
    const values = {};
    headers.forEach((header, j) => {
      values[`{{${header}}}`] = row[j] || '';
    });

    // Copy the template
    const templateFile = DriveApp.getFileById(TEMPLATE_ID);
    const docName = `Report - ${values['{{name}}'] || 'Row ' + (i + 2)}`;
    const newFile = templateFile.makeCopy(docName, outputFolder);

    // Open the copy and replace placeholders
    const doc = DocumentApp.openById(newFile.getId());
    const body = doc.getBody();

    Object.entries(values).forEach(([placeholder, value]) => {
      body.replaceText(placeholder, String(value));
    });

    doc.saveAndClose();
    Logger.log(`Created: ${docName}`);
  });

  Logger.log('All reports generated.');
}

Step 3 — Optionally convert to PDF and email it

function generateAndEmailReport(rowIndex) {
  const TEMPLATE_ID = 'YOUR_TEMPLATE_DOC_ID';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const row = sheet.getRange(rowIndex, 1, 1, sheet.getLastColumn()).getValues()[0];

  const values = {};
  headers.forEach((h, i) => { values[`{{${h}}}`] = row[i] || ''; });

  const email = values['{{email}}'];
  const name = values['{{name}}'];

  const templateFile = DriveApp.getFileById(TEMPLATE_ID);
  const newFile = templateFile.makeCopy(`Report - ${name}`);
  const doc = DocumentApp.openById(newFile.getId());
  const body = doc.getBody();

  Object.entries(values).forEach(([k, v]) => body.replaceText(k, String(v)));
  doc.saveAndClose();

  // Export as PDF blob
  const pdfBlob = newFile.getAs('application/pdf').setName(`Report - ${name}.pdf`);

  // Email it
  GmailApp.sendEmail(email, `Your Report - ${name}`, 'Please find your report attached.', {
    attachments: [pdfBlob]
  });

  // Clean up the temporary Doc
  DriveApp.getFileById(newFile.getId()).setTrashed(true);
  Logger.log(`Report emailed to ${email}`);
}

Tips

body.replaceText() uses regular expressions internally, so escape special regex characters in your placeholders if needed.
For complex layouts (tables, images), use the DocumentApp API to target specific paragraphs or table cells directly.
If you're generating a large number of documents, add execution time checks to avoid hitting the 6-minute limit (see our article on handling execution time limits).
Store the template Doc ID and folder ID in PropertiesService so you don't need to edit the code when they change.