Google Apps脚本:为包含逗号的单元格创建新行 [英] Google Apps Script: Create new rows for cells that contain commas

问题描述

背景: 用户将填写Google表单，并用逗号分隔答案.我想编写一个脚本，可以将使用的每个逗号将单元格分成几行；但是，我需要对多列进行此操作.

Background: Users will fill out a google form and separate their answers with commas. I would like to write a script that can separate cells into rows for each comma used; however, I need to do this for multiple columns.

例如，以下是我想要的概念的屏幕截图:

For example, here are screenshots of the concept that I would want:

启动方式

最终目标

这是我的实际数据集: 我的工作表

我有A到S列.我只想查看D到L列，并检查它们是否有逗号.在图片中，您可以看到信息的前三行不包含逗号.我只希望代码按原样返回这些值.在第5行中，D列到L列确实包含表示单独响应的逗号，因此我希望将这些值拆分为输出中的单独行.

到目前为止，这是我在Stackoverflow上从其他人那里获得的大部分东西.此代码有效，但仅当您指定一列时.

This is what I have so far, that I found the bulk of from someone else on Stackoverflow. This code works but only if you specify one column.

例如，在空白的Google工作表上，我写了=split_rows(Sheet1!A:S,3)，它仅检查其他工作表的第3列中的逗号.我需要一个将执行相同操作的代码，但要包含几列并拆分值.

For example, on a blank google sheet I write =split_rows(Sheet1!A:S,3), which only checks column 3 of my other sheet for commas. I need a code that will do the same but for several columns and split up the values.

function split_rows(anArray, splitColumnIndex) {
  var output = [];
  for (i in anArray){                                                 // for each row
    var current_cell = anArray[i][splitColumnIndex];                  // current cell in loop
    if (current_cell.toString().indexOf(",")>-1) {                    // if the cell in the designated column contains a comma,  
      var splitArray = current_cell.split(",");                       // split values in specified column
        for (j in splitArray){                                          // for each split cell value
         var row = anArray[i].slice(0);                                // take a copy of source row 
         row[splitColumnIndex] = alltrim(splitArray[j]);               // replace comma separated value with current split value
         output.push(row);                                             // push new row to output
       } 
    }

    else {
      var row = anArray[i].slice(0); 
      output.push(row);
    }
  }
      return output;
  }
 
function alltrim(str) {
  return str.replace(/^\s+|\s+$/g, '');
}

这是我的工作表的一个例子； https://docs.google.com/spreadsheets/d/1mgqKriAxf6-hAPNJ9Um8i9SD22TxDXVKM8llRn0sCS4/edit#gid = 0

Here is an example of my sheet; https://docs.google.com/spreadsheets/d/1mgqKriAxf6-hAPNJ9Um8i9SD22TxDXVKM8llRn0sCS4/edit#gid=0

推荐答案

• 您要从输入中获取"最终目标"的输出图像"启动方式"的图片.
• 您要将脚本用作自定义函数.
• You want to obtain the output image of "End goal" from the input image of "How it starts".
• You want to use the script as a custom function.

如果我的理解是正确的，那么该修改如何?在此修改中，我使用了以下流程.我认为您的情况有几个答案.因此，请仅考虑其中之一.

If my understanding is correct, how about this modification? In this modification, I used the following flow. I think that there are several answers for your situation. So please think of this as just one of them.

1. 检索值.
2. 当列"A"，"B"和"C"的值时，将列"B"和列"C"的值除以,.
3. 当列"B"和"C"的长度相同时，通过将每个值放入每一行来创建二维数组.
   • 从您的输入图像中，我认为列"B"和"C"的,编号相同.
1. Retrieve values.
2. When the values of column "A", "B" and "C", split the values of column "B" and column "C" by ,.
3. When the length of column "B" and "C" is the same, create 2 dimensional array by putting each value to each row.
   • From your input image, I supposed that the numbers of , for column "B" and "C" are the same.

修改后的脚本:

function split_rows(anArray) {
  var res = anArray.reduce(function(ar, e) {
    if (e.slice(0, 3).every(String)) {
      var colB = e[1].split(",").map(function(f) {return f.trim()});
      var colC = e[2].split(",").map(function(f) {return f.trim()});
      if (colB.length == colC.length) {
        var temp = colB.map(function(g, k) {
          return [e[0], g, colC[k]];
        });
        Array.prototype.push.apply(ar, temp);
      }
    }
    return ar;
  }, []);
  return res;
}

结果:

使用输入图像的值时，结果如下.

Result:

When the values of your input image are used, the result is as follows.

• 我使用您的输入和输出图像准备了此修改后的脚本.
• 尽管使用的范围是Sheet1!A:S，但是在输入图像中似乎没有从"D"列到"S"列的值.因此，在此修改后的脚本中，使用了列"A"至"C"中的值.
  • 如果要使用"D"列到"S"列中的值，可以提供有关转换结果的信息吗?
  • I prepared this modified script using your input and output images.
  • Although you use the range of Sheet1!A:S, it seems that there are no values from column "D" to "S"i n your input image. So in this modified script, the values from column "A" to "C" are used.
    • In you want to use the values from column "D" to "S", can you provide the information of about the converted result?
    • reduce()
    • map()
    • Array.prototype.push()
    • reduce()
    • map()
    • Array.prototype.push()

    如果这不是您想要的结果，请告诉我.我想修改它.那时，您可以提供所需的详细信息吗?

    If this was not the result you want, please tell me. I would like to modify it. At that time, can you provide the detail information you want?

    对于第二个问题，上述脚本已使用共享的电子表格进行了修改.

    For your 2nd question, above script was modified using your shared Spreadsheet.

    function split_rows2(anArray) {
      var res = anArray.reduce(function(ar, e) {
        var splitted = e.slice(3, 12).map(function(f) {return f.toString().split(",").map(function(g) {return g.trim()})});
        var temp = [];
        for (var j = 0; j < splitted[0].length; j++) {
          temp.push(e.slice(0, 3).concat(splitted.map(function(f) {return f[j] ? (isNaN(f[j]) ? f[j] : Number(f[j])) : ""})).concat(e.slice(12, 20)));
        }   
        Array.prototype.push.apply(ar, temp);
        return ar;
      }, []);
      return res;
    }
    

    结果:

    这篇关于Google Apps脚本:为包含逗号的单元格创建新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！