javascript 如何使用 Google Apps 脚本替换电子表格中的文本？

How do I replace text in a spreadsheet with Google Apps Script?

提问by Yuri

I wanna find a specified text in spreadsheet and replace it with other word. I tried like this.

我想在电子表格中找到指定的文本并将其替换为其他单词。我是这样试的。

sheet = SpreadsheetApp.getActiveSheet()
sheet.replaceText ('ga: sessions','Sessions');

Then it says "Cannot find function replaceText in object Sheet."

然后它说 "Cannot find function replaceText in object Sheet."

回答by Cameron Roberts

You can achieve this by reading in all values in the sheet (as an Array), Looping over the array, replacing the values, then writing the entire array back to the sheet.

您可以通过读取工作表中的所有值（作为数组）、循环遍历数组、替换值，然后将整个数组写回工作表来实现这一点。

There is a basic example below. You may need to modify this if your sheet contains formulas, or if you want to replace the text more than once in a given cell.

下面有一个基本的例子。如果您的工作表包含公式，或者您想在给定单元格中多次替换文本，您可能需要修改此设置。

Note that any changes made to sheet between the time you read in and write out the data will be lost, and potentially could break this example.

请注意，在您读入和写出数据之间对工作表所做的任何更改都将丢失，并且可能会破坏此示例。

function testReplaceInSheet(){
    var sheet = SpreadsheetApp.getActiveSheet()
    replaceInSheet(sheet,'ga: sessions','Sessions');
}

function replaceInSheet(sheet, to_replace, replace_with) {
  //get the current data range values as an array
  var values = sheet.getDataRange().getValues();

  //loop over the rows in the array
  for(var row in values){

    //use Array.map to execute a replace call on each of the cells in the row.
    var replaced_values = values[row].map(function(original_value){
      return original_value.replace(to_replace,replace_with);
    });

    //replace the original row values with the replaced values
    values[row] = replaced_values;
  }

  //write the updated values to the sheet
  sheet.getDataRange().setValues(values);
}

Documentation:

文档：

Array.map: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/map

Array.map：https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/map

String.replace: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/replace

String.replace：https: //developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/replace

Sheet.getDataRange: https://developers.google.com/apps-script/reference/spreadsheet/sheet#getDataRange()

Sheet.getDataRange：https: //developers.google.com/apps-script/reference/spreadsheet/sheet#getDataRange()

range.GetValues: https://developers.google.com/apps-script/reference/spreadsheet/range#getValues()

range.GetValues：https: //developers.google.com/apps-script/reference/spreadsheet/range#getValues()

回答by Serge insas

from Cameron Roberts answer that works in almost every cases (if all cells are filled with strings only) and just for typing convenience (please accept his answer) here is the same script with a small change in the map function : I added toString() to the return argument.

来自 Cameron Roberts 的回答几乎适用于所有情况（如果所有单元格都只填充字符串）并且只是为了打字方便（请接受他的回答）这里是相同的脚本，但在地图函数中略有变化：我添加了 toString()到返回参数。

function testReplaceInSheet(){
    var sheet = SpreadsheetApp.getActiveSheet()
    replaceInSheet(sheet,'values','test');
}

function replaceInSheet(sheet, to_replace, replace_with) {
  //get the current data range values as an array
  var values = sheet.getDataRange().getValues();

  //loop over the rows in the array
  for(var row in values){

    //use Array.map to execute a replace call on each of the cells in the row.
    var replaced_values = values[row].map(function(original_value){
      return original_value.toString().replace(to_replace,replace_with);
    });

    //replace the original row values with the replaced values
    values[row] = replaced_values;
  }

  //write the updated values to the sheet
  sheet.getDataRange().setValues(values);
}

回答by Michael Aaron Safyan

The getActiveSheet() function returns a Sheetobject whose functions are documented at the link. It would be awesome if there were an API like that, but currently you need to delete and insert (rows, columns, or the contents of a specific range) in order to be able to do that sort of replacement.

getActiveSheet() 函数返回一个Sheet对象，其功能在链接中记录。如果有这样的 API 那就太棒了，但目前您需要删除和插入（行、列或特定范围的内容）才能进行这种替换。

回答by Mark

Try something like this:

尝试这样的事情：

var sheet  = SpreadsheetApp.getActiveSheet();
var cell = sheet.getRange("A1:A1");
cell.setValue('ga: sessions','Sessions');

回答by Chris N-L

This is what worked for me, and it's simple.

这对我有用，而且很简单。

function changevalues() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('NameOfSheet');
    var rangecells = sheet.getRange('DesiredRange');
    rangecells.setValue('DesiredValue')
}

回答by Sergey Dirin

The selected answer does not work with the data containing Date cells. Actually it changes these cells to String and this is not expected behavior.

所选答案不适用于包含日期单元格的数据。实际上，它将这些单元格更改为 String，这不是预期的行为。

I modified the code to check if the value is number and change only string values.

我修改了代码以检查值是否为数字并仅更改字符串值。

function testReplaceInSheet(){
    var sheet = SpreadsheetApp.getActiveSheet()
    replaceInSheet(sheet,'values','test');
}

function replaceInSheet(sheet, to_replace, replace_with) {
  //get the current data range values as an array
  var values = sheet.getDataRange().getValues();

  //loop over the rows in the array
  for(var row in values){

    //use Array.map to execute a replace call on each of the cells in the row.
    var replaced_values = values[row].map(function(original_value){
      if (typeof original_value != 'string') {
        return original_value;
      } else {
        return original_value.replace(to_replace,replace_with);
      }
    });

    //replace the original row values with the replaced values
    values[row] = replaced_values;
  }

  //write the updated values to the sheet
  sheet.getDataRange().setValues(values);
}

Have a nice day

祝你今天过得愉快