Final Edit: I was working in a sheet another developer had started, and it turns out they created a whole bunch of extra triggers. This being my first Gsheets app, I didnt realize what had happened till I spent more time on it and learned more about triggers Thank you for people who answered and pointed me in that direction.
I have a Google Sheet script that triggers when the value of a trigger column changes to "Add to Monday".
When I manually change the column to "Add to Monday", the script collects some of the row data from the row in which I changed the trigger column to "Add to monday". It then sends it as a payload to my endpoint.
The problem is, is that I can see that my GSheet script is getting triggered 8 times every time I try to trigger it once. As a result it is sending the same payload to my endpoint, leading to duplicate entries being created on in my Monday.com account.
function onEditInstallable(e) {
console.log("function executed");
var triggerColumn = 15; // Replace with the column number that triggers the action
var targetUrl = "https://endpoint.url/"; // placeholder endpoint - actual endpoint removed
var range = e.range;
var column = range.getColumn();
var value = range.getValue();
// Check if the edit occurred in the correct sheet, column, and value
if (
column === triggerColumn && value == "Add to Monday"
) {
var row = range.getRow();
var sheet = range.getSheet();
var values = sheet.getRange(row, 1, 1, 10).getValues()[0];
var payload = {
"col1": values[0],
"col2": values[1],
"col3": values[2],
"col4": values[3],
"col5": values[4],
"col6": values[5],
"col7": values[6],
"col8": values[7],
"col9": values[8],
"col10": values[9]
};
console.log(payload);
// Send the data to Zapier endpoint
var options = {
method: "post",
payload: payload,
};
var success = false;
try {
var response = UrlFetchApp.fetch(targetUrl, options);
if (response.getResponseCode() === 200) {
success = true;
}
} catch (error) {
success = false;
}
// Temporarily remove data validation
var validationRule = range.getDataValidation();
range.clearDataValidations();
// Update the trigger column with the status
var status = success ? "Success" : "Failed";
range.setValue(status);
// Reapply data validation
range.setDataValidation(validationRule);
}
}
I did try to implement "Lock Service", which kind of helped. The app script still is triggered 8 times when I changed change the trigger column in a single row, but only 1-2 entries would be created on monday.com. So 1-2 duplicate entries is better than 8, but still not ideal. This is the version of my code w/ Lock Service.
function onEditInstallable(e) {
var triggerColumn = 15; // Replace with the column number that triggers the action
// Check if the edit occurred in the correct sheet and column
if (e.range.getColumn() === triggerColumn) {
// Acquire a lock to prevent concurrent executions
var lock = LockService.getScriptLock();
try {
if (lock.tryLock(10000)) { // Wait up to 10 seconds to acquire the lock
processTrigger(e);
} else {
console.log("Function is already running.");
}
} catch (e) {
console.error("Error acquiring lock:", e);
} finally {
lock.releaseLock();
}
}
}
function processTrigger(e) {
var targetUrl = "https://endpoint.url/"; // placeholder endpoint - actual endpoint removed
var range = e.range;
var value = range.getValue();
if (value === "Add to Monday") {
var row = range.getRow();
var sheet = range.getSheet();
var values = sheet.getRange(row, 1, 1, 10).getValues()[0];
var payload = {
"col1": values[0],
"col2": values[1],
"col3": values[2],
"col4": values[3],
"col5": values[4],
"col6": values[5],
"col7": values[6],
"col8": values[7],
"col9": values[8],
"col10": values[9]
};
console.log(payload);
// Send the data to Zapier endpoint
var options = {
method: "post",
payload: payload,
};
var success = false;
try {
var response = UrlFetchApp.fetch(targetUrl, options);
if (response.getResponseCode() === 200) {
success = true;
}
} catch (error) {
success = false;
}
// Temporarily remove data validation
var validationRule = range.getDataValidation();
range.clearDataValidations();
// Update the trigger column with the status
var status = success ? "Success" : "Failed";
range.setValue(status);
// Reapply data validation
range.setDataValidation(validationRule);
}
};
EDIT:
Explanation of how I am triggering the event:
I have a column titled "Automation". The data I am sending via this endpoint is the row data. When I change the value of the "Automation" column to "Add to Monday", it takes the row data for that row and then sends it as a payload to the endpoint. See Image below. Currently my code also will change the value of this column to "Success" but that is only for this testing iteration - I will need to make sure the data makes it to Monday.com before changing it to success.
EDIT II: Triggers for a single event (image)
