时间戳

首页 > 解决方案 > 自动替换 Google 表格中的文本值

google-apps-script - 自动替换 Google 表格中的文本值

问题描述

我已经为 Google 表格自动化设置了一封电子邮件，其中来自特定电子邮件的内容会自动成为 Google 表格中的新行。但是其中一个值是完整地址（它始终是仅有的两个地址之一），我希望它自动更改为地名。所以“1号地址”到“1号地名”，“2号地址”到“2号地名”。文本始终在 E 列中，并添加到新行中。

这是我尝试使用的：

function onOpen() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("e1:e10");
var to_replace = "full long address";
var replace_with = "place name";
replaceInSheet(sheet,range, to_replace, replace_with);
}

function replaceInSheet(sheet, range, to_replace, replace_with) {
//Confirm
var ui = SpreadsheetApp.getUi(); 
var spread = SpreadsheetApp.getActiveSpreadsheet();

var result = ui.alert(
 "Will update " + to_replace + " to " + replace_with + " ",
 'Are you sure you want to continue?',
  ui.ButtonSet.YES_NO);

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

// User clicked "Yes".
spread.toast("Will update " + to_replace + " to " + replace_with + " ", "ALERT");

var data  = range.getValues();

var oldValue="";
var newValue="";
var cellsChanged = 0;

for (var row=0; row<data.length; row++) {
  for (var item=0; item<data[row].length; item++) {
    oldValue = data[row][item];
    newValue = data[row][item].replace(to_replace, replace_with);
    if (oldValue!=newValue)
    {
      cellsChanged++;
      data[row][item] = newValue;
    }
  }
}
range.setValues(data);
spread.toast(cellsChanged + " cells changed", "STATUS");
}
else {
// User clicked "No" or X in the title bar.
spread.toast("No action taken", "ABANDONED");
}
}

标签： google-apps-scriptreplacegoogle-sheets

解决方案

OP 的原始代码最优雅（来自webapps），但任务过于复杂，也不适合替换两个地址。

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

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

2）获取E列的最后一行。

3) 获取列 E 的值。

4) 逐行遍历值，测试等于address01findor的值address02find。如果找到该值，则分别用address01replace和替换该值address02replace。

5) 循环后，为整个数据范围设置值。大多数字段值不会更改，但在循环期间修改的那些字段将更新为修改后的值。

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

}

更新 - 表格提交

过失。我设法忽略了 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);
    }

}

推荐阅读