Google Docs AutomationDifficulty: Advanced⚡ On Demand

Generate Custom Google Docs & Contracts from Spreadsheet Data

Reads rows of client data from Google Sheets, duplicates a Google Doc template, replaces merge tags like {{ClientName}} and {{Fee}}, and outputs ready-to-sign documents.

Required OAuth Scopes: https://www.googleapis.com/auth/documents • https://www.googleapis.com/auth/drive • https://www.googleapis.com/auth/spreadsheets
Code.gs (Apps Script V8 Runtime)
function generateContractsFromTemplate() {
  const templateId = 'PASTE_DOC_TEMPLATE_ID_HERE';
  const folderId = 'PASTE_OUTPUT_FOLDER_ID_HERE';
  const outputFolder = DriveApp.getFolderById(folderId);
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Clients');
  const data = sheet.getDataRange().getValues();
  
  // Assumes Row 1 has headers: [ClientName, Company, Fee, Status]
  for (let i = 1; i < data.length; i++) {
    const clientName = data[i][0];
    const company = data[i][1];
    const fee = data[i][2];
    
    const copy = DriveApp.getFileById(templateId).makeCopy('Contract - ' + company, outputFolder);
    const doc = DocumentApp.openById(copy.getId());
    const body = doc.getBody();
    
    body.replaceText('{{ClientName}}', clientName);
    body.replaceText('{{Company}}', company);
    body.replaceText('{{Fee}}', '$' + fee);
    doc.saveAndClose();
    
    sheet.getRange(i + 1, 4).setValue('Generated: ' + copy.getUrl());
  }
}

Line-by-Line Code Explanation

Line 2-4: Resolves template file ID and target Google Drive storage folder.
Line 5-6: Reads the entire table of client records in a single batch read.
Line 14-15: Makes a clean copy of the Google Doc template inside the designated folder.
Line 17-21: Replaces template placeholder tags with actual customer parameters.
Line 23: Writes the created document URL back into Google Sheets.

Step-by-Step Setup & Deployment

  1. Create a Google Doc template containing placeholders like `{{ClientName}}` and `{{Company}}`.
  2. Paste template ID and output folder ID into Lines 2 and 3.
  3. Run the script to batch-generate contracts.

Best Practices for Quota & Rate Limit Management

When deploying this automation in Google Workspace, adhere to these operational principles:

  • Batch Reads & Writes: Never execute getValue() or setValue() inside loops. Read ranges into memory in 1 call, process arrays in V8 memory, and commit changes with a single setValues() call.
  • Execution Timeouts: Apps Script terminates scripts exceeding 6 minutes. For multi-thousand row datasets, implement batch pagination with resume timestamps stored in PropertiesService.
  • OAuth Permissions: Scope your manifest (appsscript.json) narrowly to prevent security prompts from alarming external team members.

Frequently Asked Questions

Is Google Apps Script completely free to use?

Yes. Google Apps Script is built into all free Google accounts and Google Workspace editions. There are no server fees or subscription costs for running scripts within daily quotas.

Can I run this script on an automated schedule without keeping my computer on?

Yes. Time-driven triggers execute on Google's cloud infrastructure. Your script will run reliably even when your computer is closed or offline.

More Google Docs Automation Snippets