Google Apps Script Active Cell changes in Handler from the onEdit Code that called it

Question

I'm new to JavaScript and Google Apps Script so this is by all means a 'beginner' question I'm sure.

I'm using Google Apps Script in a spreadsheet to format single cells using an onEdit trigger. The format of the cell is based on user input from a custom UI app containing 6 buttons. The problem I'm having is the onClickHandler function doesn't see the same active cell that triggered the onEdit trigger initially; rather, the getActiveCell method called in the Handler is returning the cell just below the cell that was edited that became active as a result of the user pressing 'Enter' and the active range moving down one cell.

How do I get the handler to focus on the same cell that initiated the onEdit trigger? I'm not sure if I should somehow be passing the active cell from the event that triggered the onEdit code to the Handler via button parameters or somehow getting a value returned to the onEdit code to make the changes to the edited cell there. Whichever way is correct, I'm not sure how to go about it.

function onEdit(event)
{
  var sheet = event.source.getActiveSheet();
  var cell = sheet.getActiveCell();
  var cellR = cell.getRow();
  var cellC = cell.getColumn();
  var cellValue = cell.getValue();
  var active_spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  if ((cellR >= 14 && cellR <= 21 && cellC >= 1 && cellC <= 7) ||
           (cellR >= 34 && cellR <= 41 && cellC >= 1 && cellC <= 7)) {

    displayUserOptions();
  }
}

function displayUserOptions()
{
  var app = UiApp.createApplication().setTitle('Choose Equipment Class').setHeight(40).setWidth(350);
  var handler = app.createServerHandler('onClickHandler');
  var button1 = app.createButton('War').setTabIndex(1).setId('war').addClickHandler(handler);
  var button2 = app.createButton('Nature').setTabIndex(2).setId('nature').addClickHandler(handler);
  var button3 = app.createButton('Balance').setTabIndex(3).setId('balance').addClickHandler(handler);
  var button4 = app.createButton('Fortune').setTabIndex(4).setId('fortune').addClickHandler(handler);
  var button5 = app.createButton('Chaos').setTabIndex(5).setId('chaos').addClickHandler(handler);
  var button6 = app.createButton('Generic').setTabIndex(6).setId('generic').addClickHandler(handler);
  var mypanel = app.createHorizontalPanel();
  mypanel.add(button1); mypanel.add(button2); mypanel.add(button3);
  mypanel.add(button4); mypanel.add(button5); mypanel.add(button6);
  app.add(mypanel);

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  spreadsheet.show(app);

}

function onClickHandler(e) {
  var app = UiApp.getActiveApplication();
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getActiveCell();
  if (e.parameter.source == 'war') {
    Logger.log("War button pressed");
    Browser.msgBox(cell.getValue()); 
    cell.setValue("War!"); // *This string is committing to the wrong cell.
  }
  app.close();
  return app;
}

Serge insas Serge insas · Accepted Answer · 2013-09-27T08:28:14

You could handle that in your handler function simply like this :

function onClickHandler(e) {
  var app = UiApp.getActiveApplication();
  var sheet = SpreadsheetApp.getActiveSheet();
  var rowIndex = cell.getRowIndex()-1;
  var colIndex = cell.getColumnIndex();
  sheet.setActiveRange(sheet.getRange(rowIndex,colIndex));
  var cell = sheet.getActiveCell();
  ...

or you can store the coordinates of the cell in your Ui like in the example below (see the hidden widget and the callBackElement)

This has the advantage that it works even if the user uses the TAB key (or a mouseClick on another cell) to validate his entry... so I would recommend this approach even if it's a bit more code to add ;-)

function onEdit(event){
  var sheet = event.source.getActiveSheet();
  var cell = sheet.getActiveCell();
  var cellR = cell.getRow();
  var cellC = cell.getColumn();
  var cellValue = cell.getValue();
  var active_spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  if ((cellR >= 14 && cellR <= 21 && cellC >= 1 && cellC <= 7) ||
           (cellR >= 34 && cellR <= 41 && cellC >= 1 && cellC <= 7)) {

    displayUserOptions(cellR,cellC);
  }
}

function displayUserOptions(cellR,cellC){
  var app = UiApp.createApplication().setTitle('Choose Equipment Class').setHeight(40).setWidth(350);
  var mypanel = app.createHorizontalPanel();
  var hidden = app.createHidden('coord').setValue(cellR+'|'+cellC);
  mypanel.add(hidden);
  var handler = app.createServerHandler('onClickHandler').addCallbackElement(mypanel);
  var button1 = app.createButton('War').setTabIndex(1).setId('war').addClickHandler(handler);
  var button2 = app.createButton('Nature').setTabIndex(2).setId('nature').addClickHandler(handler);
  var button3 = app.createButton('Balance').setTabIndex(3).setId('balance').addClickHandler(handler);
  var button4 = app.createButton('Fortune').setTabIndex(4).setId('fortune').addClickHandler(handler);
  var button5 = app.createButton('Chaos').setTabIndex(5).setId('chaos').addClickHandler(handler);
  var button6 = app.createButton('Generic').setTabIndex(6).setId('generic').addClickHandler(handler);
  var mypanel = app.createHorizontalPanel();
  mypanel.add(button1); mypanel.add(button2); mypanel.add(button3);
  mypanel.add(button4); mypanel.add(button5); mypanel.add(button6);
  app.add(mypanel);

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  spreadsheet.show(app);

}

function onClickHandler(e) {
  Logger.log(Utilities.jsonStringify(e));
  var app = UiApp.getActiveApplication();
  var sheet = SpreadsheetApp.getActiveSheet();
  var rowIndex = Number(e.parameter.coord.split('|')[0]);
  var colIndex = Number(e.parameter.coord.split('|')[1])
  sheet.setActiveRange(sheet.getRange(rowIndex,colIndex));
  var cell = sheet.getActiveCell();
  if (e.parameter.source == 'war') {
  Logger.log("War button pressed");
  Browser.msgBox(cell.getValue()); 
  cell.setValue("War!"); // *This string is committing to the wrong cell.
  }else{
  cell.setBackground('#ffffaa');// just for test to confirm that the right cell has been modified
  }
  app.close();
  return app;
}

NOTE : if you prefer that the "active" cell is not changed but still want that the value comes in the right cell you can do it like this :

(change in the handler function)

  ... 
  var rowIndex = Number(e.parameter.coord.split('|')[0]);
  var colIndex = Number(e.parameter.coord.split('|')[1])
  var cell = sheet.getRange(rowIndex,colIndex);
  ...

Google Apps Script Active Cell changes in Handler from the onEdit Code that called it

1 Answers