Split column in BigCartel csv file into long format using Google Sheets and Appscript

I asked this question many moons ago R split column in BigCartel csv file into long format in dataframe or data.table

I've pasted in the problem description below:

Big Cartel has an option that exports orders into a csv file. However the structure is not very good for the analysis I need to do.

Here is a subset of the columns and rows from a Big cartel csv order download (there are other columns which are not significant to the problem at hand).

Number, Buyer name,Items,Item count,Item total,Total price,Total shipping,Total tax,Total discount
1,jim,product_name:Plate|product_option_name:Red|quantity:1|price:9.99|total:9.99,1,9.99,11.98,1.99,0,0
2,bill,product_name:Plate|product_option_name:Green|quantity:1|price:9.99|total:9.99;product_name:Plate|product_option_name:Blue|quantity:1|price:9.99|total:9.99,2,19.98,22.98,3,0,0
3,jane,product_name:Plate|product_option_name:Red|quantity:1|price:6.99|total:6.99;product_name:Thingy|product_option_name:|quantity:1|price:9.99|total:9.99;product_name:Mug|product_option_name:Grey|quantity:1|price:10.99|total:10.99;product_name:Cup|product_option_name:Grey|quantity:1|price:9.99|total:9.99;product_name:Saucer|product_option_name:Grey|quantity:1|price:9.99|total:9.99;product_name:Stopper|product_option_name:|quantity:1|price:9.99|total:9.99,6,57.94,64.94,7,0,0
4,dale,product_name:Plate|product_option_name:Green|quantity:1|price:10.99|total:10.99,1,10.99,13.99,4.99,0,1.99

The items column can have multiple "line-items" with a semicolon (;) as the separator. Each "line-item" has five attributes separated with a pipe (|), i.e. product_name,product_option_name, quantity, price and total (i.e. for the line). There is a column, "Item count", which gives the number of "line-items" plus columns for (order) Total Price, shipping, Tax and Discount. For analysis I'd like the data in the following long format where shipping, tax and discount are also treated as 'product items'.

Number Buyer name line-item    product_option_name quantity price total
1      jim        Plate        Red                 1        9.99  9.99
1      jim        shipping                         1        1.99  1.99
1      jim        tax                              0        0     0
1      jim        discount                         0        0     0
2      bill       Plate        Green               1        9.99  9.99
2      bill       Plate        Blue                1        9.99  9.99
2      bill       shipping                         1        3     3
2      bill       tax                              0        0     0
2      bill       discount                         0        0     0
3      jane       Plate        Red                 1        6.99  6.99
3      jane       Thingy                           1        9.99  9.99
3      jane       Mug          Grey                1        10.99 10.99
3      jane       Cup          Grey                1        9.99  9.99
3      jane       Saucer       Grey                1        9.99  9.99
3      jane       Stopper                          1        9.99  9.99
3      jane       shipping                         1        7     7
3      jane       tax                              0        0     0
3      jane       discount                         0        0     0
4      dale       Plate        Green               1        10.99 10.99
4      dale       shipping                         1        4.99  4.99
4      dale       tax                              0        0     
4      dale       discount                         0        -1.99 -1.99

and was wondering if anyone could suggest a solution in Apps script to get a google sheet as a result table.

Thanks

Martyn

Try this:

function cartelcsv() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const fdr2 = DriveApp.getFolderById(gobj.globals.folder2id);//csv files folder
  const files = fdr2.getFilesByType(MimeType.CSV);
  let obj = { pA: [] };//data collection container
  while (files.hasNext()) {
    let f = files.next();
    let c = f.getBlob().getDataAsString();//collecting data from each file in the folder
    let ls = c.split(/\r\n/);
    let hdr = ls.shift();
    ls.forEach((line, i) => {
      let l = line.split(",")
      let pre = l.splice(0, 2);
      let pos = l.splice(-6, 6);
      l = l[0].toString();
      let slA = l.split(";")
      slA.forEach((s, j) => {
        let r = s.split("|").map(e => e.slice(e.indexOf(':') + 1));
        if (j == 0) {
          obj[`${pre[0]}-${pre[1]}`] = { Number: pre[0], Buyer: pre[1], Purchases: [{ name: r[0], option: r[1], quantity: r[2], price: r[3], total: r[4] }], count: pos[0], ttl: pos[1], ttlwtax: pos[2], shipping: pos[3], tax: pos[4], discount: pos[5] };
          obj.pA.push(`${pre[0]}-${pre[1]}`)
        } else {
          obj[`${pre[0]}-${pre[1]}`].Purchases.push({ name: r[0], option: r[1], quantity: r[2], price: r[3], total: r[4] });
        }
      });
    });
  }
  let oA = [["Number", "Buyer Name", "Line-Item", "ProductOptionName", "Quantity", "Price", "Total"]];
  obj.pA.forEach(p => {
    obj[p].Purchases.forEach((o, i) => {
      oA.push([obj[p].Number, obj[p].Buyer, o.name, o.option, o.quantity, o.price, o.total]);
    });//display all of the data in the collection object
    oA.push([obj[p].Number, obj[p].Buyer, "shipping", '', '', obj[p].shipping, obj[p].shipping]);
    oA.push([obj[p].Number, obj[p].Buyer, "tax", '', '', obj[p].tax, obj[p].tax]);
    oA.push([obj[p].Number, obj[p].Buyer, "discount", '', '', obj[p].discount, obj[p].discount]);
  });
  sh.clearContents();
  sh.getRange(1, 1, oA.length, oA[0].length).setValues(oA)
}

Output:

contents of cartel.csv:

Number, Buyer name,Items,Item count,Item total,Total price,Total shipping,Total tax,Total discount
1,jim,product_name:Plate|product_option_name:Red|quantity:1|price:9.99|total:9.99,1,9.99,11.98,1.99,0,0
2,bill,product_name:Plate|product_option_name:Green|quantity:1|price:9.99|total:9.99;product_name:Plate|product_option_name:Blue|quantity:1|price:9.99|total:9.99,2,19.98,22.98,3,0,0
3,jane,product_name:Plate|product_option_name:Red|quantity:1|price:6.99|total:6.99;product_name:Thingy|product_option_name:|quantity:1|price:9.99|total:9.99;product_name:Mug|product_option_name:Grey|quantity:1|price:10.99|total:10.99;product_name:Cup|product_option_name:Grey|quantity:1|price:9.99|total:9.99;product_name:Saucer|product_option_name:Grey|quantity:1|price:9.99|total:9.99;product_name:Stopper|product_option_name:|quantity:1|price:9.99|total:9.99,6,57.94,64.94,7,0,0
4,dale,product_name:Plate|product_option_name:Green|quantity:1|price:10.99|total:10.99,1,10.99,13.99,4.99,0,1.99