CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Scott Shimer
Scott Shimer

Reputation: 131

Trigger is firing on all edits not just defined cells

Im trying to get edits made on a sheet to automatically send to Slack to alert people. That aspect is working great. However, instead of firing just when i have an change on a predefined range it fires on all changes no matter the tab.

here is the code im using in app script:

function buildReport() {
  const ss = SpreadsheetApp.getActive();
  let data = ss.getSheetByName('Mirror').getRange("A1:B13").getValues();
  let payload = buildAlert(data);
  sendAlert(payload);
}
 
 
function buildAlert(data) {
  let totalRevenue = data[0][1];
  let revenueBreakdown = data.slice(1).map(function(row) {
    return row[0] + ": " + row[1];
  }).join("\n");
 
  let payload = {
    "blocks": [
      {
        "type": "divider"
      },
      {
        "type": "section",
        "text": {
          "type": "mrkdwn",
          "text": " *2021 IFL FOOTBALL DRAFT* "
        }
      },
      
      {
        "type": "section",
        "text": {
          "type": "mrkdwn",
          "text": "Round " + totalRevenue
        }
      },
      {
        "type": "section",
        "text": {
          "type": "mrkdwn",
          "text": "Drafted Players:"
        }
      },
      {
        "type": "section",
        "text": {
          "type": "mrkdwn",
          "text": revenueBreakdown
        }
      }
    ]
  };
  return payload;
}
 
function sendAlert(payload) {
  const webhook = "https://hooks.slack.com/services/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"; //Paste your webhook URL here
  var options = {
    "method": "post", 
    "contentType": "application/json", 
    "muteHttpExceptions": true, 
    "payload": JSON.stringify(payload) 
  };
  
  try {
    UrlFetchApp.fetch(webhook, options);
  } catch(e) {
    Logger.log(e);
  }
}

Any help would be greatly appreciated! Again, i need to have the trigger fire only when changes have been made to a specific range, not the whole sheet.

Upvotes: 0

Views: 69

Answers (1)

pgSystemTester
pgSystemTester

Reputation: 9897

You need to have some conditions in place to check with what range of cells is being executed before firing off anything else.

Consider something like the below code. You may want to edit to include conditions such as only one cell being edited or consider if the first row (not last) falls into the range.

function onEdit(e) {
  const maxCol = 2;
  const maxRow = 13;

  var range = e.range;
  if (range.getLastColumn() <= maxCol && range.getLastRow() <= maxRow) {
    //Run your code.
    let data = ss.getSheetByName('Mirror').getRange("A1:B13").getValues();
    let payload = buildAlert(data);
    sendAlert(payload);
  }
}

Upvotes: 3

Related Questions