skip to Main Content

I do script in google sheets, then set trigger. On my test table it works clearly, but on other table it get error.

function onEdit(e) {
  var discordUrl = "MYWEBHOOK_URL";

  const range = e.range;
  const sheet = range.getSheet();
  var sheetName = sheet.getName();
  var data = sheet.getDataRange().getValues();
  
  const column1 = sheet.getRange("H:H").getColumn() - 1;
  const column2 = sheet.getRange("I:I").getColumn() - 1;
  const column3 = sheet.getRange("J:J").getColumn() - 1;
  var arrmessage =[];
  for (var i = 3; i < data.length; i++) {
    if (data[i][column2] !== "" || data[i][column3] !== "") {
      arrmessage.push({name: `${data[i][column1]}`,value:"",inline: true});
      arrmessage.push({name: `${data[i][column2]}`,value:"",inline: true});
      arrmessage.push({name: `${data[i][column3]}`,value:"",inline: true});
    }
  }
  Logger.log(arrmessage);

  var options = {
    "method": "post",
    "contentType": "application/json",
    "muteHttpExceptions": true,
    "payload": JSON.stringify({
      "content": "Here it is",
      "embeds": [{
        "color": 16711680,
        "title": "Best player",
        "fields": arrmessage,
        "footer": {
          "text": "TRN CLNX"
        }
      }]
    })
  };

  try {
    if (sheetName === "Sheet2") {
      var response = UrlFetchApp.fetch(discordUrl, options);
    } else {
      Logger.log("Message is empty or contains 'undefined'. No request sent.");
    }
  } catch (error) {
    Logger.log("Error occurred: " + error.toString());
  }
}

This one with status complete

ERROR1:Message is empty or contains 'undefined'. No request sent. 

This one with status uncomplete

ERROR2: TypeError: Cannot read properties of undefined (reading 'getSheet')<br>
    at onEdit(Код:7:23) 
ERROR 3: Information Error occurred: Exception: You are not allowed to call UrlFetchApp.fetch user. Required permissions: https://www.googleapis.com/auth/script.external_request.

I’m trying to reproduce part of table in embeded message in discord (with trigger "when change")

Here is my prod table:
I want to get H,I,J to embed discord meesage
Here is my test workable table:
test g.sheet
The result of test table:
discord embed message

All at all i want to sent message to discord when my table changed

2

Answers


  1. The authorization error you are getting is because of the onEdit() function being a simple trigger.

    Simple triggers cannot access API services that require authorization.

    Change the function’s name and set an installable trigger to run the target function on edit events.

    Login or Signup to reply.
  2. The message that was referred as "Error 1",

    Message is empty or contains ‘undefined’. No request sent."

    It is not an execution time error. It’s a log message printed to the execution logs because sheetName is not equal to Sheet2. Please ensure that the edit sheet is named Sheet2 or change the literal in the if statement accordingly.

    The message that was referred to as "Error 2" likely occurred because onEdit was run using the Apps Script Editor Run button. This causes the variable e to be undefined. The simplest way to test the onEdit function is by editing the spreadsheet using the Google Sheets web app UI. This assigns an instance of the edit event object to e. See How can I test a trigger function in GAS?.

    Error 3 is straightforward. Add https://www.googleapis.com/auth/script.external_request to the OAuth scopes in the Apps Script manifest. For details about how to edit the manifest, see https://developers.google.com/apps-script/concepts/manifests.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search