How to Extract URLs from HYPERLINK in Google Sheets

I have some words containing url/hyperlink, Example RTU SBCET

I want to extract url from the word RTU and SBCET in the google sheets. But, I am not able to find any inbuilt function to extract urls from word. I used some custom functions obtained from internet such as GetURL, extractHyperlinks etc. to get urls but the problem is that, while running code an error is occured 'Error Attempted to execute GETLINKmenu, but it was deleted.' and the code is not executing. I used various codes from the internet but the problem is same. so kindly help me to extract urls. Example of code which i have used.

const extractHyperlinksInSheet = () => {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = SpreadsheetApp.getActiveSheet();

  const hyperlinks = [];

  const spreadsheedId = ss.getId();
  const sheetName = sheet.getName();

  const getRange = (row, col) => {
    const address = sheet.getRange(row + 1, col + 1).getA1Notation();
    return `${sheetName}!${address}`;
  };

  const getHyperlink = (rowIndex, colIndex) => {
    const { sheets } = Sheets.Spreadsheets.get(spreadsheedId, {
      ranges: [getRange(rowIndex, colIndex)],
      fields: 'sheets(data(rowData(values(formattedValue,hyperlink))))',
    });
    const [{ formattedValue, hyperlink }] = sheets[0].data[0].rowData[0].values;
    hyperlinks.push({ rowIndex, colIndex, formattedValue, hyperlink });
  };

  sheet
    .getDataRange()
    .getFormulas()
    .forEach((dataRow, rowIndex) => {
      dataRow.forEach((cellValue, colIndex) => {
        if (/=HYPERLINK/i.test(cellValue)) {
          getHyperlink(rowIndex, colIndex);
        }
      });
    });

  Logger.log(hyperlinks);
};

Solution

I believe your goal is as follows.

You want to retrieve the hyperlinks set to the texts of RTU and SBCET using Google Apps Script.

I thought that in this case, it might not be required to use Sheets API. I thought that even RichText could be used to achieve your goal. When this is reflected in a sample script, it becomes as follows.

Sample script:

const extractHyperlinksInSheet = () => {
  const sheet = SpreadsheetApp.getActiveSheet();
  const hyperlinks = sheet.getDataRange().getRichTextValues().reduce((ar, r) => {
    r.forEach(c =>
      c.getRuns().forEach(e => {
        if (["RTU", "SBCET"].includes(e.getText().trim().toUpperCase())) {
          ar.push(e.getLinkUrl());
        }
      })
    );
    return ar;
  }, []);
  console.log(hyperlinks);
}

When this script is run, the hyperlinks set to the texts of RTU and SBCET in the active sheet are retrieved.

References:

Added 1:

From the following reply,

If the word RTU is contained in cell M1, and SBCET is contained in cell M2 or any other word in cell M3 then my need is to retrieve the URL in cell N1 and N2 or somewhere else.

I understood your expected result as follows.

You want to retrieve the URLs from the words except for the words RTU and SBCET.

In this case, how about the following sample script?

Sample script:

const extractHyperlinksInSheet = () => {
  const excludeWords = ["RTU", "SBCET"];
  const sheet = SpreadsheetApp.getActiveSheet();
  const hyperlinks = sheet.getDataRange().getRichTextValues().reduce((ar, r) => {
    r.forEach(c =>
      c.getRuns().forEach(e => {
        const url = e.getLinkUrl();
        const v = e.getText().trim().toUpperCase();
        if (url && !excludeWords.includes(v)) {
          ar.push(url);
        }
      })
    );
    return ar;
  }, []);
  console.log(hyperlinks);
}

When this script is run, the URLs from the words except for the words RTU and SBCET are retrieved.

Added 2:

From your following reply,

under consolidate tab, I have to retrieve url/hyperlink in the cell F3:F

I understand that you want to retrieve the hyperlinks from cells "B5:B" and put the retrieved hyperlinks to cells "F5:F". In this case, how about the following sample script?

Sample script:

const extractHyperlinksInSheet = () => {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Consolidate");
  const hyperlinks = sheet.getRange("B3:B" + sheet.getLastRow()).getRichTextValues().map(([c]) => [c.getLinkUrl() || null]);
  sheet.getRange(3, 6, hyperlinks.length).setValues(hyperlinks);
}

If you want to retrieve the hyperlinks from "B5:B", please modify from "B3:B" to "B5:B" and from getRange(3, 6, hyperlinks.length)togetRange(5, 6, hyperlinks.length)`.
This sample script is for your provide Spreadsheet. If you change the Spreadsheet, this script might not be able to be used. Please be careful about this.