如何根据一个单元格中的值是否以逗号分隔或Google表格中的换行符将单行拆分为多行? [英] How to split single rows into multiple rows depending on whether one cell has values separated by commas or new lines in Google Sheet?

问题描述

电子表格中有些单元格的单元格内容用逗号或换行符分隔.

The spreadsheet has some cells with cell contents that are separated by commas, or a new line.

123、876、456

123, 876, 456

"C"列是确定是否应将一行拆分为多行的列.

Column "C" is the column that determines whether a row should be split up into multiple rows.

示例电子表格

来自表单的信息进入表单提交"页面.

Information from the Form goes into the "Form Submission" page.

我们必须满足一种特定格式，才能提交给我们的报告跟踪软件，该软件要求将问题编号(在C列中找到)与在A:B，D:J列中找到的信息分成各自的行.保持不变(请参见预期结果表).

We have a specific format that we must meet to submit to our report tracking software that requires the issue numbers (found in Column C) to be separated into their own rows with the information found in Columns A:B, D:J remaining the same (see Desired Outcome sheet).

我找到了

I found a similar question and we implemented it into our Google Sheets.

此脚本要求在单独的工作表上将函数=result('FormSubmission'!A2:J)放置在我们希望显示数据的第一列/行中(请参阅当前结果"工作表，单元格A2.)

This script requires, on a separate sheet, the function =result('FormSubmission'!A2:J) to be placed in the first column / row that we wish the data to be displayed (see "Current Outcome" sheet, Cell A2.)

这是我们正在使用的编码:

Here is the coding that we are using:

function result(range) {
  var output2 = [];
  for(var i = 0, iLen = range.length; i < iLen; i++) {
    var s = range[i][2].split("\n");    
    for(var j = 0*/, jLen = s.length; j < jLen; j++) {
      var output1 = []; 
      for(var k = 0, kLen = range[0].length; k < kLen; k++) {
        if(k == 2) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
} 

function results(range) {
  var output2 = [];
  for(var i = 0 /, iLen = range.length; i < iLen; i++) {
    var s = range[i][2].split(",");    
    for(var j = 0 /, jLen = s.length; j < jLen; j++) {
      var output1 = []/; 
      for(var k = 0, kLen = range[0].length; k < kLen; k++) {
        if(k == 2 /) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
} 

如果某人在表格中提交多个用逗号分隔的问题编号，则需要将该行拆分为多行，如期望的结果"表中所示.

If someone submits multiple issue numbers separated by commas in the form, the row needs to be split up into multiple rows, as shown in the Desired Outcome sheet.

推荐答案

这是我测试过的一些代码，并且可以正常工作.它也适用于同时具有换行和逗号分隔值的单元格.不需要传入范围. . . . .不需要此代码.它将新行直接写到当前结果"表中.

Here is some code that I tested, and it works. It also works for cells that have both new lines, and comma separated values. It does not required that the range be passed in. . . . . . Don't need that for this code. It writes the new rows directly to the 'Current Outcome' sheet.

function result() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var frmSubmissionSheet = ss.getSheetByName('Form Submission');
  var desiredOutcomeSheet = ss.getSheetByName('Current Outcome');

  var data = frmSubmissionSheet.getRange(1, 1, frmSubmissionSheet.getLastRow(), frmSubmissionSheet.getLastColumn()).getValues();

  var issueNumbers = "",
      hasComma = false,
      arrayOfIssueNumbers = [],
      arrayRowData = [],
      thisRowData,
      hasNewLine;

  for (var i=0;i<data.length;i+=1) {
    if (i===0) {continue}; //Skip row 1

    issueNumbers = data[i][2];
    hasComma = issueNumbers.indexOf(",") !== -1;
    hasNewLine = issueNumbers.indexOf("\n") !== -1;
    Logger.log(hasNewLine)
    if (!hasComma && !hasNewLine) {
      desiredOutcomeSheet.appendRow(data[i]);
      continue;  //Continue to next loop, there are no multiple issue numbers
    };

    if (hasNewLine) {
      var arrayNewNewLine = issueNumbers.split("\n");//Get rid of new line
      issueNumbers = arrayNewNewLine.toString(); //Back to string.  Handles cells with both new line and commas
    };

    arrayOfIssueNumbers = [];
    arrayOfIssueNumbers = issueNumbers.split(",");

    for (var j=0;j<arrayOfIssueNumbers.length;j+=1) {
      arrayRowData = []; //Reset
      thisRowData = [];

      thisRowData = data[i];
      for (var k=0;k<thisRowData.length;k+=1) {
        arrayRowData.push(thisRowData[k]);
      };

      arrayRowData.splice(2, 1, arrayOfIssueNumbers[j]);
      desiredOutcomeSheet.appendRow(arrayRowData);
    };
  };
};

这篇关于如何根据一个单元格中的值是否以逗号分隔或Google表格中的换行符将单行拆分为多行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！