Best answer

Alert someone of a change on a google sheet only if value is less than 55%

  • 15 July 2020
  • 3 replies
  • 2216 views

Userlevel 1

Hi All,

 

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

 

 

icon

Best answer by ForYourIT 16 July 2020, 01:00

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

3 replies

Userlevel 7
Badge +7

Hi @Michael.Thomas,

This sounds like a great idea, however not possible with only Zapier modules. What you could do is the following:

  • Make an apps script in Google Sheets
  • Use the function onEdit(e) to wait for an edit in the sheet
  • When there is an edit, check the value of that specific cell
  • if the cells value is above your 55% statement, send a webhook call with doPost to Zapier webhooks

Hope this helps you out! it would require you to do some javascript. I might want to make something for you if I have some spare time.

Let me know if it works or if you need more assistance!

~Bjorn

Userlevel 1

Hi Bjorn,

 

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!  

=if(Sheetrange <65, True, False)

 

Cheers

Mik

Userlevel 7
Badge +7

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 this
var url = 'https://hooks.zapier.com/hooks/catch/7133367/ozfh5ng/'; //<----- Change this

function ifEdit(e) {
var selectedCell = ss.getActiveCell();
checkData(selectedCell);
}

function sendPost(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)
}
}

function checkData(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!

Cheers and have a great day.
~Bjorn