The Challenge of Manual Document Creation
In many enterprise environments, administrative workflows rely heavily on generating standardized documents. Whether creating employee offer letters, generating monthly invoices, or drafting legal NDAs, employees often resort to copying an existing Google Doc, manually finding and replacing names, dates, and figures, and saving the result as a PDF.
This manual process is incredibly inefficient and highly prone to human error. A missed replacement tag can result in a legal document containing the wrong client’s name.
To solve this, organizations utilizing Google Workspace can leverage Google Apps Script to programmatically generate perfectly formatted Google Docs from a centralized data source (such as a Google Sheet) and a standardized template.
Step 1: Preparing the Google Doc Template
The first step in automation is creating the template document. Open a new Google Doc and draft the standardized text. For every piece of variable data that needs to be dynamically inserted, use placeholder tags wrapped in double curly braces (e.g., {{CLIENT_NAME}}).
Example Template:
Date: {{CURRENT_DATE}}
Prepared For: {{CLIENT_NAME}}
This agreement ensures that {{CLIENT_NAME}} agrees to the standard terms outlined in section 4.2 regarding the service fee of {{FEE_AMOUNT}}.
Once the template is designed, copy its unique Document ID from the URL (the long alphanumeric string between /d/ and /edit).
Step 2: Setting up the Data Source in Google Sheets
Next, create a Google Sheet to act as the database. Create columns that correspond to the placeholder tags in your template:
- Column A: Client Name
- Column B: Fee Amount
- Column C: Status
Fill the first row with a test client (e.g., “Acme Corp”, “$5,000”, “Pending”).
Step 3: Writing the Google Apps Script
From within the Google Sheet, navigate to Extensions > Apps Script. This opens the cloud-based JavaScript IDE. Delete the default code and paste the following script.
This script reads the data from the active sheet, creates a copy of the template document, and uses the replaceText method to inject the dynamic data.
function generateDocuments() {
// Define Template and Target Folder IDs
const templateId = "YOUR_TEMPLATE_DOC_ID";
const targetFolderId = "YOUR_DESTINATION_FOLDER_ID";
// Connect to the Spreadsheet
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const data = sheet.getDataRange().getValues();
// Connect to Google Drive
const template = DriveApp.getFileById(templateId);
const targetFolder = DriveApp.getFolderById(targetFolderId);
// Iterate through the rows (skipping the header row)
for (let i = 1; i < data.length; i++) {
const clientName = data[i][0];
const feeAmount = data[i][1];
const status = data[i][2];
// Only process rows marked as "Pending"
if (status === "Pending") {
// Create a copy of the template
const newFileName = "Agreement - " + clientName;
const newFile = template.makeCopy(newFileName, targetFolder);
const doc = DocumentApp.openById(newFile.getId());
const body = doc.getBody();
// Replace the placeholder tags
const formattedDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MMMM dd, yyyy");
body.replaceText("{{CURRENT_DATE}}", formattedDate);
body.replaceText("{{CLIENT_NAME}}", clientName);
body.replaceText("{{FEE_AMOUNT}}", feeAmount);
// Save and close the document
doc.saveAndClose();
// Mark as generated in the spreadsheet
sheet.getRange(i + 1, 3).setValue("Generated");
}
}
}Step 4: Executing and Authorizing the Script
Click the Run button in the Apps Script editor. Because this script interacts with your Google Drive, Google Docs, and Google Sheets, you will be prompted to authorize the script with your Google Workspace account.
Once authorized, the script will loop through the spreadsheet. For every row marked “Pending”, it will duplicate the template, instantly replace all the {{TAGS}} with the row’s data, save the document in the target folder, and update the spreadsheet status to “Generated”.
Step 5: Exporting as PDF (Advanced)
Often, generated documents need to be locked down as PDFs before being emailed. You can append the following logic to the script immediately after doc.saveAndClose(); to convert the newly generated Google Doc into a PDF:
const pdfBlob = doc.getAs('application/pdf');
targetFolder.createFile(pdfBlob).setName(newFileName + ".pdf");
// Optionally delete the temporary Google Doc
newFile.setTrashed(true);Conclusion
By harnessing the power of Google Apps Script, organizations can completely eliminate the drudgery and risk of manual document creation. Integrating Google Sheets with Google Docs allows for massive scalability, enabling administrative teams to generate hundreds of perfectly formatted, personalized contracts or invoices with a single click.