Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- function onEdit(e) {
- var ss = SpreadsheetApp.getActiveSpreadsheet();
- var sheet = ss.getSheetByName('Lab Analysis');
- // define edit range
- var editRange = sheet.getActiveRange();
- var editRow = editRange.getRow();
- var editCol = editRange.getColumn();
- var range = sheet.getRange("AG6:AG");
- var rangeRowStart = range.getRow();
- var rangeRowEnd = rangeRowStart + range.getHeight();
- var rangeColStart = range.getColumn();
- var rangeColEnd = rangeColStart + range.getWidth();
- // if cells lie within the edit range, run the following script
- if (editRow >= rangeRowStart && editRow <= rangeRowEnd
- && editCol >= rangeColStart && editCol <= rangeColEnd) {
- // set today's date and store a date object for today
- var date = ss.getSheetByName('Daily Process
- Limits').getRange("B1").setValue(new Date()).getValue();
- // get values in date range
- var daterange = sheet.getRange("A6:A").getValues();
- // iterate the values in the range object
- for(var i=0; i<daterange.length; i++) {
- // compare only month/day/year in the date objects
- if (new Date(daterange[i]).setHours(0,0,0,0) ==
- date.setHours(0,0,0,0)) {
- // if there's a match, set the row
- // i is 0 indexed, add 6 to get correct row
- var today_row = (i+6);
- var today_set = ss.getSheetByName('Daily Process
- Limits').getRange("D1").setValue(today_row);
- var today_fos_tac_f1 = sheet.getRange("AE"+today_row).getValue();
- var today_fos_tac_f2 = sheet.getRange("AF"+today_row).getValue();
- var today_fos_tac_pf = sheet.getRange("AG"+today_row).getValue();
- // pop up notifications to operator
- if (today_fos_tac_f1 > 0.3) {
- SpreadsheetApp.getUi().alert('pop up notification content'); }
- if (today_fos_tac_f2 > 0.3) {
- SpreadsheetApp.getUi().alert('pop up notification content'); }
- if (today_fos_tac_pf > 0.3) {
- SpreadsheetApp.getUi().alert('pop up notification content'); }
- // Set email addresses
- var emails = ['emailaddress@gmail.com'];
- // send email notification to site manager
- if (today_fos_tac_f1 > 0.3) {
- MailApp.sendEmail(emails, 'High FOS:TAC in Fermenter 1', 'email content');}
- if (today_fos_tac_f2 > 0.3){
- MailApp.sendEmail(emails, 'High FOS:TAC in Fermenter 2', 'email content');}
- if (today_fos_tac_pf > 0.3){
- MailApp.sendEmail(emails, 'High FOS:TAC in Post Fermenter', 'email content');}
- }
- }
- }}
Add Comment
Please, Sign In to add comment