How to run a function when specific range is edited

Question

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

Full page of code here:

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

EDIT:

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

Answer 1

• You want to run the script when the cells of "C2:E10" on "Sheet2" are edited.
• You are using the OnEdit event trigger of the simple trigger.

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

Sample script:

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.

  }
}

• When the values which have 3 rows are put to "E10", the values are put to the cells of "E10:E12". In this script, the function is run.
  • If you don't want to run when the values are out of range, please modify range.rowStart <= 10 && range.columnStart <= 5 to range.rowEnd <= 10 && range.columnEnd <= 5.

Reference:

• Event Objects

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

Added:

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

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

Sample script:

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

  }
}

• 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.
• When the cells of "C2:E10" on "Sheet2" are edited, showSidebar() is run.
  • And also, when the value is deleted in the range, showSidebar() is NOT run.