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

问题描述

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

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

然后它说在对象表中找不到函数replaceText."

推荐答案

来自 Cameron Roberts 的回答几乎适用于所有情况(如果所有单元格都只填充字符串)，只是为了打字方便(请接受他的回答)这里是相同的脚本，在 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屋！