带有范围的Google脚本“替换"功能 [英] Google Script 'replace' function with range

问题描述

我正在尝试在Google Spreadsheets中编写罗马数字到小数数字"功能的脚本. 到目前为止，我已经设法使用下面的代码使其工作:

I'm trying to script a "roman-number-to-decimal-number" function in Google Spreadsheets. So far I've managed to make it work using the code below:

function rom2num(inputRange) {
inputRange = inputRange.replace(/\bi\b/gi,1); //convert 'I' to '1'
  return inputRange;
}

当输入仅是一个单元格时，这将完美工作.但是，每当我尝试输入范围(A1:B2)时，都会出现错误:在对象中找不到函数替换".

This works perfectly when the input is one cell only. However, whenever I try to input a range (A1:B2) I get the error: "cannot find function replace in object".

一些我想要实现的例子:

Some examples of what I want to achieve:

Column A      | Column with function
i, ii, iii    | 1, 2, 3
Godfather II  | Godfather 2
iv, v, vi     | 4, 5, 6
Star Wars V   | Star Wars 5

我可以选择在下面的单元格中重复该功能，如Crayon所建议的那样，但是，需要重新设置格式的范围可能是50行，或者可能是100行，我宁愿不抛出50个额外的实例该功能，如果不使用它们.我希望它尽可能自动. :-)

I could just choose to repeat the function in the cells below, as Crayon suggested, however, the range that needs to be reformatted might be 50 rows, or it might be 100 rows and I prefer not throwing in 50 extra instances of the function if they're not to be used. I want it to be as automatic as possible. :-)

我知道有可能通过使用以下代码来检索有关某个单元格范围的信息，并在另一范围的单元格中返回该信息:

I know that it's possible to retrieve the information on a range of cells, and the return that information in another range of cells just by using the following code:

function copyRange(inputRange) {
return inputRange;
}

因此输入一个范围并输出相同的范围不是问题.问题是试图合并替换功能.我设法使用'toString()'函数在整个范围内更改了信息.然后，我能够使用'split(，")'将输出拆分为几列，但我不希望它分散在列中，而仅分布在行中.

So to input a range and output the same range isn't a problem. The problem is trying to incorporate the replace-function. I have managed to alter the information in the whole range using the 'toString()'-function. And then I was able to to split the output over several columns using 'split(",")' but I don't want it to spread across columns, just rows.

因此，如果我能以某种方式将范围/数组转换为字符串，然后再返回至范围/数组...

So if I somehow could convert the range/array to a string and then back to a range/array...

是否可以在不使代码过于膨胀的情况下实现这一目标? (我是新手脚本编写者，更喜欢我的脚本简短，就像上面的示例一样.)

Is it possible to achieve this without bloating the code too much? (I'm a newbie scripter and prefer my scripts short, like my example above.)

很抱歉，如果我不清楚.我习惯在这样的论坛中查找答案，但自己不问问题！

I'm sorry if i'm being unclear. I'm used to look up answers in forums like this but not asking the questions myself!

谢谢！

最终意识到事情变得有点太复杂了，所以我只是接受了蜡笔的建议，并使用了拖拽"方法.为我省去了很多麻烦.感谢您的帮助！

推荐答案

根据 https://developers.google.com/apps-script/execution_custom_functions 您的函数只能更改调用该函数的单元格中的数据，因此无论如何您都不应该尝试更改多个单元格.您应该具有对该单元格执行替换的功能，然后将该功能应用于多个单元格.

according to https://developers.google.com/apps-script/execution_custom_functions your function can only alter data in the cell the function is invoked in, so you shouldn't be trying to change multiple cells anyways. You should have the function do the replace for that cell and then apply the function to multiple cells.

例如

A  B
i  =rom2num(A1)
i
i
i

这将使B1等于"1".现在，将鼠标悬停在B1上，右下角是点，向下拖动到B4，您将看到该函数已应用于B2-4行.

This will make B1 equal to "1". Now hold your mouse over B1 and on the bottom-right is the dot point, drag down to B4 and you will see the function applied to rows B2-4.

鉴于您的评论和问题的其他信息，我想我知道您要做什么.基本上，您想利用溢出值到单元格来有效地作用于多个单元格.是的，这可以通过返回一个double数组来实现.您的新示例显示了将罗马数字转换为实际数字.比简单地执行.replace稍微复杂一些，但是一旦弄清楚如何使这些值起作用，您就应该能够弄清楚如何将这些东西变成您要尝试的东西！因此，这是执行此操作的方法:

In light of your comment and additional info in your question, I think I know what you're trying to do. Basically you want to exploit overflowing the value to the cells to effectively act on multiple cells. So yeah, this is possible by returning a double array. Your new example shows converting roman numerals to actual numbers..well it's a bit more complex than simply doing a .replace but you should be able to figure out how to work that stuff into what you're trying to do, once you figure out how to get the values to work on! So here is how to do that:

function rom2num(inputRange) {
  var cells = [];
  for (var i=0;i<inputRange.length;i++) {
    cells[i] = [String(inputRange[i]).replace(/i/gi,1)];
  }
  return cells;
}

此示例在此范围内的每一行中进行迭代，并将"i"替换为"1".然后，它返回值的双精度数组，例如，如果您有

This example here iterates through each row in the range and simply replaces "i" with "1". It then returns a double array of the values, so for instance if you have

A              B
i, ii, iii     =rom2num(A1:A4)
Godfather II
iv, v, vi
Star Wars V

该函数将返回并溢出单元格，如下所示:

The function will return and overflow the cells to look like this:

A               B
i, ii, iii      1, 11, 111
Godfather II    Godfather 11
iv, v, vi       1v, v, v1
Star Wars V     Star Wars V

就像我说的那样，您实际上要做的不只是.replace(转到我给您的链接)，还要将罗马数字实际转换为数字，但是就您的实际问题而言，这应该可以做到.

Like I said, you'll have to do more than just .replace (go to the link I gave you) to actually convert the roman numbers to numberals, but as far as to your actual question, this should do it.

修改2:

实际上，我继续将罗马数字转换功能并入其中，因为我想看到它.因此，我认为最好与您分享最终的脚本:

I actually went ahead and incorporated the roman numeral conversion function into it because I wanted to see it. So I thought may as well share with you the final script:

function rom2num(inputRange) {
  var cells = [];
  for (var i=0;i<inputRange.length;i++) {
    cells[i] = [
      String(inputRange[i]).replace(/\b([ivxcldm]+)\b/gi,function(p,p1) {
        return deromanize(p1); 
      })
    ];
  }
  return cells;
}

function deromanize (str) {
    var str = str.toUpperCase(),
        validator = /^M*(?:D?C{0,3}|C[MD])(?:L?X{0,3}|X[CL])(?:V?I{0,3}|I[XV])$/,
        token = /[MDLV]|C[MD]?|X[CL]?|I[XV]?/g,
        key = {M:1000,CM:900,D:500,CD:400,C:100,XC:90,L:50,XL:40,X:10,IX:9,V:5,IV:4,I:1},
        num = 0, m;
    if (!(str && validator.test(str)))
        return false;
    while (m = token.exec(str))
        num += key[m[0]];
    return num;
}

与之前相同...列A具有要操作的内容，将=rom2num(A1:A4)放在B列的第一个单元格中.这会将转换后的罗马数字放入B列的每个单元格中，并与列A单元格.但是请记住，实际上并没有更改单个An> Bn单元.它实际上是在获取您指定的范围并将结果溢出到多个单元中.它可以有效地执行您想要的操作，但这不是一回事.

Same thing as before...column A has the stuff you want to act on, put your =rom2num(A1:A4) in first cell of column B. This will put the converted roman numerals into each cell of column B, lined up with column A cells. But remember, it's not actually changing individual An > Bn cells..it's really grabbing the range you specify and overflowing the results into multiple cells. It effectively does what you want, but it's not exactly the same thing.

这篇关于带有范围的Google脚本“替换"功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！