Build a shared-folder onboarding kit

A new Northwind hire’s first morning should not be spent chasing folder access. But that is what happens when granting Drive permissions is a manual job: a designer needs the brand assets folder, an account manager needs the client files, and whoever onboards them has to remember which folders go with which role — and inevitably misses one.

This script makes day-one access automatic. It reads a Hires sheet of new joiners and a Role access sheet that maps each role to a set of folders. For every hire who has not been granted access yet, it adds them as an editor to the right folders and stamps the row so they are never granted twice.

What you’ll need

• A Hires sheet with a header row and three columns: email (the hire’s Google account), role (must match a role in the access sheet), and granted (left blank — the script fills it).
• A Role access sheet with a header row and two columns: role and folderIds (a comma-separated list of Drive folder IDs for that role).
• Edit rights on every folder listed, so the script can add new editors.

The script

// The spreadsheet of new hires awaiting access.
const HIRES_SHEET_ID = '1abcHiresId';

// The spreadsheet mapping each role to its folder IDs.
const ROLE_ACCESS_SHEET_ID = '1abcRoleAccessId';

/**
 * Grants each new hire editor access to the folders their role needs,
 * then stamps the row so it is never processed again.
 */
function grantNewHireAccess() {
  // 1. Build a role -> folderIds lookup from the Role access sheet.
  const access = Object.fromEntries(
    readSheet(ROLE_ACCESS_SHEET_ID).map((r) => [r.role, r.folderIds])
  );

  // 2. Read the Hires sheet, keeping the full grid so we can write back.
  const sheet = SpreadsheetApp.openById(HIRES_SHEET_ID).getSheets()[0];
  const values = sheet.getDataRange().getValues();
  const [h, ...rows] = values;
  if (!rows.length) {
    Logger.log('No hires to process.');
    return;
  }
  const col = Object.fromEntries(h.map((k, i) => [k, i]));

  // 3. For each ungranted hire, add them to every folder for their role.
  let granted = 0;
  rows.forEach((r, i) => {
    if (r[col.granted]) return; // Already done — skip.

    const folders = String(access[r[col.role]] || '')
      .split(',')
      .map((s) => s.trim())
      .filter(Boolean);

    for (const id of folders) {
      DriveApp.getFolderById(id).addEditor(r[col.email]);
    }

    // Stamp the row in the in-memory grid so we can write it back.
    values[i + 1][col.granted] = new Date();
    granted++;
  });

  // 4. Write the whole grid back so the granted stamps persist.
  sheet.getDataRange().setValues(values);
  Logger.log('Granted access to ' + granted + ' new hire(s).');
}

/**
 * Reads a sheet's first tab and returns each row as an object keyed
 * by the header row.
 */
function readSheet(id) {
  const [h, ...rows] = SpreadsheetApp.openById(id).getSheets()[0]
    .getDataRange().getValues();
  return rows.map((r) => Object.fromEntries(h.map((k, i) => [k, r[i]])));
}

How it works

1. grantNewHireAccess calls readSheet on the Role access sheet and turns it into an access lookup — given a role name, it returns that role’s comma-separated folder IDs.
2. It reads the Hires sheet, keeping the entire values grid (header included) because it will write the grid back later. It builds a col lookup so columns are read by name.
3. For each hire row it checks the granted column. If it already holds a value, the row is skipped — this is what stops a hire being granted twice.
4. For an ungranted hire it splits the role’s folder IDs into a list and calls DriveApp.getFolderById(id).addEditor(email) for each one.
5. It writes a timestamp into that row’s granted cell in the in-memory grid, then, after all rows are processed, writes the whole grid back in one setValues call so the stamps persist for the next run.

Example run

The Role access sheet maps roles to folders:

role	folderIds
Designer	1brandFolder, 1assetsFolder
Account manager	1clientFolder

The Hires sheet has one new joiner waiting:

email	role	granted
[email protected]	Designer

After a run, Sam is an editor on both the brand and assets folders, and the row is stamped:

email	role	granted
[email protected]	Designer	29/11/2025 14:02

On the next run, that stamp means Sam’s row is skipped entirely.

Trigger it

Run the onboarding kit automatically so new rows are picked up without anyone remembering to:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Add a trigger for grantNewHireAccess, time-driven, on an hourly timer.
3. Save. Add a hire to the sheet and they get access within the hour.

Watch out for

• A role in the Hires sheet that does not match the Role access sheet resolves to an empty folder list — the hire is stamped granted but gets nothing. Keep the role names identical across both sheets.
• addEditor needs the hire to have a Google account at that address. A personal address or a typo will fail.
• The granted stamp is the only record of what was done. If someone clears that cell, the hire is re-granted on the next run — harmless, but it adds duplicate editor entries.
• An invalid folder ID throws and halts the run before later hires are processed. Wrap the addEditor loop in a try/catch if one bad ID should not block the rest.
• The script only ever adds access. It does not revoke folders when a hire changes role or leaves — that is a separate offboarding job.
• setValues rewrites the whole Hires grid, so avoid formulas in that sheet that you would not want overwritten with static values.