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

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

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