iterate through selected cells in Google Sheets

Question

I want to add a text to last column of all selected rows:

var sh = ss.getActiveSheet(); 
  lCol = sh.getLastColumn()
  var selected_cells=ss.getActiveRangeList()
  var rgs=selected_cells.getRanges();
  for (var i = 0; i < rgs.length; i++) {
    var range=rgs[i];
    var lRow=range.getRow();
    var cell = sh.getRange(lRow,lCol+1,1,1);
    var firstname=sh.getRange(lRow, 2).getValue();
    var lastname=sh.getRange(lRow, 3).getValue();
    var email=sh.getRange(lRow, 8).getValue();
    var link="http://192.168.80.1/d/?email="+email+"&firstname="+firstname+"&lastname="+lastname;
    cell.setFormula("=HYPERLINK(\""+link+"\",\"click\")");
  }

But it justs adds to first row. What am I missing?

Answer 1

You want to put =HYPERLINK() to the last column of the selected rows. If my understanding is correct, how about this modification? I was interested in your question. So I thought of about your issue, because I thought that I would like to study from your question.

Modification points :

• For example, when you select the cells of "A1", "B2" and "C3", the range list has 3 elements in an array. Those are "A1", "B2" and "C3". When you select the cells of "A1", "A2" and "A3", the range list has 1 element in an array. Those are "A1:A3".
  • From your script, I thought that this may be the main cause for your issue. So I would like to propose the following flow and the modified script.

Flow :

1. At first, parse the retrieved range list.
   • By this, the list is separated by the continuous cells and the individual cell.
2. Using the parsed list, import the values you want.

When you use this modified script, please run main().

Modified script :

function getFormula(e) {
  var firstname = e[1];
  var lastname = e[2];
  var email = e[7];
  var link = "http://192.168.80.1/d/?email=" + email + "&firstname=" + firstname + "&lastname=" + lastname;
  return "=HYPERLINK(\"" + link + "\",\"click\")";
}

// Please run this function, when you use this modified script.
function main() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var lCol = sh.getLastColumn();
  var selected_cells = ss.getActiveRangeList();
  var rgs = selected_cells.getRanges();
  for (var i = 0; i < rgs.length; i++) {
    var numRows = rgs[i].getNumRows();
    if (numRows > 1) {
      var values = sh.getRange(rgs[i].getRow(), 1, numRows, lCol).getValues();
      var formulas = values.map(function(e) {return [getFormula(e)]});
      sh.getRange(rgs[i].getRow(), lCol + 1, numRows, 1).setFormulas(formulas);
    } else {
      var e = sh.getRange(rgs[i].getRow(), 1, 1, lCol).getValues()[0];
      sh.getRange(rgs[i].getRow(), lCol + 1, 1, 1).setFormula(getFormula(e));
    }
  }
}

Note :

• From your script, in the modified script, it supposes that firstname, lastname and email are in column B, column C and column H, respectively. If you want to change this, please modify it.

If I misunderstand your question, I'm sorry.