2

I am a basketball coach & I am creating a dashboard to monitor my players' social media. I'm using IFTTT.com to pull in all of my players' tweets in real-time to a spreadsheet. I'm trying to write a code that if one of my players uses an inappropriate word, it'll trigger an email to me of that cell. I feel like I'm on the right track, but some guidance on my code is greatly appreciated. 

function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet()

var cell = ss.getActiveCell().activate();

Logger.log( cell.getA1Notation() );

if (cell.getValue().match("ass")) {
MailApp.sendEmail("example@example.com", "Notice of possible inappropriate tweet", cell;}

}`

This is the code for just one inappropriate word, obviously, as I'm just trying to get the basics of the coding down before I add 100 inappropriate words. The trouble is that if the sheet pulls in three tweets at the same time, it's only going to check on the last one, so that's where my main troubles lie right now.

Any guidance or help here is greatly appreciated!

Tony Adragna
  • 77
  • 1
  • 8
  • Are you running into a specific problem with this code? I can see a few ways to simplify /improve it, but those changes might not fix the problem you are encountering. I suggest updating the question with an indication of exactly what isn't working for you. – Cameron Roberts Oct 24 '14 at 13:01

1 Answers1

0

I agree with Camerons comments above, if you can describe what's not working for you specifically, that would give us an idea of what you need help with.

As it stands, your code runs for me, and it will email me when I edit a cell to contain the word 'ass'.

However, one catch is that currently in the body of the email you have the variable 'cell' which will only return the word 'range'.

Here's a slightly updated version that provides more helpful notation in the email: 

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("ass")){
    MailApp.sendEmail("example@example.com", "Notice of possible inappropriate tweet", "The cell: " + badCell + " contains the word: " + badCellContent + ".");
  }
}
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
HDCerberus
  • 2,113
  • 4
  • 20
  • 36
  • Apologies! I should have been more clear. The issue I'm running into is that if three tweets come into the spreadsheet at the same time, with my code, it's only going to update the most recent cell, not all three. Does that make sense? – Tony Adragna Oct 24 '14 at 14:15
  • Yes.... But I'm not sure it's a concern you need to worry about. 1) It's very unlikely unless you have a huge number of players, or are pulling the tweets at intervals instead of in realtime. 2) I imagine IFTTT will be performing a write action for each tweet, rather then saving them and writing them as a range, so it should trigger the 'onEdit' action every time. – HDCerberus Oct 24 '14 at 15:05
  • When I run this, I'm not getting it to send me an email when i put "ass" in a cell. Does onEdit have this capability? – Tony Adragna Oct 24 '14 at 16:54
  • It runs for me perfectly, so there may be another source for your issue. Have you definitely added the 'onEdit' trigger? – HDCerberus Oct 24 '14 at 16:56
  • So are you setting a trigger to run every minute? – Tony Adragna Oct 24 '14 at 17:02
  • Ah, this question has me suspect that this may be the source of the issue. Rather then a trigger that runs every minute, you literally need to set a trigger that runs on every edit. This is what it would look like: http://prntscr.com/4zax33 – HDCerberus Oct 24 '14 at 17:05
  • 1
    That's the problem! You're a lifesaver. If I had a higher reputation, I'd upvote you, but I can't yet. Thanks for all of your help! – Tony Adragna Oct 24 '14 at 17:10
  • Well, after getting the spreadsheet all setup & actually using the trigger from IFTTT, it doesn't work. :( I'm assuming it's not dubbing it as the active cell whenever it automatically pulls it into the spreadsheet. Any idea on a workaround on that? – Tony Adragna Oct 24 '14 at 17:37
  • Without a more in depth look at the values in the sheets, I could only speculate what might be happening. If IFTTT is pulling data and writing it to more then one cell, It's likely to not trigger. You may instead need to specify that when the sheet is being written to, check cell XY for data. I'm unlikely to be online for a couple of days from now, but I'll have a look at how IFTT pulls data from tweets and writes them and send you an updated answer in a few days. Alternatively you can ask a new question with the specifics of this roadblock, and someone else may know the answer. – HDCerberus Oct 24 '14 at 17:52
  • Appreciate your help! – Tony Adragna Oct 24 '14 at 17:56