Auto-assemble onboarding packets per new hire

Every new Northwind hire gets the same bundle of documents — a welcome letter, a role-specific checklist, the kit request form — each one personalised with the hire’s name, role, and start date. Done by hand, it is twenty minutes of copy, rename, find-and-replace per person, and it is easy to send a designer the engineering checklist by mistake.

This automation reads the Hires roster, and for every hire not yet processed it creates a personal Drive folder, copies the right template documents into it, and fills in the placeholders. It picks templates by role, so a designer gets the design pack and everyone gets the shared all documents. The roster records the folder link so the same hire is never built twice.

What you’ll need

• A Hires sheet with four columns and a header row: name, role, startDate, and packageBuilt. Leave packageBuilt empty — the script fills it with the folder link once a packet is built.
• A templates/ folder in Drive containing one sub-folder per role (for example Designer, Engineer), plus an all sub-folder for documents every hire receives. Each sub-folder holds the template Docs for that role.
• Template Docs that use the placeholders {{name}}, {{role}}, and {{start}} wherever personalised text should appear.
• An output root folder in Drive where the per-hire folders will be created.

The script

// The roster sheet listing new hires.
const HIRES = '1abcHiresId';

// Folder of per-role template sub-folders, plus an "all" sub-folder.
const TEMPLATES_FOLDER = '1abcOnboardingTemplatesId';

// Folder under which each hire's personal folder is created.
const OUTPUT_ROOT = '1abcOnboardingRootId';

// Name of the sub-folder whose documents every hire receives.
const SHARED_FOLDER = 'all';

/**
 * Builds an onboarding packet for every hire on the roster that does not
 * already have one. Copies the role-specific and shared template Docs into
 * a personal folder, fills in the placeholders, and records the folder URL.
 */
function buildOnboardingPacks() {
  const sheet = SpreadsheetApp.openById(HIRES).getSheets()[0];
  const values = sheet.getDataRange().getValues();
  const [h, ...rows] = values;
  const col = Object.fromEntries(h.map((k, i) => [k, i]));

  if (!rows.length) {
    Logger.log('No hires on the roster — nothing to do.');
    return;
  }

  rows.forEach((r, i) => {
    // 1. Skip hires whose packet has already been built.
    if (r[col.packageBuilt]) return;

    // 2. Create a personal folder for this hire.
    const personFolder = DriveApp.getFolderById(OUTPUT_ROOT)
      .createFolder(r[col.name]);

    // 3. Walk the template sub-folders, taking the role match and "all".
    const templates = DriveApp.getFolderById(TEMPLATES_FOLDER).getFolders();
    while (templates.hasNext()) {
      const t = templates.next();
      if (t.getName() !== r[col.role] && t.getName() !== SHARED_FOLDER) {
        continue;
      }

      // 4. Copy each template file into the hire's folder.
      const files = t.getFiles();
      while (files.hasNext()) {
        const f = files.next();
        const cleanName = f.getName().replace(/template/i, '').trim();
        const copy = f.makeCopy(cleanName + ' — ' + r[col.name], personFolder);

        // 5. If the copy is a Doc, swap the placeholders for real values.
        if (copy.getMimeType() === MimeType.GOOGLE_DOCS) {
          const doc = DocumentApp.openById(copy.getId());
          const body = doc.getBody();
          body.replaceText('{{name}}', r[col.name]);
          body.replaceText('{{role}}', r[col.role]);
          body.replaceText('{{start}}',
            Utilities.formatDate(r[col.startDate], 'GMT', 'd MMM yyyy'));
          doc.saveAndClose();
        }
      }
    }

    // 6. Record the folder link back into the roster row.
    values[i + 1][col.packageBuilt] = personFolder.getUrl();
  });

  // 7. Write the updated roster back in one call.
  sheet.getDataRange().setValues(values);
}

How it works

1. The script reads the whole Hires sheet, splits off the header, and builds a col lookup so columns can be addressed by name.
2. For each hire row it checks packageBuilt. A non-empty value means the packet already exists, so the row is skipped — that is what makes the script safe to run repeatedly.
3. It creates a Drive folder named after the hire under OUTPUT_ROOT.
4. It iterates the template sub-folders, keeping only the one matching the hire’s role and the shared all folder.
5. For each template file it makes a copy into the hire’s folder, stripping the word “template” from the name and appending the hire’s name.
6. When the copy is a Google Doc, it opens the body and replaces {{name}}, {{role}}, and {{start}} with the hire’s details — non-Doc files (PDFs, forms) are copied as-is.
7. It writes the new folder URL into the row’s packageBuilt cell, then commits the whole sheet back in a single setValues call.

Example run

The Hires sheet before a run:

name	role	startDate	packageBuilt
Priya Shah	Designer	2025-09-22
Tom Reilly	Engineer	2025-09-22	https://drive.google.com/…

Only Priya is processed — Tom already has a link. The script creates a Priya Shah folder containing the Designer pack plus the all pack, with every {{name}} reading “Priya Shah”, every {{role}} reading “Designer”, and every {{start}} reading “22 Sep 2025”. Afterwards her row’s packageBuilt cell holds the new folder’s URL.

Run it

This is an on-demand job — run it whenever new hires are added to the roster:

1. In the Apps Script editor, select buildOnboardingPacks and click Run.
2. Approve the authorisation prompt the first time.
3. Open the Hires sheet and click the new packageBuilt links to check the folders.

To run it without opening the editor, add a time-driven trigger (a daily Day timer works well) so new hires are picked up automatically each morning.

Watch out for

• packageBuilt is the only thing stopping duplicate packets. If you clear that cell, the next run builds a second folder for that hire.
• Role names must match folder names exactly, including capitalisation. A hire with role designer will not match a Designer sub-folder, and they will receive only the all documents.
• Placeholder replacement only touches the document body. Text in headers, footers, or tables outside the body is left untouched — call getHeader() and getFooter() too if your templates use them.
• startDate must be a real date in the sheet for formatDate to work. A text date will throw an error mid-run.
• Copying many files calls Drive and Docs repeatedly. A large roster can be slow and may approach the six-minute execution limit — process in batches if you onboard dozens of people at once.