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

Question

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

Cooper · Accepted Answer · 2022-08-23T21:33:23.120

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:

Number	Buyer Name	Line-Item	ProductOptionName	Quantity	Price	Total
1	jim	Plate	Red	1	9.99	9.99
1	jim	shipping			1.99	1.99
1	jim	tax			0	0
1	jim	discount			0	0
2	bill	Plate	Green	1	9.99	9.99
2	bill	Plate	Blue	1	9.99	9.99
2	bill	shipping			3	3
2	bill	tax			0	0
2	bill	discount			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			7	7
3	jane	tax			0	0
3	jane	discount			0	0
4	dale	Plate	Green	1	10.99	10.99
4	dale	shipping			4.99	4.99
4	dale	tax			0	0
4	dale	discount			1.99	1.99

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

Thank you so much - had to change the /r/n to /n as I'm on linux but apart from that worked like a charm — Martyn, Aug 30 '22 at 13:11

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

1 Answers1