Want an email sent to me every time conditional formatting is applied

Question

I have a spreadsheet that is automatically updated with tweets from certain Twitter lists. I have conditional formatting applied to the sheet that if there is a swear word within one of the tweets, it will highlight that particular cell red.

What I want is to have the cell (or even the entire spreadsheet) emailed to me whenever conditional formatting is applied. Is this possible?

Also, is there a way to have the spreadsheet's contents go into an "archive" sheet each day so that the main sheet has fresh content each day?

Thanks for your help!

So far, I've been working on the "archiving" script. What I have so far is below:

function importData() {

var ss = SpreadsheetApp.getActiveSpreadsheet(); //source ss

var sheet = ss.getSheetByName("Twitter"); //opens the sheet with your source data

var values = sheet.getRange("A:G").getValues(); //gets needed values

var ts = SpreadsheetApp.openById("1g5XaIycy69a3T2YcWhcbBy0hYrxSfoEEz8c4-zP63O8"); //target ss -        paste your key

ts.getSheetByName("Archives").getRange("A:G").setValues(values);}

It says range height in line 11 is wrong.

Here is my snippet of code I'm trying to use to get an email when a swear word is used. I feel like I'm on the cusp, but not quite there yet.

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

var cell = ss.getActiveCell();
var range = sheet.getRange(cell.getRow(),1,1,sheet.getDataRange().getLastColumn());
var note = cell.getBackground()

var email = "antadrag@gmail.com";
var subject = "Notice of possible inappropriate tweet";
message = cell.getValue();

if(message.contains ("piss")) {
range.setBackgroundRGB(255, 0, 0);
MailApp.sendEmail(email, subject, message);
}
};

Answer 1

To answer your first question, I agree with Dougs suggestion in the comments. The trigger you use to detect a swearword would be the best place to call a function to email you, rather then waiting for a conditional formatting change.

With regards to your query on how to archive the data, your making life a little too difficult for yourself with fetching the ranges and copying them to sheets etc. The 'copyTo()' operation is your friend here, as that will copy the entire sheet to another spreadsheet while preserving conditional formatting.

Here's a sample of how this could possibly work:

function importData() {
   var ss = SpreadsheetApp.getActiveSpreadsheet(); //source ss
   var sheet = ss.getSheetByName("Twitter"); //opens the sheet with your source data
   var ts = SpreadsheetApp.openById('SHEET ID');//Opens destination spreadsheet
   sheet.copyTo(ts);//Copy the data to the destination sheet. 
   sheet.clear();//Clear the source sheet.

   //Past this point it's optional, and only if you want to rename the destination sheet. 
   ts.getSheetByName("Copy of Twitter").activate();//Get the destination sheet by name.
   var date = new Date().getDate();//Random variable for the sheets new name
   ts.renameActiveSheet(date);//Set the new sheets name. 
}

This will open your source sheet, copy the data to a new spreadsheet of your choice and clear the source sheet of data. Optionally, I've added a few lines that rename the destination sheet to a variable of your choice so that it's not easy to understand when it was copied (I suggest date or time, but it can be whatever you want really).