点击图片运行脚本时，如何取消选择活动单元格？ [英] How do I deselect an active cell when clicking on an image to run a script?

问题描述

我有一个电子表格绑定脚本，通过点击电子表格中的图像来调用。我发现，如果需要修改的单元格处于活动状态或正在被用户编辑，那么脚本可以被阻止。

I have a spreadsheet-bound script that is invoked by clicking an image in the spreadsheet. I've found that the script can be blocked if a cell that it needs to modify is active or being edited by the user.

代码可以是任何东西，即使是像这样简单：

The code could be anything, even as something as simple as this:

function newf() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var range = sheet.getRange("R4");
  range.clear();
}

当单元格 R4 是打开的，函数将忽略单元格。

When the cell R4 is open, the function will ignore the cell.

当点击图片运行脚本时，如何取消选择开放单元格？

How do I deselect an open cell when clicking on an image to run a script?

简单的（手动）解决方案是在点击图像之前点击开放单元格，但是我正在尝试使脚本无懈可击，这可能是一个昂贵的问题。有没有什么办法可以强制取消一个活动单元格？

The easy (manual) solution would be to click off the open cell prior to clicking on the image, but I am trying to make the script fool-proof and this can be a costly concern. Is there any way that the function can forcefully deselect an active cell?

编辑：我不是指活动单元格。绝对是开放的细胞。这里是错误的图片。

I do not mean active cell. Definitely "open cell." Here are pictures of the error.

输入数据的开放单元格

Open cell with data being entered

点击图片，程序运行

Clicked on Picture, program ran

推荐答案

您可以使用 Sheet.setActive（）将附加的用户光标移动到电子表格中的其他位置。您需要记住用户界面和从其中调用的脚本之间存在关系，这使得脚本可以确定和更改当前活动的单元格。但是，这并没有扩展到多用户案例 - 如果User1在用户点击运行脚本时编辑了保护单元格，脚本将不知道它。

You can use Sheet.setActive() to move the attached user's cursor to a different location in the spreadsheet. You need to remember that there is a relationship between the User Interface and scripts invoked from it, which is what allows the script to determine and change the currently active cell. This does not extend to the multiple user case, though - if User1 is editing the guarded cell when User2 clicks to run the script, the script will have no idea about it.

function newf() {
  var safePlace = "A1"; // Some safe cell
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var activeCell = ss.getActiveCell();
  if (activeCell.getSheet() == sheet && activeCell.getA1Notation() == "R4") 
    sheet.setActiveSelection(safePlace);
  var range = sheet.getRange("R4");
  var activeCell = ss.getActiveCell();
  if (activeCell.getA1Notation() == range.getA1Notation() && activeCell.getSheet().getName() == range.getSheet().getName()) {
    // Move user from target cell
    sheet.setActiveSelection(safePlace);
  range.clear();
}

这篇关于点击图片运行脚本时，如何取消选择活动单元格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！