Trigger email when a cell is written to another application (IFTTT)

Question

So this is what I am working on. I am a basketball coach and have a spreadsheet that pulls all the tweets of my players from IFTTT.com (basically it takes the RSS feed on the twitter list, and when it is updated it updates the spreadsheet).

I am working on coding, which basically says: "If a player is repairing the wrong word, write me immediately."

I have a code that understands that if I just type an inappropriate word, it will turn the cell into red and write me a letter. However, I did not understand how to get the code to send me an email after IFTTT automatically updates the spreadsheet using tweets.

Here is my code so far. Right now I have only one word “trigger” which is “player” to try to get the table to work. Here is the code:

function onEdit(e) { var ss = SpreadsheetApp.getActiveSpreadsheet();//Get the spreadsheet var sheet = ss.getActiveSheet()//Get the active sheet var cell = ss.getActiveCell().activate();//Get the active cell. var badCell = cell.getA1Notation();//Get the cells A1 notation. var badCellContent = cell.getValue();//Get the value of that cell. if (badCellContent.match("players")){ cell.setBackgroundColor("red") MailApp.sendEmail(" [e-mail] ", "Notice of possible inappropriate tweet", "This tweet says: " + badCellContent + "."); } } 

Here is the link to the table I'm working with now: https://docs.google.com/spreadsheets/d/1g5XaIycy69a3T2YcWhcbBy0hYrxSfoEEz8c4-zP63O8/edit#gid=0 Any help or guidance on this subject is welcome! Thanks!

Answer 1

I originally wrote this answer for your previous question , so it includes answers to some of your comments from there, but since you keep asking the community to write this step by step, here is the next step.

---

The problem I am facing is that if three tables go to the spreadsheet at the same time, with my code, it will update the very last cell, not all three. It makes sense?

Yes, that makes sense.

When the onEdit() trigger function calls the spreadsheet service functions to retrieve current information from the worksheet, it enters the Race condition. If, after the change caused by onEdit() , and when it is assigned, the changes occur on the worksheet, these changes will be visible when it is launched. This is what you see when you think that the change you are processing is in the last line - by the time you process it, there may be a new last line.

The good news is that the attributes of the event object passed to onEdit contain information about the change. (Parameter e .) See Event Objects .

Using e.range and e.value , you will find that you have the location and contents of the edited cell that clicked on the trigger. If additional tweets arrive before the trigger is serviced, your function will not be tricked when processing the last line.

In new sheets, onEdit() can be triggered for multi-core changes, such as cut and paste. As unlikely that this could happen, it's worth it.

Well, after the spreadsheet is all set up and actually uses the trigger from IFTTT, it doesn't work. :( I assume that it doesn't duplicate it as an active cell whenever it automatically pulls it into the spreadsheet. Any idea about a workaround?

Q: When editing is not edited? A: When it is done using a script. In this case, this is a change. You can add an installable function on Change to catch these events. Unfortunately, the change event is less detailed than the edit event, so you have to read the spreadsheet to find out what has changed. My habit is for the change handler to mimic editing by creating a fake event (as for testing) and passing it to the onEdit function.

So give it a try. This script:

• processes a list of "bad words". (You can also easily follow the mention of your product or reason.)
• has an onEdit() function that uses an event object to evaluate the lines that caused the function to be called.
• colors of bad tweets
• has a function to test the trigger onEdit () based on How to test the trigger function in GAS?
• includes playCatchUp(e) , an installable trigger function (change and / or time) that will evaluate any lines that have not been previously evaluated. The Script property "Last Processed Row" used to track the value of this row. (If you plan to delete rows, you need to configure the property.)
• The sendMail function is disabled.

Enjoy it!

 // Array of bad words. Could be replaced with values from a range in spreadsheet. var badWords = [ "array", "of", "unacceptable", "words", "separated", "by", "commas" ]; function onEdit(e) { if (!e) throw new Error( "Event object required. Test using test_onEdit()" ); Logger.log( e.range.getA1Notation() ); // e.value is only available if a single cell was edited if (e.hasOwnProperty("value")) { var tweets = [[e.value]]; } else { tweets = e.range.getValues(); } var colors = e.range.getBackgrounds(); for (var i=0; i<tweets.length; i++) { var tweet = tweets[i][0]; for (var j=0; j< badWords.length; j++) { var badWord = badWords[j]; if (tweet.match(badWord)) { Logger.log("Notice of possible inappropriate tweet: " + tweet); colors[i][0] = "red"; //MailApp.sendEmail(myEmail, "Notice of possible inappropriate tweet", tweet); break; } } } e.range.setBackgrounds(colors); PropertiesService.getDocumentProperties() .setProperty("Last Processed Row", (e.range.getRowIndex()+tweets.length-1).toString()); } // Test function, adapted from https://stackoverflow.com/a/16089067/1677912 function test_onEdit() { var fakeEvent = {}; fakeEvent.authMode = ScriptApp.AuthMode.LIMITED; fakeEvent.user = " [e-mail] "; fakeEvent.source = SpreadsheetApp.getActiveSpreadsheet(); fakeEvent.range = fakeEvent.source.getActiveSheet().getDataRange(); // e.value is only available if a single cell was edited if (fakeEvent.range.getNumRows() === 1 && fakeEvent.range.getNumColumns() === 1) { fakeEvent.value = fakeEvent.range.getValue(); } onEdit(fakeEvent); } // Installable trigger to handle change or timed events // Something may or may not have changed, but we won't know exactly what function playCatchUp(e) { // Check why we've been called if (!e) Logger.log("playCatchUp called without Event"); else { // If onChange and the change is an edit - no work to do here if (e.hasOwnProperty("changeType") && e.changeType === "EDIT") return; // If timed trigger, nothing special to do. if (e.hasOwnProperty("year")) { var date = new Date(e.year, e.month, e["day-of-month"], e.hour, e.minute, e.second); Logger.log("Timed trigger: " + date.toString() ); } } // Find out where to start processing tweets // The first time this runs, the property will be null, yielding NaN var lastProcRow = parseInt(PropertiesService.getDocumentProperties() .getProperty("Last Processed Row")); if (isNaN(lastProcRow)) lastProcRow = 0; // Build a fake event to pass to onEdit() var fakeEvent = {}; fakeEvent.source = SpreadsheetApp.getActiveSpreadsheet(); fakeEvent.range = fakeEvent.source.getActiveSheet().getDataRange(); var numRows = fakeEvent.range.getLastRow() - lastProcRow; if (numRows > 0) { fakeEvent.range = fakeEvent.range.offset(lastProcRow, 0, numRows); onEdit(fakeEvent); } else { Logger.log("All caught up."); } }