Google表格-将单元格内容拆分为行和列 [英] Google Sheet - Split Cell contents into rows and columns

问题描述

链接的Google表格: https://docs.google.com/电子表格/d/1j2P2V0SCiE7QtK7kdCorpLfYQPFRghKMw4tuCYbqce0/edit?usp = sharing

Linked Google Sheet: https://docs.google.com/spreadsheets/d/1j2P2V0SCiE7QtK7kdCorpLfYQPFRghKMw4tuCYbqce0/edit?usp=sharing

我有一种将所有产品和数量输出到一个单元格中的表格:

I have a form that outputs all products and quantities into one cell:

产品:NF900XC，数量:3 产品:NF900，数量:2 产品:N1930CB，数量:2 产品:N2120，数量:1 产品:NLPCR200，数量:2 产品:N272，数量:2"

"Product: NF900XC, Quantity: 3 Product: NF900, Quantity: 2 Product: N1930CB, Quantity: 2 Product: N2120, Quantity: 1 Product: NLPCR200, Quantity: 2 Product: N272, Quantity: 2"

我需要每个产品和数量都在其自己的生产线上，并具有相应的关联数据收集.

I need each Product and Quantity on their own Line with its corresponding associated data collection.

我希望将内容(O列)分成各自的列:

I would like the contents (column O) split into their respective columns:

**Product** : **Quantity** NF900 : 2 N1930CB : 2 N2120 : 1 NLPCR200: 2 N272 : 2

**Product** : **Quantity** NF900 : 2 N1930CB : 2 N2120 : 1 NLPCR200: 2 N272 : 2

我已经使用SPLIT(text, delimiter, [split_around_each_character])命令来表示名字，但是不确定在这种情况下如何将其余文本解析为行和列.

I have used SPLIT(text, delimiter, [split_around_each_character]) command for First Name Last Name but am unsure how to parse the rest of the text into their rows and columns in this situation.

我还将在功能上复制时间戳为

I will also be functionally copying rows with the timestamp in

列A

提交日期

对应于O单元格中的Products:Quantity

that correspond to the Products:Quantity in cell O

我希望这是有道理的.

I hope this makes sense.

推荐答案

以下是一些我测试过的代码，它可以正常工作.该代码删除了所有单词"Product:"，并用逗号替换它们.然后，代码使用字符串"Quantity:"执行类似的操作，将其替换为空字符串.接下来，它创建一个数组，并将该数组转换为二维数组，以便可以一次完成将行和列全部写入的操作.

Here is some code that I've tested and it works. The code removes all the words "Product: ", and replaces them with a comma. Then the code does something similar with the string "Quantity: ", replacing it with an empty string. Next it creates an array, and converts the array to a two dimensional array, so that the rows and columns can be written all in one action.

function convertData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('Form Submission');

  var data = sh.getRange("O2").getValue();
  //Logger.log(data);
  var firstProductRemoved = data.replace("Product: ", "");
  //Logger.log(firstProductRemoved);

  var allProductReplacedWithComma = firstProductRemoved.replace(/Product: /g,",");
  //Logger.log(allProductReplacedWithComma);

  var allQuantityReplacedWithNothing = allProductReplacedWithComma.replace(/Quantity: /g,"");
  //Logger.log(allQuantityReplacedWithNothing);

  var dataAsArray = allQuantityReplacedWithNothing.split(",");

  var outerArray = [], innerArray = [];
  var i=0;

  for (i=0;i<dataAsArray.length;i+=2) {
    innerArray = []; //reset every loop

    innerArray.push(dataAsArray[i]);
    innerArray.push(dataAsArray[i+1]);
    outerArray.push(innerArray);
  };
  //Logger.log(outerArray);

  var orderItemsSh = ss.getSheetByName('Order Items');
  orderItemsSh.getRange(orderItemsSh.getLastRow()+1, 15,outerArray.length, 2).setValues(outerArray);
};

这篇关于Google表格-将单元格内容拆分为行和列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！