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:
Here is my test workable table:
The result of test table:
All at all i want to sent message to discord when my table changed
2
Answers
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.
The message that was referred as "Error 1",
It is not an execution time error. It’s a log message printed to the execution logs because
sheetName
is not equal toSheet2
. Please ensure that the edit sheet is namedSheet2
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 beundefined
. The simplest way to test theonEdit
function is by editing the spreadsheet using the Google Sheets web app UI. This assigns an instance of the edit event object toe
. 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.