I am trying to run an on edit trigger, But it only works when I only works when i change the value in that cell only. The script enters an entire row of information, but the on edit trigger does not react when it does. only if i change the value in that cell separately. I tried to put a utilitysleep in because i was thinking it maybe that the email trigger is running before the rest of the script updates the spreadsheet. ( could that be the case? if so how do i fix this.
My Script below
// Clear form
function ClearCell() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formS = ss.getSheetByName("Form"); //Form Sheet
var rangesToClear = ["C9", "C12", "C15", "C18", "C21", "D7", "F9", "F12", "F15", "F18", "F21", "C24" ];
for (var i=0; i<rangesToClear.length; i++) {
formS.getRange(rangesToClear).clearContent();
}
}
//-------------------------------------------------
//Input Values
function SubmitData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formS = ss.getSheetByName('Form'); //Data entry Sheet
var dataS = ss.getSheetByName("Data"); //Data Sheet
var values = [[formS.getRange("C12").getValue(),
formS.getRange("C15").getValue(),
formS.getRange("C18").getValue(),
formS.getRange("C21").getValue(),
formS.getRange("C24").getValue(),
formS.getRange("F9").getValue(),
formS.getRange("F12").getValue(),
formS.getRange("F15").getValue(),
formS.getRange("F18").getValue(),
formS.getRange("F21").getValue()]];
dataS.getRange(dataS.getLastRow()+1, 2, 1, 10).setValues(values);
ClearCell();
}
//------------------------------------------------------------------
var SEARCH_COL_IDX = 0;
function Search() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formS = ss.getSheetByName("Form"); //Form Sheet
var str = formS.getRange("D7").getValue();
var values= ss.getSheetByName("Data").getDataRange().getValues();
for (var i = 0; i < values.length; i++) {
var row = values;
if (row[SEARCH_COL_IDX] == str) {
formS.getRange("C9").setValue(row[0]) ;
formS.getRange("C12").setValue(row[1]);
formS.getRange("C15").setValue(row[2]);
formS.getRange("C18").setValue(row[3]);
formS.getRange("C21").setValue(row[4]);
formS.getRange("C24").setValue(row[5]);
formS.getRange("F9").setValue(row[6]);
formS.getRange("F12").setValue(row[7]);
formS.getRange("F15").setValue(row[8]);
formS.getRange("F18").setValue(row[9]);
formS.getRange("F21").setValue(row[10]);
} }}
//--------------------------------------------------
function Update() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formS =ss.getSheetByName("Form"); //Form Sheet
var dataS = ss.getSheetByName("Data"); //Data Sheet
var str = formS.getRange("D7").getValue();
var values = ss.getSheetByName("Data").getDataRange().getValues();
for (var i = 0; i < values.length; i++) {
var row = values;
if (row[SEARCH_COL_IDX] == str) {
var INT_R = i+1
var values1 = [[formS.getRange("C12").getValue(),
formS.getRange("C15").getValue(),
formS.getRange("C18").getValue(),
formS.getRange("C21").getValue(),
formS.getRange("C24").getValue(),
formS.getRange("F9").getValue(),
formS.getRange("F12").getValue(),
formS.getRange("F15").getValue(),
formS.getRange("F18").getValue(),
formS.getRange("F21").getValue()]];
dataS.getRange(INT_R, 2, 1, 10).setValues(values1);
SpreadsheetApp.getUi().alert(' "Claim Log Updated "');
} }}
//----------------------------------------
function Delete() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formS = ss.getSheetByName("Form"); //Form Sheet
var dataS= ss.getSheetByName("Data"); //Data Sheet
var ui = SpreadsheetApp.getUi();
var response = ui.alert('Delete ?',ui.ButtonSet.YES_NO);
// Process the user's response.
if (response == ui.Button.YES) {
var str = formS.getRange("D7").getValue();
var values = ss.getSheetByName("Data").getDataRange().getValues();
for (var i = 0; i < values.length; i++) {
var row = values;if (row[SEARCH_COL_IDX] == str) {
var INT_R= i+1
dataS.deleteRow(INT_R) ;
ClearCell();
}}} }
//------------------------------------------
function sendMailEdit(e){
if (e.range.columnStart != 5 || e.value != "Terry DeCoteau") return;
const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,11).getValues();
let ID = rData[0][0];
let POLICY = rData[0][3];
let DOL = new Date(rData[0][6]).toLocaleDateString("en-US");
let ISD = rData[0][2];
let ISDVEH = rData[0][8];
let TP = rData[0][3];
let TPVEH = rData[0][9];
let now = new Date().toLocaleString("en-US");
let msg = "Claim ID " + ID + " Loss Date--(" + DOL + ") Policy--(" + POLICY + ") Insured--(" + ISD + ") Isd Vehicle--(" + ISDVEH + ") Third Party--(" + TP + ") TP Vehicle--(" + TPVEH + ") Assigned to you at " + now;
Logger.log(msg);
GmailApp.sendEmail("t<Mod Edit - Personal email redacted), "Claim Assignment Notification", msg)
}
My Script below
// Clear form
function ClearCell() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formS = ss.getSheetByName("Form"); //Form Sheet
var rangesToClear = ["C9", "C12", "C15", "C18", "C21", "D7", "F9", "F12", "F15", "F18", "F21", "C24" ];
for (var i=0; i<rangesToClear.length; i++) {
formS.getRange(rangesToClear).clearContent();
}
}
//-------------------------------------------------
//Input Values
function SubmitData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formS = ss.getSheetByName('Form'); //Data entry Sheet
var dataS = ss.getSheetByName("Data"); //Data Sheet
var values = [[formS.getRange("C12").getValue(),
formS.getRange("C15").getValue(),
formS.getRange("C18").getValue(),
formS.getRange("C21").getValue(),
formS.getRange("C24").getValue(),
formS.getRange("F9").getValue(),
formS.getRange("F12").getValue(),
formS.getRange("F15").getValue(),
formS.getRange("F18").getValue(),
formS.getRange("F21").getValue()]];
dataS.getRange(dataS.getLastRow()+1, 2, 1, 10).setValues(values);
ClearCell();
}
//------------------------------------------------------------------
var SEARCH_COL_IDX = 0;
function Search() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formS = ss.getSheetByName("Form"); //Form Sheet
var str = formS.getRange("D7").getValue();
var values= ss.getSheetByName("Data").getDataRange().getValues();
for (var i = 0; i < values.length; i++) {
var row = values;
if (row[SEARCH_COL_IDX] == str) {
formS.getRange("C9").setValue(row[0]) ;
formS.getRange("C12").setValue(row[1]);
formS.getRange("C15").setValue(row[2]);
formS.getRange("C18").setValue(row[3]);
formS.getRange("C21").setValue(row[4]);
formS.getRange("C24").setValue(row[5]);
formS.getRange("F9").setValue(row[6]);
formS.getRange("F12").setValue(row[7]);
formS.getRange("F15").setValue(row[8]);
formS.getRange("F18").setValue(row[9]);
formS.getRange("F21").setValue(row[10]);
} }}
//--------------------------------------------------
function Update() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formS =ss.getSheetByName("Form"); //Form Sheet
var dataS = ss.getSheetByName("Data"); //Data Sheet
var str = formS.getRange("D7").getValue();
var values = ss.getSheetByName("Data").getDataRange().getValues();
for (var i = 0; i < values.length; i++) {
var row = values;
if (row[SEARCH_COL_IDX] == str) {
var INT_R = i+1
var values1 = [[formS.getRange("C12").getValue(),
formS.getRange("C15").getValue(),
formS.getRange("C18").getValue(),
formS.getRange("C21").getValue(),
formS.getRange("C24").getValue(),
formS.getRange("F9").getValue(),
formS.getRange("F12").getValue(),
formS.getRange("F15").getValue(),
formS.getRange("F18").getValue(),
formS.getRange("F21").getValue()]];
dataS.getRange(INT_R, 2, 1, 10).setValues(values1);
SpreadsheetApp.getUi().alert(' "Claim Log Updated "');
} }}
//----------------------------------------
function Delete() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formS = ss.getSheetByName("Form"); //Form Sheet
var dataS= ss.getSheetByName("Data"); //Data Sheet
var ui = SpreadsheetApp.getUi();
var response = ui.alert('Delete ?',ui.ButtonSet.YES_NO);
// Process the user's response.
if (response == ui.Button.YES) {
var str = formS.getRange("D7").getValue();
var values = ss.getSheetByName("Data").getDataRange().getValues();
for (var i = 0; i < values.length; i++) {
var row = values;if (row[SEARCH_COL_IDX] == str) {
var INT_R= i+1
dataS.deleteRow(INT_R) ;
ClearCell();
}}} }
//------------------------------------------
function sendMailEdit(e){
if (e.range.columnStart != 5 || e.value != "Terry DeCoteau") return;
const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,11).getValues();
let ID = rData[0][0];
let POLICY = rData[0][3];
let DOL = new Date(rData[0][6]).toLocaleDateString("en-US");
let ISD = rData[0][2];
let ISDVEH = rData[0][8];
let TP = rData[0][3];
let TPVEH = rData[0][9];
let now = new Date().toLocaleString("en-US");
let msg = "Claim ID " + ID + " Loss Date--(" + DOL + ") Policy--(" + POLICY + ") Insured--(" + ISD + ") Isd Vehicle--(" + ISDVEH + ") Third Party--(" + TP + ") TP Vehicle--(" + TPVEH + ") Assigned to you at " + now;
Logger.log(msg);
GmailApp.sendEmail("t<Mod Edit - Personal email redacted), "Claim Assignment Notification", msg)
}