Download and file documents from a link sheet

Northwind’s team collects file links all over the place — a supplier emails a spec PDF, a freelancer drops an asset in a shared note, a brief links to a reference image. Those links rot. The sensible fix is to pull a copy into Drive, but doing it by hand for a long list is exactly the kind of chore nobody gets to.

This script reads a Links sheet where each row names a URL, a destination folder, and a filename, downloads each file, and saves it into Drive. It marks every row as it goes, so a re-run only picks up the rows that have not been downloaded yet — and a row that fails gets the error written next to it instead of silently disappearing.

What you’ll need

• A Google Sheet with a Links tab and these four columns in row 1: url, targetFolderId, filename, downloaded. Leave the downloaded column blank — the script fills it in.
• The destination folders must already exist in Drive; put each folder’s ID in the targetFolderId column.
• Nothing else — the script writes its own status back to the sheet.

The script

// The Sheet that holds the list of links to download.
const LINKS_SHEET_ID = '1abcLinksId';

/**
 * Reads the Links sheet, downloads every not-yet-fetched URL into its
 * target Drive folder, and writes a timestamp or error back per row.
 */
function downloadFromLinks() {
  const sheet = SpreadsheetApp.openById(LINKS_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet and split off the header row.
  const values = sheet.getDataRange().getValues();
  const [header, ...rows] = values;

  // 2. Bail out if there are no data rows to process.
  if (!rows.length) {
    Logger.log('Links sheet has no rows — nothing to download.');
    return;
  }

  // 3. Map column names to indexes so the order of columns does not matter.
  const col = Object.fromEntries(header.map((name, i) => [name, i]));

  // 4. Walk each row, skipping any that already has a "downloaded" value.
  rows.forEach((r, i) => {
    if (r[col.downloaded]) return;

    try {
      // 5. Fetch the file, name the blob, and save it into the target folder.
      const blob = UrlFetchApp.fetch(r[col.url])
        .getBlob()
        .setName(r[col.filename]);
      DriveApp.getFolderById(r[col.targetFolderId]).createFile(blob);

      // 6. Record success as a timestamp (i + 1 skips the header row).
      values[i + 1][col.downloaded] = new Date();
    } catch (e) {
      // 7. Record the failure inline so it is visible and re-runnable.
      values[i + 1][col.downloaded] = `error: ${e.message}`;
    }
  });

  // 8. Write the updated grid back in one call.
  sheet.getDataRange().setValues(values);
  Logger.log('Finished processing ' + rows.length + ' link row(s).');
}

How it works

1. downloadFromLinks opens the Links sheet and reads the entire data range in one go, then peels the header row off the front.
2. If there are no data rows it logs a message and stops, so an empty sheet never triggers a needless write.
3. It builds a col lookup from header name to column index, so you can reorder or insert columns without breaking the script.
4. For each row it checks the downloaded column first — any row that already has a timestamp or an error is skipped, which makes re-runs safe.
5. For a fresh row it fetches the URL with UrlFetchApp, turns the response into a blob, and names that blob from the filename column.
6. It creates the file in the folder named by targetFolderId and writes the current date into the downloaded cell.
7. If anything throws — a dead URL, a bad folder ID — it writes error: plus the message into the same cell, so the row stays visible and can be retried.
8. After the loop it writes the whole grid back in a single setValues call.

Example run

Before the run, the Links sheet looks like this:

url	targetFolderId	filename	downloaded
https://example.com/spec.pdf	1abcBriefsFolder	acme-spec.pdf
https://example.com/logo.png	1abcAssetsFolder	partner-logo.png
https://broken.example/x.zip	1abcAssetsFolder	archive.zip

After the run, the files are in Drive and the sheet records what happened:

url	targetFolderId	filename	downloaded
https://example.com/spec.pdf	1abcBriefsFolder	acme-spec.pdf	2025-10-24 09:15:02
https://example.com/logo.png	1abcAssetsFolder	partner-logo.png	2025-10-24 09:15:04
https://broken.example/x.zip	1abcAssetsFolder	archive.zip	error: DNS error

Run it again and only the failed row is retried — the two timestamped rows are skipped.

Trigger it

Most teams run this on a schedule so links added during the day get filed overnight:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add a trigger for downloadFromLinks, time-driven, on a daily timer.

You can also run it by hand straight after pasting a batch of new links.

Watch out for

• UrlFetchApp follows redirects but cannot get past login pages — a link that needs a session will download an HTML error page named like your file. Spot these by their size in Drive.
• The filename you set is what you get; UrlFetchApp does not infer an extension. Include the correct extension in the filename column.
• There is a daily UrlFetchApp quota. A very long list may stop partway — because finished rows are marked, the next run simply continues where it left off.
• The script does not de-duplicate. The same URL in two rows downloads twice into Drive as two separate files.
• A row with a malformed targetFolderId fails per-row and is logged inline — it does not stop the rest of the batch.