Google Sheets: automatically link some specific patterns.

AUTOMATIC_LINKS.md

The following Google Apps Script script automatically adds hyperlinks to text following a given pattern in Google Sheets cells.

The pattern provided below will automatically add hyperlinks to strings matching [SheetName!Range]. For instance, [Foo!B4] will point to #gid=<foo_gid>&range=B4.

/** @type [RegExp, (match: RegExpExecArray, range: SpreadsheetApp.Range) => string | undefined][] */
const patterns = [
  [ /\[(\w+!)?([A-Z]+[0-9]+(?::[A-Z]+[0-9]+)?)\]/g,
    (match, range) => {
      let gid = range.getSheet().getSheetId();

      if (match[1] !== undefined) {
        const sheetName = match[1].substring(0, match[1].length - 1);
        const sheet = range.getSheet().getParent().getSheetByName(sheetName);

        if (sheet === null)
          return;

        gid = sheet.getSheetId();
      }

      return `#gid=${gid}&range=${match[2]}`;
    },
  ],
];

function onEdit(/** @type {{ readonly range: SpreadsheetApp.Range }} */ e) {
  const richTextValue = e.range.getRichTextValue(),
        text = richTextValue.getText();

  // By default, `builder` is undefined, which avoids an unecessary call to
  // the Spreadsheet service when there is no link to set.

  /** @type SpreadsheetApp.RichTextValueBuilder */
  let builder;

  for (const [pattern, getLinkUrl] of patterns) {
    /** @type RegExpExecArray */
    let match = null;

    while (match = pattern.exec(text)) {
      const linkUrl = getLinkUrl(match, e.range);

      if (linkUrl) {
        if (builder === undefined)
          builder = richTextValue.copy();

        builder.setLinkUrl(match.index, match.index + match[0].length, linkUrl);
      }
    }
  }

  // If the builder is defined (i.e. a link was set), we update the RichTextValue.
  if (builder !== undefined) {
    e.range.setRichTextValue(builder.build());
  }
}

Here is another pattern that can be used to link to users, assuming a sheet named "Users", whose A column is the identifier or name of each user, and the list of users starts at 2 (since the row 1 is for the header).

[ /(?:\b|^)(?:User\/|@)(\S+)/g,  // Matches strings like @민주 or User/Greg
  (match, range) => {
    const userId = match[1];

    const usersSheet = range.getSheet().getParent().getSheetByName("Users"),
          namesRange = usersSheet.getRange(2, 1, usersSheet.getLastRow()),
          textFinder = namesRange.createTextFinder(userId).matchEntireCell(true);

    const firstOccurence = textFinder.findNext();

    if (firstOccurence !== null) {
      return `#gid=${usersSheet.getSheetId()}&range=${firstOccurence.getA1Notation()}`;
    }
  },
]

The pattern above can be easily adapted for other things. For instance, one could look up invoices using the pattern /(?:\b|^)Invoice\/(\S+)/g and getting the sheet "Invoices" instead of "Users" above.

const pattern   = /(?:\b|^)Invoice\/(\S+)/g,  // The pattern to match against.
      sheetName = "Invoices",                 // The name of the sheets to link to.
      startRow  = 2,                          // The first row containing values (starting at 1).
      column    = 1;                          // The column containing the identifiers / keys (starting at 1).

const patterns = [
  // snip...
  [ pattern,
    (match, range) => {
      const id = match[1];

      const sheet = range.getSheet().getParent().getSheetByName(sheetName),
            matchRange = sheet.getRange(startRow, column, sheet.getLastRow() - startRow + 2),
            textFinder = matchRange.createTextFinder(id).matchEntireCell(true);

      const firstOccurence = textFinder.findNext();

      if (firstOccurence !== null) {
        return `#gid=${sheet.getSheetId()}&range=${firstOccurence.getA1Notation()}`;
      }
    },
  ],
];

// snip...