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

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

   Choose Extensions, then Apps Script. Paste the code over the starter code and save. Choose fillDocTemplateFromSheetRows in the function dropdown above the code, then click Run. Approve spreadsheet, Docs, and Drive access when Google asks. Only one person should run this at a time. Wait for the run to finish, and do not edit or sort the rows while it runs.

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 that show a link in column D. A copy can exist even when its link is missing. If a run failed, search Drive for copies it made and paste each link into its row before you run again.

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 run stops with "No item with the given ID could be found, or you do not have permission to access it."

Copy the ID from the address bar again, taking only the part between /d/ and /edit, and check that the account running the script can open the Doc. Either templateId is wrong or the file has not been shared with you.

The copy still shows {{Name}}.

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

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

Move the placeholder into the body of the first tab. The script replaces text only there, including inside tables in that body.

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

Do not run the script again yet. Search Drive for the name from column A followed by letter. Open each match and check its creation time and that every placeholder was replaced. Paste the link into column D only when the copy is complete. Fix a partial copy by hand. If several files match, or you cannot tell whether a copy is complete, decide what to keep before you run again. Another copy is made when a link cell is empty, when two runs happen at the same time, or when a run fails after copying.