Google app script OnEdit to run only on one cell in one sheet

user_19732018 0
Viewed 45

I'm new and playing around with Google sheet app script.

i'm trying to sheet on onEdit trigger for only one cell (B5). It contains a dropdown list. When staff selects the month (eg. May), it runs the May() script.

The below code works. But when i add an if() it suddenly stops.

Any help will be appreciated!

function onEdit(e) {
  let ss = SpreadsheetApp;
  let activeSheet = ss.getActive().getActiveSheet();
  let dropDownCell = activeSheet.getRange('B5').getValue();
  if (dropDownCell == 'January'){ return Jan() };
  if (dropDownCell == 'February'){ return Feb() }
  if (dropDownCell == 'March'){ return Mar() };
  if (dropDownCell == 'April'){ return Apr() };
  if (dropDownCell == 'May'){ return May() };
  if (dropDownCell == 'June'){ return Jun() };
  if (dropDownCell == 'July'){ return Jul() };
  if (dropDownCell == 'August'){ return Aug() };
  if (dropDownCell == 'September'){ return Sep() };
  if (dropDownCell == 'October'){ return Oct() }
  if (dropDownCell == 'November'){ return Nov() };
  if (dropDownCell == 'December'){ return Dec() };
  }```
google-apps-script google-sheets
1 Answers

I believe your goal is as follows.

  • You want to run the script when the cell "B5" is edited.

In this case, how about the following modification? In this modification, the event object e is used.

From:

function onEdit(e) {
  let ss = SpreadsheetApp;

To:

function onEdit(e) {
  const { range } = e;
  const sheet = range.getSheet();
  if (range.getA1Notation() != "B5") return;
  let ss = SpreadsheetApp;

Or, if you want to also check the sheet name, you can also the following modification.

function onEdit(e) {
  const sheetName = "Sheet1"; // Please set the sheet name.
  const { range, value } = e;
  const sheet = range.getSheet();
  if (range.getA1Notation() != "B5" || sheet.getSheetName() != sheetName) return;
  let ss = SpreadsheetApp;

Note:

  • When I saw your showing script, I thought that your script might be able be simpler. So, how about the following modification?

      function onEdit(e) {
    const sheetName = "Sheet1"; // Please set the sheet name.
    const { range, value } = e;
    const sheet = range.getSheet();
    if (range.getA1Notation() != "B5" || sheet.getSheetName() != sheetName) return;
    const obj = { 'January': Jan, 'February': Feb, 'March': Mar, 'April': Apr, 'May': May, 'June': Jun, 'July': Jul, 'August': Aug, 'September': Sep, 'October': Oct, 'November': Nov, 'December': Dec };
    if (!obj[value]) return;
    obj[value]();
  }

  • In this modification, the event object e is used. So, please edit the cell "B5" of the sheet. By this, the script is run by the onEdit trigger. If you directly run the script, an error like TypeError: Cannot destructure property 'range' of 'e' as it is undefined. occurs. Please be careful about this.

  • And, this modified script supposes that your functions of Jan(), Feb() and so on have no methods which cannot be used by the simple trigger. Please be careful about this. If an error related to the permission occurred, please consider using the installable trigger.

References:

user_71086530
Related