如何使用Google Apps脚本替换电子表格中的文字？ [英] How do I replace text in a spreadsheet with Google Apps Script?

问题描述

我想在电子表格中找到指定的文字，并用其他词语替换它。
$ b

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

然后它说在对象Sheet中找不到函数replaceText来自Cameron Roberts的回答在几乎所有情况下都能正常工作（如果所有单元格都只填充字符串）code $ <


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

推荐答案

并且只是为了方便打字（请接受他的回答），下面是一个在map函数中做了小改动的脚本：我将toString（）添加到了返回参数中。

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.

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

这篇关于如何使用Google Apps脚本替换电子表格中的文字？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！