Google Apps 脚本活动单元格在处理程序中从调用它的 onEdit 代码更改

Question

我是JavaScript和Google Apps Script的新手，所以这绝对是一个'初学者'的问题。

我在电子表格中使用Google Apps Script来使用onEdit触发器格式化单个单元格。单元格的格式基于包含 6 个按钮的自定义 UI 应用的用户输入。我遇到的问题是 onClickHandler 函数看不到最初触发 onEdit 触发器的相同活动单元格;相反，在处理程序中调用的 getActiveCell 方法返回编辑的单元格正下方的单元格，该单元格由于用户按"Enter"并且活动范围向下移动一个单元格而变为活动状态。

如何让处理程序专注于启动 onEdit 触发器的同一单元格？我不确定我是否应该以某种方式通过按钮参数将活动单元格从触发 onEdit 代码的事件传递给处理程序，或者以某种方式将值返回到 onEdit 代码以对那里编辑的单元格进行更改。无论哪种方式是正确的，我都不确定该怎么做。

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;
}

Answer 1

您可以在处理程序函数中简单地处理它，如下所示：

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();
  ...

或者您可以将单元格的坐标存储在 Ui 中，如下例所示(请参阅隐藏的小部件和 callBackElement(

这样做的好处是，即使用户使用 TAB 键(或鼠标单击另一个单元格(来验证他的输入，它也可以工作......所以我会推荐这种方法，即使要添加更多的代码;-(

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;
}

注意：如果您希望"活动"单元格不更改，但仍然希望值位于正确的单元格中，则可以这样做：

(更改处理程序函数(

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