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?