0

I am trying to write a function that takes a Google Sheet ID as an input and parses the spreadsheet, extracting URL links out of the cells when encountered. I wrote the following function based off of this answer and this blog post.

function getLinksFromSheet(sheetId){
var ss = SpreadsheetApp.openById(sheetId); //general case
var sheets = ss.getSheets();

sheets.forEach(sheet => {
  var rangeData = sheet.getDataRange();
  var lastColumn = rangeData.getLastColumn();
  var lastRow = rangeData.getLastRow();
  var searchRange = sheet.getRange(1,1, lastRow, lastColumn);
  var rangeValues = searchRange.getRichTextValues();


  for (var i = 0; i < lastRow; i++){
    for (var j = 0 ; j < lastColumn; j++){
      Logger.log(rangeValues[i][j].getLinkUrl())
    }
  }
});

The function works well for extracting links from cells where the only text contents of the cell are the link (for example http://google.com). If, however, the cell contains other text that isn't a part of the link (for example Link: http://google.com), then the function returns null. Does anybody know of a way to modify my funtion so that it will still return the link from cells with other text besides a link in them?

Gabriel Tero
  • 81
  • 7
  • Is the data in the cells structured in any specific way? Consider providing a copy of the spreadsheet showing this. – Iamblichus Aug 23 '21 at 07:26

1 Answers1

1
  for (var i = 0; i < lastRow; i++){
    for (var j = 0 ; j < lastColumn; j++){
      const runs = rangeValues[i][j].getRuns();
      for (const v of runs) {
        Logger.log(v.getLinkUrl())
      }
    }
  }

Reference:

getRuns()

idfurw
  • 5,727
  • 2
  • 5
  • 18