google apps script - Email notification if specific cell is changed and add another cells value to message without change tracking -

what did:

if cell in column m has been changed, email notification has been sended specify adres column n @ same row. need specify body text other columns @ same row. did working causes if other declared columns (such project, customer, task, executor) has been changed emil has been send to.

what need:

just track change in 1 "m" column , put @ body of email additional data other columns same row. , (thats point) did not track change @ other columns, email should send if changing column m.

probably easy, i'm twisted...

i bulid script based on:

• email notification if cell changed
• how attach onchange cell value event/script google sheet
• restrict notifications sent changes referenced in columns

my script:

function sendnotification() {   var ss = spreadsheetapp.getactivespreadsheet();   var sheet = ss.getactivesheet();   var cell = ss.getactivecell().geta1notation();   var row = sheet.getactiverange().getrow();   var cellvalue = ss.getactivecell().getvalue().tostring();   var sendto = '';       if(cell.indexof('m')!=-1){        sendto = sheet.getrange('n'+ sheet.getactivecell().getrowindex()).getvalue()       }      var project = '';        project = sheet.getrange('c'+ sheet.getactivecell().getrowindex()).getvalue()      var customer = '';       customer = sheet.getrange('d'+ sheet.getactivecell().getrowindex()).getvalue()         var task = '';       task = sheet.getrange('e'+ sheet.getactivecell().getrowindex()).getvalue()         var executor = '';       executor = sheet.getrange('f'+ sheet.getactivecell().getrowindex()).getvalue()    var deadline = '';       deadline = languageapp.translate(utilities.formatdate(sheet.getrange('i'+ sheet.getactivecell().getrowindex()).getvalue() , "gmt" , "eeee, dd mmmm yyyy" ),'en','pl')   var status = '';       status = sheet.getrange('m'+ sheet.getactivecell().getrowindex()).getvalue()            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();    mailapp.sendemail({     to:sendto,      subject:mysubject,     body:mybody}); }; 

i have made example script should able see @ https://docs.google.com/spreadsheets/d/11u0xkdtplqsnvppcnpym0chuctpldmn8pcflaw08lnw/edit#gid=0

you'll have make copy it.

but ... copying won't give trigger set up. if edit script, go menu reources --> current project's triggers , make time-based trigger on function checkforchanges(). set "every minute" testing purposes.

to columns added 4 new ones:

• concat - concatenation of row values changes need monitor
• current hash - generated simple function added script
• last edit - after sending email script gives cell value of current hash
• changed - compares last edit , current hash , says **true* if different.

so ... periodically function checkforchanges() runs down range changedetector looking true. if finds nothing quits immediately.

each time find change, collects data of row , emails it. (actually, log it, simplicity sake.)

the key trick pair of lines :

  lastedits[row_][0] = currenthashcodes[row_][0];   ss_.getrangebyname("lastedit").setvalues(lastedits); 

note how clear code can if use named ranges ever possible.

here's code in case example gets lost someday in future :

/* called cells in column "current hash" */ function strhash(valcell) {   var hash = 0;   if (valcell.length == 0) return hash;   (i = 0; < valcell.length; i++) {     char = valcell.charcodeat(i);     hash = ((hash<<5)-hash)+char;     hash = hash & hash; // convert 32bit integer   }    return hash; }   /* called periodically timed trigger. */ function checkforchanges() {    var ss = spreadsheetapp.getactivespreadsheet();   var changedetector = ss.getrangebyname("changedetector").getvalues();   (row in changedetector) {     if (row > 0) {       changed = changedetector[row][0];       if (changed) notify(ss, row);     }   }  }   /* called 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 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 few changes in demo spreadsheet. please take peek.

to script added . . .

function strarrayhash(range) {   var ret = new array();   var str = "";   (item in range) {     str = range[item].tostring();     if (str.length > 0) {       ret[item] = strhash(str);       utilities.sleep(50);      // play reduce "internal execution error"s     } else {       ret[item] = "";     }   };   return ret; } 

in column "p" replaced . . .

=if( m2 = "", "", strhash(r2))) =if( m3 = "", "", strhash(r3)))       :       : =if( m22 = "", "", strhash(r22))) 

. . . . . .

=arrayformula(if( m2:m514 = "", "", strarrayhash(r2:r514))) 

. . . in cell "p2" only. also deleted contents of cells in range "p3:p22"

i increased total number of rows 1026

once values appeared in column p, copied cells p2:p1026 m2:m1026 using [paste special] » [paste values only].

in i7 laptop took 30 seconds recalc , detect change in line 500.

i internal execution error if try 1024 lines.

probably need complicate "if" clause such causes hash calculations on lines need it.

update :: 2017/02/24 no 1 interested in stopped trigger ran script.