The OP's original code was most elegant (sourced from webapps) but over complicated for the task, and also not adapted to the replacing two addresses.
The following code is essentially a cut-down version of the OP original. A number of Logger.log() statements have been kept which will allow the OP (if &/or when necessary) to test values at different stages of the code.
The code logic is straightforward.
1) the addresses to be found and replaced are described as variables (var address01find and var address01replace, rinse and repeat for address02). The OP can edit this according to their taste.
2) get the last row in column E.
3) get the values for column E.
4) iterate through the values, row-by-row, testing for a value equal to either address01find or address02find. If the value is found, replace the value with address01replace and address02replace respectively.
5) after the loop, setValues for the entire data range. Most field values won't change, but those fields that were modified during the loop, will be updated to the revised value.
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var MenuEntries = [{
name: "Replace addresses",
functionName: "replaceaddresss"
}];
ss.addMenu("Address Update", MenuEntries);
};
function replaceaddresss() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
//Logger.log("DEBUG: sheet name: "+sheet.getSheetName());//DEBUG
//Logger.log("DEBUG: range: "+range.getA1Notation());//DEBUG
var Evals = ss.getRange("E1:E").getValues();
var Elast = Evals.filter(String).length;
//Logger.log("DEBUG: last row in E: "+Elast);//DEBUG
var range = sheet.getRange(1, 5, Elast)
//Logger.log("DEBUG: range: "+range.getA1Notation());//DEBUG
var columnValues = range.getValues();
var address01find = "Full Address 123, Tokyo, Japan";
var address01replace = "Place Name No.1";
var address02find = "Short Address 123, Tokyo, Japan";
var address02replace = "Place Name No.2";
//Logger.log("DEBUG: address #1: find: "+address01find+", replace with: "+address01replace);//DEBUG
//Logger.log("DEBUG: address #2: find: "+address02find+", replace with: "+address02replace);//DEBUG
for (i = 0; i < Elast; i++) {
if (columnValues[i][0] === address01find) {
columnValues[i][0] = address01replace;
}
if (columnValues[i][0] === address02find) {
columnValues[i][0] = address02replace;
}
};
range.setValues(columnValues);
}
Before and after
UPDATE for OnEdit
This update looks at the scenario where data (i.e. the street address in Column E) is created by a script.
The code is essentially the same except that the event range is known from onEdit, and it is only necessary to get the values, evaluate the addresses, and update the data IF the address value was changed.
Note: this script requires that it be created as an Installable OnEdit Trigger.
function oneditemailupdate(e) {
// set up spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
// setup triggerdetector
var trigger = 0;
// User variables
// identify the column to watch
var addresscol = 5; // = Column E - this is the column to watch
// Setup the old and new addresses
var address01find = "Full Address 123, Tokyo, Japan";
var address01replace = "Place Name No.1";
var address02find = "Short Address 123, Tokyo, Japan";
var address02replace = "Place Name No.2";
// list onedit values for debug reporting and other use
var debug_e = {
authMode: e.authMode,
range: e.range.getA1Notation(),
source: e.source.getId(),
user: e.user,
value: e.value,
oldValue: e.oldValue
};
//Logger.log("DEBUG: AuthMode: "+debug_e.authMode);// DEBUG
//Logger.log("DEBUG: Range: "+debug_e.range);// DEBUG
//Logger.log("DEBUG: Source: "+debug_e.source);// DEBUG
//Logger.log("DEBUG: User: "+debug_e.user);// DEBUG
//Logger.log("DEBUG: user email"+debug_e.user.getEmail());// DEBUG
//Logger.log("DEBUG: Value: "+debug_e.value);// DEBUG
//Logger.log("DEBUG: Old value: "+debug_e.oldValue);// DEBUG
//Logger.log("DEBUG: AuthMode: "+debug_e.authMode+", Range: "+debug_e.range+", source: "+debug_e.source+", user: "+debug_e.user+", value: "+debug_e.value+", old value: "+debug_e.oldValue);//DEBUG
// get the values for the range that was edited (e.range)
var emailvalues = e.range.getValues();
// evaluate email address value#1 and change value if necessary
if (emailvalues[0][4] === address01find) {
emailvalues[0][4] = address01replace;
// set the trigger so that the range value can be updated
trigger = 1;
//Logger.log("DEBUG: Updated address01");//DEBUG
}
// evaluate email address value#2 and change value if necessary
if (emailvalues[0][4] === address02find) {
emailvalues[0][4] = address02replace;
// set the trigger so that the range value can be updated
trigger = 1;
//Logger.log("DEBUG: Updated address02");//DEBUG
}
// if trigger value has been set to one, then update the values
// Logger.log("DEBUG: Trigger value is "+trigger);//DEBUG
if (trigger == 1) {
e.range.setValues(emailvalues);
}
}
UPDATE - FORM SUBMIT
Mea culpa. I managed to ignore the fact that the OP said that the spreadsheet was an email response sheet. Script executions and API requests do not cause triggers to run. So onEdit (whether as a Simple or Installable trigger) will not work.
- The script needs to be installed as an Installable Script
- The event type = "On form Submit"
This will return 'Form Submit' event data when the event object is called.
Note: the sheet name is now a variable. Otherwise the code is almost (but not quite) identical to the previous onEdit version.
function addressupdate(e) {
// set up spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formresponsesheet = "Form Responses 2";
var sheet = ss.getSheetByName(formresponsesheet);
// setup changetriggerdetector
var trigger = 0;
// User variables
// identify the column to watch
var addresscol = 5; // = Column E - this is the column to watch
// Setup the old and new addresses
var address01find = "Full Address 123, Tokyo, Japan";
var address01replace = "Place Name No.1";
var address02find = "Short Address 123, Tokyo, Japan";
var address02replace = "Place Name No.2";
// list onedit values for debug reporting and other use
var debug_e = {
authMode: e.authMode,
range: e.range.getA1Notation(),
namedValues: e.namedValues,
triggerUid: e.triggerUid,
values: e.values
};
//Logger.log("DEBUG: AuthMode: "+debug_e.authMode);// DEBUG
//Logger.log("DEBUG: Range: "+debug_e.range);// DEBUG
//Logger.log("DEBUG: named ValuesSource: "+debug_e.namedValues);// DEBUG
//Logger.log("DEBUG: triggerUid: "+debug_e.triggerUid);// DEBUG
//Logger.log("DEBUG: values: "+debug_e.values);// DEBUG
// get the values for the range that was created (e.range)
var emailvalues = e.range.getValues();
// evaluate email address value#1 and change value if necessary
if (emailvalues[0][4] === address01find) {
emailvalues[0][4] = address01replace;
// set the trigger so that the range value can be updated
trigger = 1;
//Logger.log("DEBUG: Updated address01");//DEBUG
}
// evaluate email address value#2 and change value if necessary
if (emailvalues[0][4] === address02find) {
emailvalues[0][4] = address02replace;
// set the trigger so that the range value can be updated
trigger = 1;
//Logger.log("DEBUG: Updated address02");//DEBUG
}
// if trigger value has been set to one, then update the values
// Logger.log("DEBUG: Trigger value is "+trigger);//DEBUG
if (trigger == 1) {
e.range.setValues(emailvalues);
}
}
