自动替换Google表格中的文本值

OP的原始代码最优雅（源自webapps），但是对于该任务而言过于复杂，并且也不适用于替换两个地址。

以下代码本质上是OP原始版本的简化版本。Logger.log()保留了许多语句，这些语句将使OP（如果需要和/或必要时）可以在代码的不同阶段测试值。

代码逻辑很简单。
1）将要查找和替换的地址描述为变量（var address01find和var address01replace，对地址02冲洗并重复）。OP可以根据自己的喜好对其进行编辑。

2）取得E栏的最后一行。

3）获取列E的值。

4）逐行迭代这些值，以测试等于address01find或的值address02find。如果找到该值address01replace，则address02replace分别用和替换该值。

5）循环后，setValues用于整个数据范围。大多数字段值不会更改，但是在循环中修改的那些字段将更新为修改后的值。

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

之前和之后

更新为OnEdit

此更新着眼于脚本创建数据（即E列中的街道地址）的场景。除了从onEdit知道事件范围外，代码基本相同，并且仅在地址值更改时才需要获取值，评估地址并更新数据。

注意：此脚本要求将其创建为可安装的OnEdit触发器。

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

}

更新-表格提交

Mea culpa。我设法忽略了一个事实，那就是OP表示电子表格是一封电子邮件回复表。脚本执行和API请求不会导致触发器运行。因此onEdit（无论是简单触发器还是可安装触发器）都将无法工作。

• 该脚本需要作为可安装脚本安装
• 事件类型=“在表单上提交”

调用事件对象时，它将返回“表单提交”事件数据。

注意：工作表名称现在是一个变量。否则，该代码与先前的onEdit版本几乎（但不太完全）相同。

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

}