Question 230295 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – How to separate data on on google sheet?

Currently I have this data format on my google sheet:

SKU: PORK BARREL (PILGRIMS/SOBI), QTY: 20000, PRICE PER KILO: 225

enter image description here

Then I want format my Data like this

enter image description here

Then I tried to format it using appscript this code

/**
 * Multiplies an input value by 2.
 * @param {number} input The number to double.
 * @return The input multiplied by 2.
 * @customfunction
*/
function DOUBLE(input) {
  return input * 2;
}
function result(range) {
  delimiter = ":"
  targetColumn = 19

  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][targetColumn].split(delimiter);    
    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 == targetColumn  ) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

but my result is like this one
enter image description here
It goes vertical instead of horizontal.

How can we achieve it like on my desire output to be horizontal and segregate the data.
I also tried to do MID function to split the data but I’m having problem with the QTY , QTY(KG) , QTY(BOX)

I used this code

=MID('Form Responses'!T5,Find(":", 'Form Responses'!T5)+1,find("QTY", 'Form Responses'!T5)-10-Find(":", 'Form Responses'!T5)+20)

enter image description here

Tags:

3

Answers


  1. 0

    Can You Try this –

    function result(range) {
  delimiter = ":"
  targetColumn = 19

  var output2 = [];
  
  // Add header row
  var headerRow = ["SKU", "QTY", "PRICE"];
  output2.push(headerRow);
  
  for (var i = 0, iLen = range.length; i < iLen; i++) {
    var s = range[i][targetColumn].split(delimiter);
    
    var skuValue = s[0].trim();
    var qtyValue = s[1].trim();
    var priceValue = s[2].trim();
    
    var output1 = [skuValue, qtyValue, priceValue];
    
    output2.push(output1);
  }
  
  return output2;
}
    1. I added a header row ("SKU", "QTY", "PRICE") as the first element in the output2 array.
    2. Within the loop, I split the SKU data into three separate values: SKU Value, QTY Value, and PRICE Value using the delimiter.
    3. I constructed a new array output1 for each row with the SKU Value, QTY Value, and PRICE Value.
    4. I pushed the output1 array into the output2 array for each row.
    5. The output2 array now contains the header row and the formatted data rows in the desired format.
    Login or Signup to reply.
  2. 0

    Since I only had one line of text data I just repeated it.

    function lfunko() {
  let arr = ["SKU: PORK BARREL (PILGRIMS/SOBI), QTY: 20000, PRICE PER KILO: 225", "SKU: PORK BARREL (PILGRIMS/SOBI), QTY: 20000, PRICE PER KILO: 225", "SKU: PORK BARREL (PILGRIMS/SOBI), QTY: 20000, PRICE PER KILO: 225", "SKU: PORK BARREL (PILGRIMS/SOBI), QTY: 20000, PRICE PER KILO: 225"];
  let b = []
  arr.forEach((r, i) => {
    let a = r.split(",");
    let o = {};
    a.forEach(e => {
      let t = e.split(":");
      o[t[0].trim()] = t[1].trim();
    });
    if (i == 0) b.push(Object.keys(o))
    b.push(Object.values(o))
    Logger.log(JSON.stringify(b));
  })
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  sh.clearContents();
  sh.getRange(1,1,b.length,b[0].length).setValues(b);
}
    A B C
    1 SKU QTY PRICE PER KILO
    2 PORK BARREL (PILGRIMS/SOBI) 20000 225
    3 PORK BARREL (PILGRIMS/SOBI) 20000 225
    4 PORK BARREL (PILGRIMS/SOBI) 20000 225
    5 PORK BARREL (PILGRIMS/SOBI) 20000 225
    Login or Signup to reply.
  3. 0

    You can also try:

    =REGEXEXTRACT(A1,"SKU: (.+?), QTY: ()(d+), PRICE.*?: (d+)")
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search