0

I am trying to use the discord webhook with google sheets.

Here is the function to send a message to discord given a webhook url. This function works perfectly fine when called manually (not inside onEdit).

function sendDiscord(msg) {
  var url = ""; // webhook url

  var message = JSON.stringify({
      content: msg
  });

  var options = {
    'method': 'post',
    'payload': message,
    muteHttpExceptions : true,
    contentType: "application/json"
  };

  var res = UrlFetchApp.fetch(url, options);
}

Input: https://i.gyazo.com/cf8686a85a1e281de64c44ea491f75ff.mp4

Output: output

But whenever I try to call the same function inside onEdit, the message does not pop up on discord.

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var tSheet = ss.getSheetByName("Tracking");

  if (e.range.getColumn() === 3) {
    if (e.value === "Needs Response") {
      tSheet.getRange(e.range.getRow(), 1, 1, tSheet.getLastColumn()).setBorder(true, true, true, true, false, false, "red", SpreadsheetApp.BorderStyle.DOUBLE);
      sendDiscord("The status has been updated."); // why does this line not run ???
    }
    else if (e.value === "To Be Sent") {
      tSheet.getRange(e.range.getRow(), 1, 1, tSheet.getLastColumn()).setBorder(true, true, true, true, false, false, "red", SpreadsheetApp.BorderStyle.DOUBLE);
    }
    else {
      // remove border
      tSheet.getRange(e.range.getRow(), 1, 1, tSheet.getLastColumn()).setBorder(false, false, false, false, false, false, null, null);
    }

  }

}

Everything else in the onEdit() function works perfectly fine as well. As you can see, when I selected "Needs Response" on google sheets, it added the borders, but did not send the message to discord.

Execution log running a test function by pressing "Run" using the same line:

function test() {
  sendDiscord("The status has been updated.");
}

Log: 5:01:39 PM Info

input on sheets output onedit call

Following Ruben's suggestions, here is what I am seeing in the "My Executions" tab:

Exception: You do not have permission to call UrlFetchApp.fetch. Required permissions: https://www.googleapis.com/auth/script.external_request at sendDiscord(Code:18:25) at onEdit(Code:34:7)

dan
  • 347
  • 2
  • 14
  • Log the response from the webhook, and add the executions logs – Rubén Sep 16 '22 at 20:13
  • Is it possible that the ordering of the functions matters? Maybe this could help? https://github.com/Kelo/Google-Sheets-to-Discord – Kelo Sep 16 '22 at 20:36
  • @Kelo I just tried switching them around. Still not working unfortunately :( – dan Sep 16 '22 at 20:59
  • @Rubén How should I send the execution logs for an onEdit function? – dan Sep 16 '22 at 21:00
  • @Rubén So I went to the "My Executions" tab instead and here is what I am seeing now: `Exception: You do not have permission to call UrlFetchApp.fetch. Required permissions: https://www.googleapis.com/auth/script.external_request at sendDiscord(Code:18:25) at onEdit(Code:34:7)` – dan Sep 16 '22 at 21:04

1 Answers1

0

After following Ruben's suggestion to look at the executions log, I saw that there was actually an error. And after searching online, I found the solution:

You do not have permission to call "UrlFetchApp.fetch"

dan
  • 347
  • 2
  • 14