活动工作表始终是Google表格中最左侧的工作表 [英] Active Sheet always being leftmost sheet on Google Sheets

问题描述

上一个问题的继续:使用onChange检测注释更改

我知道这是一个重复的问题，但是类似问题的答案对我没有用.

I know that this is a duplicate question, but the answers from the questions similar to it have not worked for me.

Google App脚本getActiveSheet返回最左边的表单，而不是最左边的表单活动表

function createSpreadsheetOpenTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onChange')
      .forSpreadsheet(ss)
      .onChange()
      .create();
}

function onChange(e) {
  var ss = SpreadsheetApp.getActive().getActiveSheet();
  if(e.changeType == 'OTHER') {
    if (ss.getName()=="Completed") {
      var celladdress ='G2';
      ss.getRange(celladdress).setValue(Utilities.formatDate(new Date(), "GMT-08:00", "MM/dd/yyyy HH:mm"));
    }
  }
} 

这是复制问题的样本表. https://docs.google.com/spreadsheets/d/1pVfIv6NRybK8JH-FgzTLYiTkzu73-8iD77HFZOGk3iA/edit#gid = 2124234178

Here is the sample sheet replicating the issue. https://docs.google.com/spreadsheets/d/1pVfIv6NRybK8JH-FgzTLYiTkzu73-8iD77HFZOGk3iA/edit#gid=2124234178

编辑或清除已完成"中的注释工作表应在单元格G2中写一个日期，但不能.另外，返回活动单元格的getRow()和getCol()总是产生1，这可能是由于该单元格不存在于活动工作表中(它仍然卡在最左边的工作表上).帮助将不胜感激.

Editing or clearing a note in "Completed" sheet should write a date to the cell G2, but it does not. Additionally returning getRow() and getCol() of the active cell always yields in 1, presumably due to the fact that the cell does not exist in the active sheet (it's still stuck on leftmost sheet). Help would be appreciated.

在进一步测试中，问题似乎出在changeType OTHER上.如果我将类型更改为EDIT，则电子表格是正确的.我认为这是一个错误.我希望能找到一种解决方法.

On further testing, the problem seems to lie with changeType OTHER. If I change the type to EDIT, the spreadsheet is the correct one. I presume it's a bug. I'd appreciate a workaround.

推荐答案

尝试一下:

function createSpreadsheetOpenTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onMyChange')
      .forSpreadsheet(ss)
      .onChange()
      .create();
}

function onMyChange(e) {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Completed');
  var celladdress ='G2';
  if(e.changeType == 'OTHER') {
    sheet.getRange(celladdress).setValue(Utilities.formatDate(new Date(), "GMT-08:00", "MM/dd/yyyy HH:mm"));
  }
}

您已安装了onChange触发器.卸载并安装onMyChange触发器.

You have an onChange trigger installed. Uninstall it and install the onMyChange trigger.

这篇关于活动工作表始终是Google表格中最左侧的工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！