I have made an example script that you should be able to see at https://docs.google.com/spreadsheets/d/11u0xkdtPlQsnVppCnPYM0CHuCTPLdmN8PcFlaW08lNw/edit#gid=0
You'll have to make a copy to actually do something with it.
But ... copying won't give you the trigger I set up. If you edit the script, go to the menu Reources --> Current project's triggers and make yourself a time-based trigger on the function checkForChanges(). I set it for "every minute" for testing purposes.
To your columns I added four new ones:
- concat - is simply a concatenation of all the row values whose changes you need to monitor
- Current Hash - is generated from a simple function I added to your script
- Last Edit - after sending an email the script gives this cell the value of Current Hash
- Changed - Compares Last Edit and Current Hash and says **true* if they are different.
So ... periodically the function checkForChanges() runs down the range ChangeDetector looking for true. If it finds nothing it quits immediately.
Each time it does find a change, it collects the data of that row and emails it. (Actually, I just log it, for simplicity sake.)
The key trick is the pair of lines :
lastEdits[row_][0] = currentHashCodes[row_][0];
ss_.getRangeByName("LastEdit").setValues(lastEdits);
Note how clear your code can be if you use named ranges where ever possible.
Here's the code in case the example gets lost someday in the future :
/* Called from cells in column "Current Hash" */
function strHash(valCell) {
var hash = 0;
if (valCell.length == 0) return hash;
for (i = 0; i < valCell.length; i++) {
char = valCell.charCodeAt(i);
hash = ((hash<<5)-hash)+char;
hash = hash & hash; // Convert to 32bit integer
}
return hash;
}
/* Called periodically from a timed trigger. */
function checkForChanges() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var changeDetector = ss.getRangeByName("ChangeDetector").getValues();
for (row in changeDetector) {
if (row > 0) {
changed = changeDetector[row][0];
if (changed) notify(ss, row);
}
}
}
/* Called by checkForChanges(). */
function notify(ss_, row_) {
initializeRangeArrays(ss_);
status = statii[row_][0];
project = projects[row_][0];
task = tasks[row_][0];
customer = customers[row_][0];
executor = executors[row_][0];
deadline = deadlines[row_][0];
sendto = recipients[row_][0];
var mysubject = status + ' | ' + project + ': ' + task + ' - ' + ss_.getName() + ' update';
var mybody = '\nStatus: ' + status
+ '\n\nproject: ' + project
+ '\ncustomer: ' + customer
+ '\ntask: ' + task
+ '\nexecutor: ' + executor
+ '\nDeadline: ' + deadline
+ '\n\n' + ss_.getName()
+ ': \n' + ss_.getUrl();
Logger.log("to: " + sendto);
Logger.log("subject: " + mysubject);
Logger.log("body: " + mybody);
Logger.log("");
lastEdits[row_][0] = currentHashCodes[row_][0];
ss_.getRangeByName("LastEdit").setValues(lastEdits);
};
var recipients = null;
var projects = null;
var customers = null;
var tasks = null;
var deadlines = null;
var executors = null;
var statii = null;
var lastEdits = null;
var currentHashCodes = null;
var rangeArraysInitialized = false;
/* Called by notify(). */
function initializeRangeArrays(ss_) {
if ( ! rangeArraysInitialized ) {
recipients = ss_.getRangeByName("Recipient").getValues();
projects = ss_.getRangeByName("Project").getValues();
customers = ss_.getRangeByName("Customer").getValues();
tasks = ss_.getRangeByName("Task").getValues();
deadlines = ss_.getRangeByName("Date").getValues();
statii = ss_.getRangeByName("Status").getValues();
executors = ss_.getRangeByName("Executor").getValues();
lastEdits = ss_.getRangeByName("LastEdit").getValues();
currentHashCodes = ss_.getRangeByName("CurrentHash").getValues();
rangeArraysInitialized = false;
}
}
Update 2014/09/22 :
I have made a few changes in the demo spreadsheet. Please take a peek.
To the script I added . . .
function strArrayHash(range) {
var ret = new Array();
var str = "";
for (item in range) {
str = range[item].toString();
if (str.length > 0) {
ret[item] = strHash(str);
Utilities.sleep(50); // play with this to reduce "internal execution error"s
} else {
ret[item] = "";
}
};
return ret;
}
In column "P" I replaced . . .
=if( M2 = "", "", strHash(R2)))
=if( M3 = "", "", strHash(R3)))
:
:
=if( M22 = "", "", strHash(R22)))
. . . with . . .
=ARRAYFORMULA(if( M2:M514 = "", "", strArrayHash(R2:R514)))
. . . in cell "P2" ONLY. I also deleted the contents of all cells in the range "P3:P22"
I increased the total number of rows to 1026
Once values appeared in column P, I copied cells P2:P1026 into M2:M1026 using [Paste Special] » [Paste values only].
In my i7 laptop it took it 30 seconds to recalc and detect a change in line 500.
I get an internal execution error if I try to do all 1024 lines.
Probably you will need to complicate the "if" clause such that it causes hash calculations on only the lines that really need it.
Update :: 2017/02/24 No one is interested in this so I stopped the trigger that ran the script.
