Update cell value everytime active cell changes row

Question

I am using this formula to get the productID of the current row

=index(B:B,row())

From: Get value of specific column in current row?

It seems working but it looks like the cell does not refresh every time the cell moves from one row to another.

What I want is, for example the current active cell is in row 7. The cell value where that formula is entered will be B7. If I move/click to row 10, cell value should be B10, and so on...

Using that formula alone makes the cell value the same as B1 and does not change.

Is there a way to update that cell value every time active cell changes?

Thanks

tehhowch tehhowch · Accepted Answer · 2018-02-28T21:08:37

onSelect is not a valid trigger function, so you cannot write a function that replaces the content of a given cell.

What you can do is create a menu function that will do this, e.g.

function onOpen() {
    SpreadsheetApp.getActiveSpreadsheet()
        .addMenu('Updater', [{name:"Use Current Row", functionName:"updateRow"}]);
} 
function updateRow() {
    var cellToUpdate = "A1";
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    // Get the row in which the cursor is active.
    var newRow = sheet.getActiveCell().getRow();
    sheet.getRange(cellToUpdate).setFormula("= .... ");
}

Then when you have selected a cell in a new row, you activate the helper menu function to write the updated formula into your desired cell.

Update cell value everytime active cell changes row

1 Answers