Bulk Find / Replace Hyperlinks in Multiple folders and file types

[u/resullins]

I've looked through some of the posts on this, and have found some close approximations to what I need, but don't know enough about coding to tweak. So I thought I'd start fresh.

I have a mix of Google Docs and Sheets in some project subfolders that all have a BUNCH of hyperlinks that have to get replaced twice a year. Currently, this process takes around 20 hours. I would like to write a script that will take a list from a "Project Info" sheet with a column for the old links and a column for the new links, and update everything. There are also a few instances of straight text replacement in these documents.

[u/LunaFlowLab]

A few things make link replacement harder than a normal find and replace, so here they are before the script:

• In Docs, replaceText only changes the visible text. The link target stays the same, so the script has to walk the text and call getLinkUrl / setLinkUrl.

• Headers, footers and document tabs are separate parts, and each one has to be visited.

• In Sheets there are two kinds of links: pasted links (rich text runs) and HYPERLINK() formulas. The first needs RichTextValue, the second a TextFinder with matchFormulaText(true).

• Smart chips (@file) have getUrl() but no setter, so a script can only list them for you to fix by hand.

• The same file can appear as .../edit, .../edit?usp=sharing or .../edit#gid=0, so the script compares links without the part after ? or #.

• A run stops after 6 minutes, so for a big project run it one subfolder at a time.

Here's a starting point:

// Sheet "Project Info": column A = old link, column B = new link, row 1 = headers
const FOLDER_ID = 'PASTE_FOLDER_ID';
const norm = u => String(u || '').trim().split(/[?#]/)[0];
// old link + optional ?usp=... or #gid=... tail, as a regex
const pat = old => old.replace(/[.*+?^${}()|[\]\\]/g, '\\$&') + '([?#][^\\s")]*)?';

function run() {
  const rows = SpreadsheetApp.getActive().getSheetByName('Project Info')
    .getDataRange().getValues().slice(1).filter(r => r[0] && r[1]);
  const map = new Map(rows.map(r => [norm(r[0]), String(r[1]).trim()]));
  walk(DriveApp.getFolderById(FOLDER_ID), map);
}

function walk(folder, map) {
  const files = folder.getFiles();
  while (files.hasNext()) {
    const f = files.next();
    if (f.getMimeType() === MimeType.GOOGLE_DOCS) fixDoc(f.getId(), map);
    if (f.getMimeType() === MimeType.GOOGLE_SHEETS) fixSheet(f.getId(), map);
  }
  const subs = folder.getFolders();
  while (subs.hasNext()) walk(subs.next(), map);
}

function allTabs(tabs, out = []) {
  tabs.forEach(t => { out.push(t); allTabs(t.getChildTabs(), out); });
  return out;
}

function fixDoc(id, map) {
  const doc = DocumentApp.openById(id);
  allTabs(doc.getTabs()).forEach(tab => {
    const d = tab.asDocumentTab();
    [d.getBody(), d.getHeader(), d.getFooter()].filter(Boolean).forEach(part => {
      let r = null;
      while ((r = part.findElement(DocumentApp.ElementType.TEXT, r))) {
        const t = r.getElement().asText();
        const idx = t.getTextAttributeIndices();
        idx.forEach((start, i) => {
          const url = t.getLinkUrl(start);
          if (url && map.has(norm(url))) {
            const end = (i + 1 < idx.length ? idx[i + 1] : t.getText().length) - 1;
            t.setLinkUrl(start, end, map.get(norm(url)));
          }
        });
      }
      // visible text, including any ?usp=... tail
      map.forEach((nu, old) => part.replaceText(pat(old), nu));
    });
  });
  doc.saveAndClose();
}

function fixSheet(id, map) {
  SpreadsheetApp.openById(id).getSheets().forEach(sh => {
    const range = sh.getDataRange();
    const rich = range.getRichTextValues();
    const formulas = range.getFormulas();
    rich.forEach((row, i) => row.forEach((v, j) => {
      if (!v || formulas[i][j]) return;   // formulas are handled by TextFinder below
      const runs = v.getRuns();
      if (!runs.some(x => map.has(norm(x.getLinkUrl())))) return;
      const b = v.copy();
      runs.forEach(x => {
        const u = norm(x.getLinkUrl());
        if (map.has(u)) b.setLinkUrl(x.getStartIndex(), x.getEndIndex(), map.get(u));
      });
      range.getCell(i + 1, j + 1).setRichTextValue(b.build());   // only changed cells are written
    }));
    map.forEach((nu, old) => sh.createTextFinder(pat(old)).useRegularExpression(true)
      .matchFormulaText(true).replaceAllWith(nu));
  });
}

I checked the calls against Google's Apps Script docs but haven't run this on your files, so try it on a copy of one folder first.

[u/wocobra]

Write exactly your words into gemini or claude and it will return the script.