Build a digital-asset request and approval flow

When the Northwind design team gets asked for a logo pack, a brand font, or a product render, the request usually arrives as a Slack message that scrolls away by lunchtime. Nobody can see what is pending, who approved what, or whether the requester ever actually got the file. The result is duplicated work and the same asset sent five times.

This script turns a plain Google Sheet into the delivery half of an approval flow. A teammate logs the request, an approver sets the status to approved and drops in a Drive file ID, and the script does the rest: it grants the requester view access to the file and emails them the link. The request log becomes the single source of truth for every asset that has gone out.

What you’ll need

• An Asset requests Google Sheet with a header row and these columns: requester (an email address), assetType, status, approver, fileId, and delivered. The delivered column starts empty — the script stamps it.
• A Drive folder of approved, ready-to-share assets. Approvers paste the ID of a file from this folder into the fileId column.
• Edit access to every file referenced in fileId, so the script can add a viewer.

The script

// The request log. Each row is one asset request and its current status.
const REQUESTS = '1abcAssetRequestsId';

// The Drive folder holding approved assets — referenced for documentation;
// approvers paste individual file IDs into the sheet.
const APPROVED_ROOT = '1abcApprovedAssetsId';

// Statuses the script acts on.
const STATUS_APPROVED = 'approved';

/**
 * Scans the request log for approved-but-undelivered rows, grants the
 * requester view access to the asset, emails them the link, and stamps
 * the row as delivered.
 */
function deliverApprovedAssets() {
  const sheet = SpreadsheetApp.openById(REQUESTS).getSheets()[0];
  const values = sheet.getDataRange().getValues();

  // Bail out early if the sheet is empty apart from the header.
  if (values.length < 2) {
    Logger.log('No requests in the log — nothing to deliver.');
    return;
  }

  // Split off the header and build a name -> column-index map, so the
  // code never depends on a fixed column order.
  const [h, ...rows] = values;
  const col = Object.fromEntries(h.map((k, i) => [k, i]));

  let delivered = 0;

  rows.forEach((r, i) => {
    // Skip anything that is not approved, has no file, or already went out.
    if (r[col.status] !== STATUS_APPROVED || !r[col.fileId] || r[col.delivered]) {
      return;
    }

    // Grant the requester view access and email them the link.
    const file = DriveApp.getFileById(r[col.fileId]);
    file.addViewer(r[col.requester]);
    GmailApp.sendEmail(
      r[col.requester],
      `Asset ready: ${file.getName()}`,
      `Approved by ${r[col.approver]}. Link: ${file.getUrl()}`
    );

    // Stamp the in-memory copy so the row is not delivered twice.
    values[i + 1][col.delivered] = new Date();
    delivered++;
  });

  // Write the whole grid back in one call so the stamps persist.
  sheet.getDataRange().setValues(values);
  Logger.log(`Delivered ${delivered} approved asset(s).`);
}

How it works

1. deliverApprovedAssets opens the request log and reads every row in one getDataRange call.
2. If the sheet holds only a header, it logs a message and stops — no Drive or Gmail calls are made.
3. It separates the header row and builds a col lookup, mapping each column name to its index. The rest of the script reads cells by name, so reordering columns in the sheet never breaks it.
4. For each row it skips anything that is not approved, has no fileId, or already carries a delivered timestamp — that last check is what makes the script safe to run repeatedly.
5. For a row that qualifies, it opens the file, adds the requester as a viewer, and sends them an email with the approver’s name and the file URL.
6. It writes the current date into the delivered column of the in-memory grid.
7. After the loop it writes the entire grid back to the sheet in a single call, persisting every new timestamp at once.

Example run

Before the run, the Asset requests sheet looks like this:

requester	assetType	status	approver	fileId	delivered
[email protected]	Logo pack	approved	[email protected]	1xLogoPack
[email protected]	Product render	pending
[email protected]	Brand font	approved	[email protected]	1xFontFile	2025-11-02

After the run, only Sam’s row changes — Dana’s is still pending and Ravi’s was already delivered:

requester	status	delivered
[email protected]	approved	2025-11-09 14:22

Sam also receives an email: “Asset ready: Logo pack — Approved by [email protected]. Link: …” and now has view access to the file.

Trigger it

Run this on a schedule so approved requests go out without anyone remembering to press a button:

1. In the Apps Script editor open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose deliverApprovedAssets, an event source of Time-driven, and an Hour timer every hour (or every 15 minutes for a faster turnaround).
4. Save and approve the authorisation prompt.

Approvers just edit the sheet — set status to approved and paste a fileId — and the next run delivers it.

Watch out for

• The requester column must hold a real email address. A typo means the addViewer call shares the file with the wrong person and the email bounces.
• addViewer shares the file with whatever account you give it. If the asset is confidential, double-check the address before approving.
• Gmail has a daily send quota (around 100 messages on consumer accounts, 1,500 on Workspace). A large backlog of approvals could hit it — spread delivery across runs if you expect a flood.
• The script trusts the status value. There is no second sign-off; whoever can edit the sheet can approve a request. Restrict edit access accordingly.
• getDataRange().setValues() rewrites the whole sheet. If someone edits a row while the script is mid-run, their change can be overwritten — keep runs short and frequent rather than rare and large.