How to split comma separated values in single cell to different columns in google sheet using GScript?

Question

Have the above data in single cell of google sheet sheet1. Need to bring it to individual col's in sheet2.

categories: ScreenGuard item_meta: {'Brand': 'Lenovo', 'Series': 'Z', 'Model': '7r883', 'Length (mm)': '134', 'Width (mm)': '132', 'Total Area (sq mm)': '17688', '_measurement_data': '{"length":"{\\"value\\":\\"134\\",\\"unit\\":\\"mm\\"}","width":"{\\"value\\":\\"132\\",\\"unit\\":\\"mm\\"}","_measurement_needed":"17688","_measurement_needed_unit":"sq mm"}'} line_subtotal: 176.88 line_subtotal_tax: 0.00 line_tax: 0.00 line_total: 176.88 name: Screen Guard product_id: 10 quantity: 1 sku:  tags:  tax_class:  type: simple unit_price: 176.88 variation_id: 0

this can repeat in the same cell ref the sheet for more examples

https://docs.google.com/spreadsheets/d/1NTqAi361vmaVQhgjWhs0y7oEZcb7K09W9unEE12nOkI/edit?usp=sharing

need Brand,Series,Model,Length, Width values in next sheet. Can this be achieved using formulas?

You tagged apps script. Have you tried anything? Where does the data come from? — TheMaster
– TheMaster, Commented Feb 20, 2020 at 8:17
this comes from a woocommerce plugin, I know little about formulas and that little knowledge was telling me that this cannot be achieved it using formulas, hence added script. If it can be through formula then that would be great. — Kanted
– Kanted, Commented Feb 20, 2020 at 10:02

Inacio Schweller · Accepted Answer · 2020-03-29 06:37:32Z

Building on the answer given by Timmer, I've built a function to split the values and post them into different columns in the second sheet.

Here you go:

function split_values(){
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName("Original_Data");        //Your original Sheet Name
  var sheet2 = ss.getSheetByName("Product_Measurement"); //Your modified Sheet Name
  var cellRange = sheet.getDataRange();
  var cellValues = sheet.getDataRange().getValues();  //All values from original sheet.

  var arrayItems = [];
  var array_to_print = [];
  cellValues.forEach(function(row, index) {
    //Uncomment the below line if in sheet 1 we have header row that needs to be ignored.
    //if (index !== 0) {                                      
      var cell = sheet.getRange(index+1, 2)
      if(cell.getValue() != "Success"){                //Check if we have already parsed this row
        cell.setValue("Success")                       //If not, parse it and set status as Success.
      var split_item = row[0].split(/item_meta: /g);
      for(var i=1;i<split_item.length;i++){
        var item = split_item[i].split(/, 'Total Area/)[0].split(/, 'Total Area/)[0].replace(/'/g,'"')+ '}';
        item = JSON.parse(item);
        arrayItems.push(item);                         //Add all the split prdoducts to an Array.
      //} Uncomment for header.
      }}
  });

  //Add only required details to the array that needs to be put in different columns.
  for (var i = 0; i < arrayItems.length ; i++) {
    array_to_print.push([arrayItems[i].Brand,arrayItems[i].Series,arrayItems[i].Model,arrayItems[i]['Length (mm)'],arrayItems[i]['Width (mm)'],"Success"]) 
  }
  
  //Print the array in the Modified sheet.
  if(array_to_print.length>0){
       sheet2.getRange(sheet2.getLastRow()+1, 1,array_to_print.length,6).setValues(array_to_print);
  }
}

In the sheet, I've also added some menu options and created a third sheet to shift the success items. You can run the functions from the menu item "Google Script Functions".

Hope this helps you!

Kessy · Accepted Answer · 2020-02-20 15:06:00Z

Something like this as a first step?

function vanDikHoutZaagtMenPlanken(){
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName("sheet1");
  var cellValue = sheet.getRange("A3").getValue();

  var arrayItems = [];
  var hlpItems = cellValue.split(/item_meta: /g);

  for(var i=1;i<hlpItems.length;i++){

    var item = hlpItems[i].split(/, 'Total Area/)[0].split(/, 'Total Area/)[0].replace(/'/g,'"')+ '}';
    item = JSON.parse(item);
    arrayItems.push(item);

  }

  //Edit -> Looping through the result to get every value needed

  for (var i = 0; i < arrayItems.length ; i++) {

    Logger.log(arrayItems[i])
    Logger.log(arrayItems[i].Brand)
    Logger.log(arrayItems[i].Series)
    Logger.log(arrayItems[i].Model)
    Logger.log(arrayItems[i]['Length (mm)']) 
    Logger.log(arrayItems[i]['Width (mm)'])
    Logger.log("================================")

    // This is an example on how to set a value to the sheet2

    ss.getSheetByName("sheet2").getRange('B5').setValue(arrayItems[i].Brand);

  }

}

Check the following links to see how to use:

getRange(a1Notation): To get the cells you need to work with.

getLastRow(): Returns the position of the last row that has content.

setValue(value): To set a value to a cell.

Collectives™ on Stack Overflow

How to split comma separated values in single cell to different columns in google sheet using GScript?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related