Fill Google Doc Template Using Sheet Apps Script

Below is my messy “createNewGoogleDocs” function which is a bit of Google Apps Script that automates the process of generating personalized documents based on document templates using data stored in a spreadsheet.

This script is a powerful way to streamline document generation, especially for repetitive contracts or agreements where the structure is fixed, but the content varies per customer or record.

How it Works:

Step 1: Choose the Right Template

The function first selects the appropriate Google Docs template based on the sheet name passed in (name). For example:

• If the sheet is called “eMotorBike Form B”, it uses Template A.
• If it’s “eCycle Form B”, it uses Template B.

This makes it easy to handle different types of contracts or forms based on product type.

Step 2: Set Up Folders and Sheet

It then:

• Accesses the parent template folder (for logging or organization).
• Identifies the destination folder where new documents will be saved.
• Opens the relevant sheet (based on name) from a central spreadsheet.

Step 3: Loop Through the Sheet Rows

The script loops through each row in the sheet:

• It skips the header row and ignores rows where a contract has already been generated (column M is filled).
• For valid rows, it pulls out the necessary information (like name, address, registration number, etc.).

Step 4: Fill the Template

It makes a copy of the template and replaces placeholder tags (like {{Name}}, {{Address}}, {{Vehicle Registration}}) with actual values from the spreadsheet row.

Special logic handles:

• Different hire charges for eMotorBike vs eCycle (weekly or monthly).
• Adding VIN or frame size based on vehicle type.
• Formatting payment and start dates.
• Adding checkmarks (✓) for communication preferences like Post, Phone, Email and SMS.

Step 5: Insert Signature Image

If a signature URL is provided, it fetches the image and replaces the {{Signature}} tag with the image. The image is resized if it’s too large.

Step 6: Save and Mark as Complete

Finally, it saves and closes the document, and marks the sheet with a timestamp or a status (“Contract Generated”) to avoid regenerating the same document again.

The Code:

function createNewGoogleDocs(name) {
  Logger.log("Get Template");

  /**
   * IF NAME IS 'eMotorBike Form B' THEN TEMPLATE A, IF NOT THEN TEMPLATE B
   */
  const googleDocTemplate = name === 'eMotorBike Form B' ? DriveApp.getFileById('[Google Doc Template ID 1]') : (name === 'eCycle Form B' ? DriveApp.getFileById('[Google Doc Template ID] 2') : '<OTHER OPTIONS HERE>');
  Logger.log("Got Template: " + googleDocTemplate.getName());

  var parentFolder = DriveApp.getFolderById("[Google Doc Template Parent Folder ID]");
  var n1 = parentFolder.getName();
  Logger.log("Got Parent: " + n1);

  Logger.log("Get Folder");
  const destinationFolder = DriveApp.getFolderById('[Google Doc Destination Folder ID]');
  Logger.log("Got Folder: " + destinationFolder.getName());
  Logger.log("Get Sheet");
  const sheet = SpreadsheetApp.openById("[Spreadsheet ID]").getSheetByName(name);
  Logger.log("Got Sheet: " + sheet.getName());
  Logger.log("Fill Template");

  const rows = sheet.getDataRange().getValues();

  // Creates an array to record contract generation
  const out = [];

  rows.forEach(function(row, index) {
    if (index > 0 && row[12] == "") {

    var d = new Date(row[0]);
    Logger.log("Date (Unformatted): " + d);
    var formattedDate = Utilities.formatDate(d, Session.getScriptTimeZone(), "yyyy-MM-dd");
    Logger.log("Date (Formatted): " + formattedDate);

    const copy = googleDocTemplate.makeCopy(formattedDate + ' Hire Agreement ' +row[9]+' - '+row[8]+' - '+row[1], destinationFolder);
    const doc = DocumentApp.openById(copy.getId());
    const body = doc.getBody();

    body.replaceText('{{Name}}', row[1]);
    body.replaceText('{{Address}}', row[4]);
    body.replaceText('{{Vehicle Registration}}', row[8]);
    body.replaceText('{{Payment Type}}', row[9]);
    body.replaceText('{{Vehicle Make}}', row[6]);
    body.replaceText('{{Vehicle Model}}', row[7]);

    if(name === 'eMotorBike Form B') {
      //Set Charge Amount
      if(row[9] == "Weekly") {
        body.replaceText('{{Hire Charge}}', "£30.00");
        body.replaceText('{{Total Rental}}', "£30.00");
      } else if (row[9] == "Monthly") {
        body.replaceText('{{Hire Charge}}', "£130.00");
        body.replaceText('{{Total Rental}}', "£130.00");
      }

      //Get VIN Number!
      var vin = findVINorDate(row[8], "vin")
      body.replaceText('{{VIN}}', vin);
    } else if(name === 'eCycle Form B') {
      //Set Charge Amount
      if(row[9] == "Weekly") {
        body.replaceText('{{Hire Charge}}', "£21.00");
        body.replaceText('{{Total Rental}}', "£21.00");
      } else if (row[9] == "Monthly") {
        body.replaceText('{{Hire Charge}}', "£90.00");
        body.replaceText('{{Total Rental}}', "£90.00");
      }

      //Get Frame Size!
      var size = identifySize(row[7]);
      body.replaceText('{{Frame Size}}', size);
    }

    var paymentDate = Utilities.formatDate(new Date(row[10]), Session.getScriptTimeZone(), "dd/MM/yyyy");
    body.replaceText('{{Payment Date}}', paymentDate);
    body.replaceText('{{Email}}', row[2]);
    body.replaceText('{{Phone}}', row[3]);
    var startDate = Utilities.formatDate(new Date(row[5]), Session.getScriptTimeZone(), "dd/MM/yyyy");
    body.replaceText('{{Contract Start Date}}', startDate);

    if(row[13].includes('Post')) {
      body.replaceText('{{T1}}', '\u2714');
    } else {
      body.replaceText('{{T1}}', '');
    }
    if(row[13].includes('Phone')) {
      body.replaceText('{{T2}}', '\u2714');
    } else {
      body.replaceText('{{T2}}', '');
    }
    if(row[13].includes('Email')) {
      body.replaceText('{{T3}}', '\u2714');
    } else {
      body.replaceText('{{T3}}', '');
    }
    if(row[13].includes('SMS')) {
      body.replaceText('{{T4}}', '\u2714');
    } else {
      body.replaceText('{{T4}}', '');
    }
    
    
    /**
     * Signature Image Attempt!
     */
    
    // Find the text {{Signature}}
    var searchResult = body.findText('{{Signature}}');

    if (searchResult) {
      var foundText = searchResult.getElement().asText();
      var startOffset = searchResult.getStartOffset();
      var endOffset = searchResult.getEndOffsetInclusive();
      Logger.log("Get Image URL: " + row[11]);
      // Get the image from the URL
      var imageBlob = UrlFetchApp.fetch(row[11]).getBlob();
      Logger.log("Got Image URL");
      // Insert the image at the found position
      var oImg = body.findText("{{Signature}}").getElement().getParent().asParagraph();
      oImg.clear();
      oImg = oImg.appendInlineImage(imageBlob);
      if(oImg.getWidth() > 650) {
        oImg.setWidth(650);
      }
      if(oImg.getHeight() > 75) {
        oImg.setHeight(75);
      }

      // Split the text at the found position
      foundText.deleteText(startOffset, endOffset);
    }

    doc.saveAndClose();
    out.push([new Date()]);
    } else if(row[12] != "Contract Generated") {
      out.push([row[12]]);
    }
  });
  //
  sheet.getRange(2, 13, out.length).setValues(out);
}

Hopefully posting my work in my blog like this will make someone’s google search a bit quicker.

Leave a like or a comment if it helped!