Google Sheets - Automated Text to Hyperlinks

Turn a Column of Text and a Column of Links, into a New Column of Hyperlinks

Have you ever,

Scraped some data into columns and the links were in one column, but the text in another?

Using the formulas in sheets can be annoying to create text that you can actually click and follow the link.

This is an Apps Script, script. You need to open the Apps Script from the Sheet and add the Sheet service.

Then create a tab called “Processed”, or use your own. You simply replace the tab name in the script.

Ensure you add your sheet ID to the const sheetID variable.

/**
 * This script takes values from columns A and B in the "Processed" sheet
 * and writes a clickable hyperlink into column D. Each cell in D2:D will
 * show the name from A2:A as a clickable link to the corresponding URL in B2:B.
 *
 * Example:
 *   A2 = "Google"
 *   B2 = "https://google.com"
 *   D2 will become =HYPERLINK("https://google.com", "Google")
 */

function linkNamesToURLs() {
  // Step 1: Set your target Google Sheet ID and Sheet/tab name
  const sheetId = "YOUR_SHEET_ID_HERE";      // Replace with your actual Sheet ID
  const sheetName = "Processed";             // Tab name where the data is stored

  // Step 2: Open the Google Sheet and get the specific tab
  const ss = SpreadsheetApp.openById(sheetId);
  const sheet = ss.getSheetByName(sheetName);

  // Step 3: Read the values from columns A and B (starting from row 2 to the last row)
  const valuesA = sheet.getRange("A2:A" + sheet.getLastRow()).getValues(); // Names
  const valuesB = sheet.getRange("B2:B" + sheet.getLastRow()).getValues(); // URLs

  // Step 4: Prepare an array of =HYPERLINK formulas to insert into column D
  const output = valuesA.map((row, i) => {
    const name = row[0];          // Value from column A
    const url = valuesB[i][0];    // Corresponding URL from column B

    // If both name and URL exist, create the formula; else leave the cell empty
    if (name && url) {
      return [`=HYPERLINK("${url}", "${name}")`];
    } else {
      return [""];
    }
  });

  // Step 5: Write the formulas into column D (starting from row 2)
  sheet.getRange(2, 4, output.length, 1).setFormulas(output); // Column 4 = D
}

This works great, and hopefully it will save you some time.