How to write a Google Sheets script
How to write a Google Sheets script
I am trying to start an edit trigger, but it only works when I change the value in one specific cell. The script runs and takes a whole row of information into the spreadsheet, but the edit trigger doesn't fire up when that happens. It only starts when I click on that single cell separately. I tried adding a utility sleep to see if the email trigger was running before the rest of the script finished updating the sheet (maybe that's why it didn't work). Could this be the cause? If so, how do I fix it? 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) }
You need a flag or function to know if something changed in the handler (or any part of the sheet data). You get functions. A flag is just a variable that gets set and/or changed when things happen. Then your scripts and functions look at that value to decide which path to take. That path could lead to other functions. You can change, clear or set those variables as needed for what you need. = = = = These links will help you start: https://codewithcurt.com/how-to-use-the-...le-sheets/ https://itectec.com/webapp/google-s...di...-selected/ As you...
I don't know if I'm getting this right. But I'm curious why we are clearing the values of cells like C9, C12, and so on, including D7. When we run the code, it shows a list of cells to clear. Then it opens a popup window that asks for input: "Delete ?" with two buttons, YES or NO. If you click YES, what should happen? Is the default value supposed to be yes, no, or maybe something else? Also, why is there an underscore in the word "YES_NO"?
Good Day, thanks for your reply. I will send the links so you can see things better. DATA ENTRY PAGE: https://docs.google.com/spreadsheets/d/1...sp=sharing
Work flow. Even though I am still having trouble following your process and script, I can tell what actions are supposed to happen. Here is how things work from the end user's point of view: First, open the form. Second, wipe out all the current values so we start fresh. Third, type in a search ID. Then, use that search ID to pull relevant customer info from a spreadsheet I have saved. Next, put those customer details into the right spots on the form. After that, let them fix things if they need to. Also, let them save their edits before moving on. Finally, send a message to another person about what changed. You are working with a DATA ENTRY PAGE, but it looks like you can also edit and change things too. When they do this editing, the original spreadsheet data gets replaced or overwritten in that row, and an email is sent to the handler telling them everything has been updated. Is that correct? Can you add some lines to your script so I know exactly what each part does, especially about the editing features?
Here is a fresh look at your code, using simpler words and keeping all the "I" parts exactly as they are:
Clearing the form cells
The ClearCell function starts by getting the sheet named "Form". Then it lists specific boxes on that sheet like C9, C12, F9, etc. It runs through each box one by one and deletes whatever is inside them. This makes sure every single field in the Form gets wiped clean when you hit Clear Button.
Saving the data
The SubmitData function grabs the active spreadsheet and finds two sheets called "Form" (where users type things) and "Data" (where info stays). It takes all the numbers from specific boxes on the Form sheet, puts them into a list, and then pastes that whole list into one cell in the Data sheet. Finally, it calls ClearCell to make sure there are no hidden marks left over.
Finding a matching ID
The Search function opens the active spreadsheet again and gets the value from box D7 on the Form sheet. It also grabs everything from the Data sheet. Then it goes through every single number in that list and checks if any of them match what was found in the form. If they do, it starts filling up other boxes on the Form with the matching numbers from the Data sheet. This lets you quickly find which old record belongs to the new one.
Updating a row
The Update function follows the same pattern as Search but instead of just copying over, it puts everything into a specific row number (like Row 12) in the Data sheet. It also shows a small popup message saying "Claim Log Updated" so you know it worked.
Deleting an entry
The Delete function starts by asking a question on your computer screen: "Delete ? Yes / No?". If you say yes, it gets the value from box D7 again and looks through all the rows in the Data sheet to find the matching one. It then deletes that specific row so the whole thing is gone from the list.
Sending an email
The sendMailEdit function checks if a cell named "Handler" was changed to the name "Terry DeCoteau". If it matches, it grabs the current data and builds up a long message with all the details like Policy ID, Vehicle info, dates, etc. It then uses Google's built-in email tool to send that big message directly to your inbox at [email protected].
You will need a flag or function to tell you when something changed in the Manual section (or anywhere inside the sheet called Data). You know how functions work. A flag is just a variable that gets set or updated when things happen. Then your scripts and functions look at the value of that flag to decide which path to follow. That path might include other functions too. You can change and clear those variables as needed for your needs.
Here are some links to help you get started:
https://codewithcurt.com/how-to-use-the-...le-sheets/
https://itectec.com/webapp/google-s...di...-selected/
As you learn, just "Google" it for similar examples. There are usually several ways to apply the needed triggers. What makes everything a little more tricky is that there are at least two things about emailing handlers:
1) A new policy has been made and the newly assigned handler needs to be emailed. (It only seems to work now if you are that Handler - right?)
2) An existing policy changed, like maybe the handler changing, and you need a notification whether you are the new handler or not - correct? An email for a Handler change is one thing. An email for any Sheet 'Data' change is another.... Map out the logic first.