I have a Google Sheet that sends an alert if ANY of the ROW/COLS are changed or added. I send a Gmail alert to let a user know that there has been a change on Row(X).
However I am trying to add a condition so that if the Zap is triggered only to send an email if that value changes below a threshold - here 55%
When applying a conditional filter it is asking me for a specific col to watch, in this instance I need it to watch the whole sheet.
Any ideas anyone? I’m on a Premium Starter Plan.
Cheers everyone.
Mik
Best answer by ForYourITBest answer by ForYourIT
Sooo…..I had some fun ;) Let's see if this can help you out.
In Zapier, add a Trigger called Zapier webhooks and let it wait for a post request. Now this action will give you a URL which you will use.
In the script editor in Google Sheets, add the following code, and edit the first few lines as stated in the code.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var percentageValue = 0.65; //<----- Change thisvar url = 'https://hooks.zapier.com/hooks/catch/7133367/ozfh5ng/'; //<----- Change thisfunctionifEdit(e) {var selectedCell = ss.getActiveCell();
checkData(selectedCell);
}
functionsendPost(cellA1Notation, cellValue) {var cellValue = cellValue * 100;
try {
var payload = {
cell: cellA1Notation,
value: cellValue
};
payload = JSON.stringify(payload);
var options = {
method: "post",
contentType: "application/json",
payload: payload
};
var result = UrlFetchApp.getRequest(url, options);
Logger.log(result)
var result = UrlFetchApp.fetch(url, options);
Logger.log(result)
} catch (e) {
Logger.log(e)
}
}
functioncheckData(cell) {let cellValue = cell.getValue();
let row = cell.getRow();
let column = cell.getColumn();
var range = sheet.getRange(row, column);
var cellA1Notation = range.getA1Notation();
if (cellValue >= percentageValue) {
sendPost(cellA1Notation, cellValue);
}
}
Now since the function UrlFetchApp can't be runned on an edit, you need to make a project trigger. Do the following:
In your script editor, go to Edit → Current Triggers (see image)
In the right bottom, click on New Trigger
Now add the trigger action ifEdit when something gets edited (see image)
Now add something in your sheet anywhere, and it will send a webhook POST to Zapier
Within Zapier you can see 2 fields being returned; the cell notation and the cell value. Maybe you can use this in your future automation!
Let me know if this helped you out or if you need more assistance!
Thanks for this, it clarifies that it's not me missing something. Your suggestion is great, however, it's out of my remit, as I am not really conversant with JavaScript.
If you do manage to do this, please hook me in!
Thinking about it more… What I might do is the following ..
Add a Col called In_scope then a formula that checks any cell and if the cell is <55% set the row in that col to true, then get Zapier to trigger on true as it would know that it is within threshold!
