Google Sheets Script Editor
Google Sheets Script Editor
I'm attempting to activate an on edit trigger, but it only functions when I modify the cell individually. The script processes the entire row, yet the trigger remains inactive unless I change the value in that cell separately. I considered adding a utility sleep, thinking perhaps the email trigger starts before the rest of the script updates the spreadsheet. Could that be the issue? If so, how should I resolve it?
A flag or function helps identify when a manual adjustment to Handler or within the 'Data' sheet is made. You know functions. A flag acts like a variable that gets set, modified, or cleared when needed. Your scripts can rely on its value to choose the correct IF THEN ELSE route. You may adjust these variables as required.
Hello,
Thank you for your reply. I will share the links so you can gain a clearer understanding.
Work flow explanation.
Despite some challenges in following your process, I have a general understanding of the intended steps. From the end user's perspective, the workflow looks like this:
Start by opening the form.
Remove all existing values.
Input a search ID.
Retrieve the relevant customer details from a spreadsheet using that ID.
Populate the appropriate form fields with the customer information.
Enable the end user to adjust or edit as needed.
Permit the user to save those modifications.
Send the updated changes to another person.
You are working with a data entry page, but it seems editing is also occurring. The outcome is that the original spreadsheet data (stored in rows) is being replaced and an email is sent to the handler notifying them of the changes. Is this accurate?
Can you add comments to your script by including lines that describe each section or function? Particularly those related to functions?
This function resets the specified cells in the active spreadsheet.
(
Assigned to Clear Button on Sheet 'Form'
. It empties the designated fields when the Clear Button is pressed)
//-------------------------------------------------
//Details of Input Parameters
function SubmitData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formS = ss.getSheetByName('Form'); //Form 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 updates the data from the selected cells in '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 searches for the ID in the 'Data' sheet and populates the relevant cells)
//--------------------------------------------------
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'
This updates the corresponding row in the 'Data' sheet when a match is found)
//----------------------------------------
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);
// Handle user's decision.
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();
}
}
}
A flag or function helps confirm that a manual adjustment to Handler (or within the 'Data' sheet) has taken place. You are familiar with functions. A flag acts like a variable that gets set or modified when certain events happen. Scripts and functions then rely on this value to decide which conditional path to follow, which might involve additional functions. You can adjust these variables as needed to meet your needs.