[SOLVED] Google Sheets Script Editor

[terry decoteau]

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)
}

 

[Ralston18]

Not sure that I am correctly following the process/logic.

But I am wondering about D7 being cleared in

var rangesToClear = ["C9", "C12", "C15", "C18", "C21", "D7", "F9", "F12", "F15", "F18", "F21", "C24" ];

Then looking at:

var ui = SpreadsheetApp.getUi();
var response = ui.alert('Delete ?',ui.ButtonSet.YES_NO);

// Process the user's response.
if (response == ui.Button.YES) {

What is or should be the default value for D7? (YES, NO, ?)

Also wondering about the underscore in YES_NO

 

[terry decoteau]

Good Day, Thanks for the response, I will share the links so you can get a better idea
DATA ENTRY PAGE https://docs.google.com/spreadsheets/d/1yymCPIAXWkAXoMRoc9D4MadtxP6hV2cOJ1USyQwVChs/edit?usp=sharing

 

[Ralston18]

Work flow.

Although I am still having some difficulty following your process and script I do have a sense of the intended actions.

The work flow as I understand it from the end user's view:

Open the form.
Clear all values.
Enter a search ID.
Using that search ID get the relevant customer information from a source spreadsheet.
Fill in the applicable form fields with that customer information.
Allow the end user to make changes/edits as necessary.
Allow the end user to save those changes.
Email the changes to someone else.

You are using DATA ENTRY PAGE but it also appears that editing is being done as well. And a result of that editing is the the original spreadsheet data (stored in row) is overwritten/replaced and that an email is also being sent to the handler informing the handler of the changes. Is that correct?

Are you able to "comment" your script by adding some lines to document what each section/function etc. is doing?

Especially the functions.

 

[terry decoteau]

// 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", "B28", "F21", "C24" ];
for (var i=0; i<rangesToClear.length; i++) {
formS.getRange(rangesToClear).clearContent();
}
} (Assigned to Clear Button on Sheet 'Form' . This Clears the Fields on the Sheet ' Form 'when press the Clear Button )
//-------------------------------------------------
//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(),
formS.getRange("B28").getValue()]];
dataS.getRange(dataS.getLastRow()+1, 2, 1, 11).setValues(values);
ClearCell();
} ( Assigned to Save Button on Sheet 'Form'. This writes info from selected cells on 'form' to 'Data' )
//------------------------------------------------------------------

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]);
} }}( Assigned to Search Button on Sheet 'Form' This Button Searches the ID on Sheet ' Form ', And Returns the row on Sheet 'Data' with the matching ID, in the relevant fields)
//--------------------------------------------------

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(),
formS.getRange("B28").getValue()]];

dataS.getRange(INT_R, 2, 1, 11).setValues(values1);
SpreadsheetApp.getUi().alert(' "Claim Log Updated "');
} }}

( Assigned to Update Button on Sheet 'Form'. Writes to the row on Sheet 'Data' matching the ID number on Sheet 'Form' )

//----------------------------------------

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();
}}} }
( Assigned to Delete Button on Sheet 'Form' .. to delete Data on Sheet 'Data' Matching the ID on Sheet 'Form' )
//------------------------------------------

function sendMailEdit(e){
if (e.range.columnStart != 5 || e.value != "Terry DeCoteau") return;
const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,12).getValues();
let ID = rData[0][0];
let POLICY = rData[0][1];
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 RECOVERY = rData[0][11];
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 + ") RECOVERY--(" + RECOVERY + ") Assigned to you at " + now;
Logger.log(msg);
GmailApp.sendEmail("MODEDIT@MAIL.com", "Claim Assignment Notification", msg)
}
(This code was set up to send an email whenever the Handler column on Sheet 'Data' ( Column E) is changed to Terry Decoteau . But I have now learnt that an onedit trigger only works when somone manually changes information on the sheet. So i was advised that i need to use an installable trigger. But i dont know anything about how to do that here.

 

[Ralston18]

You will need a flag or function of some sort to determine that a manual change to Handler (or anywhere within Sheet 'Data') has occurred.

You understand functions.

A flag is simply a variable that is set and/or changed when something occurs. Then your scripts and functions use the value of the variable to determine which logical IF THEN ELSE path to execute. That path may include other functions.

You can set and change and clear those variables as necessary for the requirements.

= = = =

The following links should get you started:

https://codewithcurt.com/how-to-use-the-onedit-function-on-google-sheets/

https://itectec.com/webapp/google-s...dit-trigger-when-specific-cells-are-selected/

As you learn, simply research ("Google") for similar examples.

For the most part there are likely several ways of applying the necessary triggers.

What makes it all a bit more tricky is that there are at least two requirements with regards to emailing handlers:

1) A new policy has been created and the newly assigned handler must be emailed. (Apparently only now executed if that Handler is you - correct?)

2) An existing policy has changed (including a change of handler perhaps) and you must be notified whether you are the new handler or not - correct?

An email for Handler changes is one thing. An email for any Sheet 'Data' change is another....

Map out the logic first.