Fill a Google Doc template from a Sheet row

1 · Prepare

Put these pieces in place

• Create a Google Doc to use as the template and type the placeholders {{Name}}, {{Date}}, and {{Amount}} in the body of its first tab, where those values belong.
• Create a Sheet tab named Letters with headers in row 1: Name in column A, Date in column B, Amount in column C, and a Link header in column D. Put every letter on row 2 or below.
• Use an account that can open the template and create files in your Google Drive, because the script makes one copy per row.

2 · Review before running

What the script can access and change

3 · Copy

Paste this into the Apps Script editor

Create or open a script file, replace the example values marked above, then save the project.

function fillDocTemplateFromSheetRows() {
  const templateId = 'PASTE_YOUR_TEMPLATE_DOC_ID_HERE';
  if (templateId === 'PASTE_YOUR_TEMPLATE_DOC_ID_HERE') {
    throw new Error('Put the ID of your template Doc in templateId before running this function.');
  }

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const timeZone = spreadsheet.getSpreadsheetTimeZone();
  const sheet = spreadsheet.getSheetByName('Letters');
  if (!sheet) throw new Error('Add a Sheet tab named Letters before running this function.');

  const firstDataRow = 2;
  const lastRow = sheet.getLastRow();
  if (lastRow < firstDataRow) return;

  const template = DriveApp.getFileById(templateId);
  const rows = sheet.getRange(firstDataRow, 1, lastRow - firstDataRow + 1, 4).getValues();

  rows.forEach((row, index) => {
    const [name, date, amount, documentUrl] = row;
    if (!name || documentUrl) return;

    const copy = template.makeCopy(String(name) + ' letter');
    const document = DocumentApp.openById(copy.getId());
    const body = document.getBody();
    body.replaceText('\\{\\{Name\\}\\}', String(name));
    body.replaceText('\\{\\{Date\\}\\}', formatCellAsDateText(date, timeZone));
    body.replaceText('\\{\\{Amount\\}\\}', String(amount));
    document.saveAndClose();

    sheet.getRange(firstDataRow + index, 4).setValue(copy.getUrl());
  });
}

/**
 * @param {unknown} value
 * @param {string} timeZone
 */
function formatCellAsDateText(value, timeZone) {
  if (value instanceof Date) {
    return Utilities.formatDate(value, timeZone, 'MMMM d, yyyy');
  }
  return String(value);
}

4 · Run safely

Follow these steps in order

1. Copy the template ID from the address bar

   Open your template Doc. Its address looks like https://docs.google.com/document/d/1AbCdEfGhIjKlMnOpQrStUvWxYz/edit. The long code between /d/ and /edit is the document ID. Paste it into templateId in the code.

2. Start with one test row

   On the Letters tab, use row 1 for the Name, Date, Amount, and Link headers. In row 2, fill in your own name, a date, and a small amount, and leave the Link cell empty. Do not add the rest of the rows until this one works.

3. Paste both functions and run

   Open Extensions, choose Apps Script, paste the code, and save. Choose fillDocTemplateFromSheetRows and run it. Approve spreadsheet, Docs, and Drive access. Use one operator, wait for completion, and do not edit or reorder rows during a run.

4. Open the link in column D

   That link is the copy made for your test row. Check that every placeholder was replaced. If you still see {{Name}} in the copy, the text in the Doc and the pattern in the code do not match.

5. Add the remaining rows

   Fill in the rest of the rows after checking the test copy. A later run skips rows with a visible link in column D. If a run failed, inspect Drive and reconcile unlinked rows before retrying; a copy can exist even when its link is missing.

5 · Confirm

How to tell it worked

Each finished row shows a link in column D, and opening that link shows a copy of the template with the name, date, and amount from that row in place of the placeholders.

If the result is different

Troubleshooting

The script stops with "No item with the given ID could be found, or you do not have permission to access it."

The value in templateId is wrong, or the file belongs to someone who has not shared it with you. Copy the code from the address bar again, taking only the part between /d/ and /edit, and open the Doc with the same account that runs the script.

The copy still shows {{Name}}.

The Doc text and the search pattern must match exactly, including capital letters. Check that the Doc has two braces on each side and no extra space inside them, and that each brace in the code still has its backslash.

A placeholder in another tab, a header, or a footer did not change.

This example only replaces text in the first tab body. Keep the template placeholders there. Tables inside that body are covered.

A row got a second copy or a run failed without a link.

Stop before another run. Search Drive for the expected copy name and inspect its creation time and all replacements. Record its link in column D only when you confirm it is complete; repair a partial copy by hand. Resolve multiple matches or an uncertain result before retrying. An empty link, an overlapping run, or a failure after copying can lead to another copy.