I've been trying to automate sending messages when some of my reports are uploaded through a checkbox trigger on Gscript. (Idea being report is updated-> User check the checkbox -> message is sent to slack)
So far, I've been able to create the script to send the message to slack, and even reset my checkboxes at night. The only issue I have is with the trigger.
I've used onEdit as a trigger, on my three checkboxes (A1,B1,C1):
function onEdit(e) {
const as = e.source.getActiveSheet();
const cell = e.range.getA1Notation();
if(as.getName() == 'Sheet1' && cell.getRange('A1').ischecked() === true) {
SendSlackMessage1();
}
else if (as.getName() == 'Sheet1' && cell.getRange('B1').ischecked() === true) {
SendSlackMessage2();
}
else if (as.getName() == 'Sheet1' && cell.getRange('C1').ischecked() === true) {
SendSlackMessage3();
}}
As a reference here's the function to send messages :
function SendSlackMessage1() {
const url = "https://hooks.slack.com/services/XXXXXXXXXXXXX";
const params = {
method: "post",
contentType: "application/json",
payload: JSON.stringify({
"text":"Report A has been uploaded"
})
}
const sendMsg = UrlFetchApp.fetch(url, params)
var respCode = sendMsg.getResponseCode()
Logger.log(sendMsg)
Logger.log(respCode)
}
(All of SendSlackmessages function are the same except for text (Report A/B/C), and I've already authorized the functions to run on google.
So obviously, I'm doing something wrong, but what ? my first guess would be that I'm not properly defining the e range, but I don't know what to do next.
I'd be grateful if someone can give me a headstart or pointing me at where to look.
back here because I've found the answers to my issues, and I want to share:
First of all, with an onEdit(e), you can't run it within the editor, hence my issues finding what was wrong. So, here's the code i'm using now, that works :
function SendSlackMessages(e) {
const col = e.range.getColumn();
const row = e.range.getRow();
const checked = e.range.isChecked();
var rtexte = "";
const url = "https://hooks.slack.com/services/XXXX";
if (col == 3 && row == 3){
rtexte = "Short EPGS Horizon";
} else if (col == 4 && row == 3){
rtexte = "Generic Days with EPGS Data";
} else if (col == 5 && row == 3){
rtexte = "TBAs";
} else {return false;}
const params = {
method: "post",
contentType: "application/json",
payload: JSON.stringify({
"text":"Report "+ rtexte +" has been uploaded"
})
}
if(checked === true){
const sendMsg = UrlFetchApp.fetch(url, params);
var respCode = sendMsg.getResponseCode();
Logger.log(sendMsg);
Logger.log(respCode);
}
}
On this function, I have added an onEdit trigger, and it works, send slack messages only when the edit happens in the right cells.
Hope it can help someone in the future.
The simply trigger onEdit() can't be used to perform an action that needs an authorization. Probably an installed variant of the trigger can do. It can be the one of the causes of the problem.
The code looks weird. Are you sure it will work at all?
cell is a string. Thus you can't get a range this way: cell.getRange('A1'). Perhaps you want to change its definitions this way (if you need the constant at all):const cell = e.range;
cell.getRange('A1') probably it should be:as.getRange('A1')
ischecked() will work. It should be isChecked():https://developers.google.com/apps-script/reference/spreadsheet/range#ischecked
etc...