寻找可执行CTRL + F替换字符串的Google脚本 [英] Looking for a Google script that will perform CTRL+F replace for a string

问题描述

基本上，我有一个单元格与长，有点相似的文本字符串，我想分离特定的文本标记，以便能够在这些标记上拆分。我正在寻找的特定字符串是MHPP，我要替换为] [MHPP，所以我可以使用拆分功能分割在]。

我能够通过手动查找和替换（CTRL + F并选择替换的参数）让它工作，但我想能够脚本化，因为我不会是运行脚本，需要以便简化低信息用户的过程。

使用= replace（find（MHPP），7，] [MHPP）只找到第一个查找值，并且在整个单元格中可能存在该术语的多种用法。

有任何建议吗？我想可能有一种方法来将单元格写入字符串，并在数组中进行替换，但该过程的逻辑正在逃避我。

我不是要求整个代码。我可以激活工作表，获取范围，并从那里工作，但我只是不知道如何写特定的函数findAndReplace（），实际上将定位所有重复的字符串，并替换它们全部。

我也可以将.csv导入不同的格式，在那里运行一个函数，并将其返回到.csv，但是这还没有被证明是非常有成效的

感谢您提供的任何指导，让我走上正确的道路。

解决方案

您可以使用 replace 字符串函数在您的工作表的全局迭代中的每个单元格，在数组级别，以保持它快速和简单。
代码本身可以非常简短，如下：

  function myFunction（）{
 var sh = SpreadsheetApp.getActive（）; 
 var data = sh.getDataRange（）。getValues（）; //获取所有数据
 for（var n = 0; n  for = 0; m  if（typeof（data [n] [m]）=='string'）{//如果是字符串
 data [b] [b] [b] [b] [b] [b] [b] [b] $ b} 
} 
 sh.getDataRange（）。setValues（data）; //更新工作表值
} 
  

这可以改进以处理某些情况，其中脚本将执行两次（或更多），以防止替换，如果'] ['已经存在...我会让您管理这些详细信息。

I have looked at multiple solutions here for similar tasks, and tried them in different ways.

Essentially, I have a cells with long, somewhat similar strings of text, and I want to isolate specific text markers in order to be able to split on those markers. The specific string I am looking for is "MHPP" and I want to replace it with "][MHPP " so I can used the split function to split on the "]".

I was able to get it to work by manually Finding and Replacing (CTRL+F and selecting parameters for the replace), but I want to be able to script it because I won't be the one running the script and need to simplify the process for low-information users.

Using =replace(find("MHPP"),7,"][MHPP ") only finds the first instance of the find value, and there may be multiple usages of the term throughout the cell.

Any suggestions? I suppose there might be a way to write the cell to a string, and replace within the array, but the logic of that process is escaping me at the moment.

I'm not asking for the entire code. I can activate the sheet, get the range, and work from there, but I just don't know how to write the specific function findAndReplace() that would actually locate all repetitions of the string and replace them all.

I'm also open to importing the .csv into a different format, running a function there, and returning it back out to a .csv, but that hasn't proven to be very fruitful either in my searches.

Thanks for any guidance you can offer to get me on the right path.

解决方案

You can use the replace string function on every cell in a global iteration of your sheet, do that at array level to keep it fast and simple. The code itself can be very short and straightforward like this :

function myFunction() {
  var sh = SpreadsheetApp.getActive();
  var data = sh.getDataRange().getValues();// get all data
  for(var n=0;n<data.length;n++){
    for(var m=0;m<data[0].length;m++){
      if(typeof(data[n][m])=='string'){ // if it is a string
         data[n][m]=data[n][m].replace(/MHPP/g,'][MHPP');// use the regex replace with /g parameter meaning "globally"
      }
    }
  }
  sh.getDataRange().setValues(data);// update sheet values
}

This could be improved to take care of certain situations where the script would be executed twice (or more) to prevent replacement if '][' is already present... I'll let you manage these details.

这篇关于寻找可执行CTRL + F替换字符串的Google脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！