编辑特定范围时如何运行功能 [英] How to run a function when specific range is edited

问题描述

我有一个打开包含消息和音乐的补充工具栏的功能.此函数运行完美，但是我希望在编辑某些单元格时运行 showSidebar 函数("Sheet2"上的C2:E10).我已经尝试了以下代码，但是当在电子表格中的任何地方进行编辑时，它仍然可以运行该功能.

I have a function which opens a Sidebar containing a message and music. This function works perfectly but i would like the function showSidebar to run when certain cells are edited (C2:E10 on "Sheet2"). I have tried the following code but this still runs the function when an edit is made anywhere in the Spreadsheet.

function onEdit(showSidebar){
  var range = 
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getRange(2, 3, 10, 1)
}

完整的代码页在这里:

function onEdit(e){
  var range = e.range;
  var sheetName = e.source.getActiveSheet().getSheetName();
  if ( // C2:E10 on "Sheet2"
    sheetName == "Sheet2" &&
    range.rowStart >= 2 &&
    range.columnStart >= 3 &&
    range.rowStart <= 10 &&
    range.columnStart <= 5
  ) {
    showSidebar();
    // do something
    // showSidebar(); // If you want to run the function of showSidebar(), please use this.

  }
}

var SIDEBAR_TITLE = 'Dashboard Notification!';

/**
 * Adds a custom menu with items to show the sidebar and dialog.
 *
 * @param {Object} e The event parameter for a simple onOpen trigger.
 */
function onOpen(e) {
  SpreadsheetApp.getUi()
      .createAddonMenu()
      .addItem('Show sidebar', 'showSidebar')
      .addToUi();
}

/**
 * Runs when the add-on is installed; calls onOpen() to ensure menu creation and
 * any other initializion work is done immediately.
 *
 * @param {Object} e The event parameter for a simple onInstall trigger.
 */
function onInstall(e) {
  onOpen(e);
}

/**
 * Opens a sidebar. The sidebar structure is described in the Sidebar.html
 * project file.
 */


function showSidebar() {
  var ui = HtmlService.createTemplateFromFile('Sidebar')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle(SIDEBAR_TITLE);
  SpreadsheetApp.getUi().showSidebar(ui);
} 

已将此脚本从我的测试区域复制到活动工作表中，因此在编辑范围时触发器不再起作用.任何人都可以阐明为什么会发生这种情况

Having copied this script from my test area to live Sheet, the trigger no longer works when the range is edited. Can anyone shed any light on why this may happen

推荐答案

• 您要在"Sheet2"上的"C2:E10"单元格被编辑后运行脚本.
• 您正在使用简单触发器的OnEdit事件触发器.

如果我的理解是正确的，那么该示例脚本如何?请认为这只是几个答案之一.

If my understanding is correct, how about this sample script? Please think of this as just one of several answers.

function onEdit(e){
  var range = e.range;
  var sheetName = e.source.getActiveSheet().getSheetName();
  if ( // C2:E10 on "Sheet2"
    sheetName == "Sheet2" &&
    range.rowStart >= 2 &&
    range.columnStart >= 3 &&
    range.rowStart <= 10 &&
    range.columnStart <= 5
  ) {

    // do something
    showSidebar(); // If you want to run the function of showSidebar(), please use this.

  }
}

• 将具有3行的值放入"E10"时，会将值放入"E10:E12"的单元格中.在此脚本中，该函数运行.
  • 如果您不想在值超出范围时运行，请修改 range.rowStart< = 10&&range.columnStart< = 5 到 range.rowEnd< = 10&&range.columnEnd< = 5 .
  • 事件对象

  如果我误解了您的问题，而这不是您想要的结果，我深表歉意.

  If I misunderstood your question and this was not the result you want, I apologize.

  如果函数包含授权所需的几种方法，请将OnEdit事件触发器与可安装触发器一起使用. https://developers.google.com/apps-script/guides/triggers/可安装

  If the function includes several methods which are required to authorize, please use the OnEdit event trigger with the installable trigger. https://developers.google.com/apps-script/guides/triggers/installable

  那时，请将函数名称修改为"fullows".

  At that time, please modify the function name as fullows.

  在使用此脚本之前，请安装 installableOnEdit 功能作为OnEdit事件触发器的可安装触发器.您可以在此处中了解如何安装它...>

  Before you use this script, please install the function of installableOnEdit as the installable trigger of OnEdit event trigger. You can see how to install it at here.

  function installableOnEdit(e){ // Modified
    var range = e.range;
    var sheetName = e.source.getActiveSheet().getSheetName();
    if ( // C2:E10 on "Sheet2"
      sheetName == "Sheet2" &&
      range.rowStart >= 2 &&
      range.columnStart >= 3 &&
      range.rowStart <= 10 &&
      range.columnStart <= 5 && // Modified
      "value" in e // Added
    ) {
  
      showSidebar();
  
    }
  }
  

  • 在这种情况下，功能名称从 onEdit 修改为 installableOnEdit .
    • 将 onEdit 安装为可安装触发器时，将同时运行简单触发器和可安装触发器.通过将函数名称从 onEdit 修改为其他名称，可以防止重复运行.
    • In this case, the function name was modified from onEdit to installableOnEdit.
      • When onEdit is installed as the installable trigger, both the simple trigger and the installable trigger are run. By modifying the function name from onEdit to other name, the duplicate running can be prevented.
      • 此外，当在范围内删除该值时，不会运行 showSidebar().

      这篇关于编辑特定范围时如何运行功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！