custom spreadsheet function - get a1 notation for the range?

Question

I'm trying to create a custom function for a google sheet that will find the rightmost string in a 1d range of cells, then return a header (in a specified row).

Here's where I'm stuck. I can get the string for that cell with the following code:

function FarRightHeader(range, rownumber) {
  var cells = range[0].length;//gets the number of cells
  for (var i = 0; i < cells; i++) { //loop through the cells in the range
      var j = cells - 1 - i; // j will start at the end so the loop can work from left to right
      if (range[0][j] != "") { //if the cell contains something
        break; //jump out of the loop
      }
  }

var activeCell = SpreadsheetApp.getActiveRange().getA1Notation();
var activeColumn = activeCell.charAt(0);
var FarRightCell = "Hi, I'm___" + range[0][j] + "___ and I'm in column " + activeColumn;

return FarRightCell;
}

here's the glitch - the activeCell variable is taking the cell from which the custom function is called, not the far right populated cell in the range. I understand why this is happening, but don't know how to get the column I want.

To me it appears that the function is treating the range as simply the values in the cells divorced from what cells they actually are in the spreadsheet.

Is there a way to get information about the range within the spreadsheet that the function takes as a parameter?

Thanks in advance for any help or leads you can give me!

Kriggs Kriggs · Accepted Answer · 2015-07-30T13:05:30

I see no glitch, you're imagining your loop as searching the cells, while you're just searching an Array that you got from the cells values. But as your code sugests you don't need to retrieve the column like, you already have it, saved in the j, you just need to convert it to a letter, here's a code I fetched:

function colName(n) {
    var ordA = 'a'.charCodeAt(0);
    var ordZ = 'z'.charCodeAt(0);
    var len = ordZ - ordA + 1;

    var s = "";
    while(n >= 0) {
        s = String.fromCharCode(n % len + ordA) + s;
        n = Math.floor(n / len) - 1;
    }
    return s;
}

Also here's a suggested better for loop:

var column; // Initialize before so if doesn't become private scope
    for (column = ( range[0].length - 1 ); column > 0; column--) {
        if (range[0][j] != "")
            break;
}

Note: This requires that the Range starts at "A" column, if it doesn't you have to add the range first column to column.

custom spreadsheet function - get a1 notation for the range?

1 Answers