1

I have a script running that I found online that allows me to add a hyperlink to a range so it will take the value of cell, add it to a hyperlink and make the text show as the value itself. I currently target a specific range for this ("B14:B97")

What I want to do is instead of targeting a specific range, I want it to target Column B, Row 14 all the way down to the last row with data.

My current script:

function createUrl() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActive();
  var range = sheet.getRange('B14:B97');
range.activate();

var selection = sheet.getSelection();

var currentCell = selection.getCurrentCell();

var activeRange = selection.getActiveRange();


    var numRows = range.getNumRows();

    for (var i = 1; i <= numRows; i++) {
      var value = range.getCell(i,1).getValue();
      range.getCell(i,1).setValue('=HYPERLINK("https://jira.com/browse/'+value+'","'+value+'")');
    }

  };
pnuts
  • 58,317
  • 11
  • 87
  • 139
Tommeh
  • 11
  • 1
  • I apologize for my poor English skill. I cannot understand about ``I want it to target Column B, Row 14 all the way down to the last row with data.``. Can I ask you about it? – Tanaike Feb 20 '19 at 22:34
  • Instead of `getRange('B14:B97');` , use `getRange(row, column, numRows);` [(documentation reference)](https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow-column-numrows). row=14, column=2 (=B14), numRows = Use this ingenious little routine [Determining the last row in a single column](https://stackoverflow.com/a/17637159/1330560) (obviously substitute for column B). So you would have something like `var Bvals = ss.getRange("B14:B").getValues();`, `var Blast = Bvals.filter(String).length;`, 'var range = sheet.getRange(14,2,BLast);`. – Tedinoz Feb 28 '19 at 11:41

0 Answers0