0

I have a google sheet with each cell containing multiple Hyperlinks.

My goal is to:

  1. extract url from say cell A1 (or the active cell/range)
  2. keep existing cell content and append the extracted URL to the existing content (in the same cell)
  3. Be able to do this for a range instead of each cell individually

Currently, I have this code that works for one cell with only one Hyperlink but gives an error

TypeError: Cannot read property '0' of undefined

when a cell contains multiple hyperlinks.

function URL() {
  var spreadsheet =SpreadsheetApp.getActive(); 
  var sheet = spreadsheet.getActiveSheet();
  var id = spreadsheet.getId();
  var range = sheet.getActiveRange();
  var linkArray = Sheets.Spreadsheets.get(id, {ranges: sheet.getName() + "!" + 
  range.getA1Notation(), fields: "sheets/data/rowData/values/hyperlink"});
  var link = linkArray.sheets[0].data[0].rowData[0].values[0].hyperlink;
  var cellInE = sheet.getRange(range.getRow(), 5);
  var cellEcontent = sheet.getRange(range.getRow(), 5).getValues()
  var appnded = cellEcontent + String.fromCharCode(10) + link;
  cellInE.setValue(appnded);
}

I am new to google app scripts and coding in general. Any help would be appreciated.

muaz masood
  • 1
  • 1
  • Welcome to [so]. Please add some sample data and clearly state if the "hyperlinks" are clickable or not. Also add the complete textual error message (the file name and line number are missing). – Rubén Aug 10 '21 at 00:42

1 Answers1

0

You need to correctly iterate the linkArray, I found an example and made a few brief modifications. It worked for me.

function myFunction() {
  var spreadsheetId = "1olBuawXzIxriAhWM2_e42LzjWM2fh_1aWitZkJMErWk";
  let rangeToSet = SpreadsheetApp.openById(spreadsheetId).getSheetByName("Sheet1");
  var res = Sheets.Spreadsheets.get(spreadsheetId, { ranges: "Sheet1!A1:C3", fields: "sheets/data/rowData/values/hyperlink" });
  var sheets = res.sheets;
  for (var i = 0; i < sheets.length; i++) {
    var data = sheets[i].data;
    for (var j = 0; j < data.length; j++) {
      var rowData = data[j].rowData;
      for (var k = 0; k < rowData.length; k++) {
        var values = rowData[k].values;
        for (var l = 0; l < values.length; l++) {
          Logger.log(values[l].hyperlink) // You can see the URL here.
          // k is Row number
          //l is column number
          rangeToSet.getRange(k + 1, l + 1).setValue(values[l].hyperlink);
        }
      }
    }
  }
}

Before:

enter image description here

Now:

enter image description here

Julian Benavides
  • 320
  • 1
  • 11
  • Thank you so much for your response! when I run your code it says: "Error: TypeError: Cannot read property 'length' of undefined" Also, when I click debug and go through the variables, I cant seem to find "rowData" in there. I see "getRowData". I am looking in res/sheets[0]/data[0].... – muaz masood Aug 10 '21 at 20:55
  • Review the next: 1. in ranges: "Sheet1!A1:C3" review if it has the realy name Sheet and range. 2. enable advanced Sheets Api from "Services" options of Google apps script editor – Julian Benavides Aug 10 '21 at 22:38