Is there a way to set the value of a cell which the user isn't interacting with

Question

I have created a function that displays a pop-up message when a button is pressed and then sets the value of the cell the user is in as the time and was wondering if I could do the same thing but instead set the value of a different cell (ideally directly relating to the cell the user is in.

This is how I'm looking at it right now, but if there's an easier way (or no way) it would be very useful.

function endCellCode() {
  var cell = SpreadsheetApp.getCurrentCell();       //I am trying to add a variable that adds a 
                                                    //column to the CurrentCell, which i can set 
                                                    //the value of (without changing the value 
                                                    //of the CurrentCell
  var ui = SpreadsheetApp.getUi();
  var response = ui.alert('Do you want to end?', '', ui.ButtonSet.YES_NO);
  var output;
  if (response == ui.Button.YES) {
    cell.setValue(new Date());
  }
}

Thanks!

Answer 1

If you want to find the position of the next column but same row of your active cell, you can use cell.offset(0,1).

The following code will add a new timestamp to both the active cell and the cell in the next column same row:

function endCellCode() {
  var cell = SpreadsheetApp.getCurrentCell();
  var next_cell = SpreadsheetApp.getActive().getActiveSheet().getRange(cell.offset(0,1).getA1Notation());
                                           
  var ui = SpreadsheetApp.getUi();
  var response = ui.alert('Do you want to end?', '', ui.ButtonSet.YES_NO);
  var output;
  if (response == ui.Button.YES) {
    cell.setValue(new Date());
    next_cell.setValue(new Date())
  }
}

If there is no available column to the right of the cell then the script will automatically create one.

---

References:

• offset()