google-apps-scriptgoogle-sheets

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.

    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);
}

    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.

    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);
}

    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);
}