Google-sheets – How to split then combine data to rows

google sheets

I have a Google Spreadsheet that receives data from a Quickbooks invoice entry. The data from item description has the quantity of each item separated by commas, then the items which are also separated by commas. Quantity and items are separated by a ~. Quickbooks always sends a "None" to indicate the end of the list for each. I need to split the data, match the quantity to the items, add new rows and replicate the data in the other columns for however many are in the list from Quickbooks. I have found script to split comma separated data into new rows but not for the combining. Any suggestions?

Sample Rows

Best Answer

It seems you want the processed entries (the row with ~) to remain in the spreadsheet. In this case it's reasonable to have some indicator of whether the row has been processed by the script, so it doesn't add the same things again. Something like this: 

+---------+------------+---------------------------------------------+----------+-----------+
| Invoice |    Name    |                 Description                 |   Due    | Processed |
+---------+------------+---------------------------------------------+----------+-----------+
|    1087 | Customer22 | 2,20,20,None~Item 485,Item 234,Item879,None | 6/2/2015 |           |
|    1088 | Customer33 | 3,4~this,that                               | 6/5/2015 |           |
+---------+------------+---------------------------------------------+----------+-----------+

And here is a script that handles it. Standard JavaScript string methods: split, indexOf, and concatenation. 

function splitJoin() {
  var i, j, dataString, quantItems, quant, items;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  for (i=0; i<values.length; i++) {
    dataString=values[i][2];
    if ((dataString.indexOf('~')!=-1) && (values[i][4]!='yes')) {
      quantItems=dataString.split('~');
      quant = quantItems[0].split(',');
      items = quantItems[1].split(',');
      for (j=0; j<quant.length; j++) {
        if (quant[j]!='None') {
           sheet.appendRow([values[i][0], values[i][1], quant[j]+'-'+items[j], values[i][3]]);
        }
      }
      range.getCell(i+1, 5).setValue('yes');
    }
  }
}

Output: 

+---------+------------+---------------------------------------------+----------+-----------+
| Invoice |    Name    |                 Description                 |   Due    | Processed |
+---------+------------+---------------------------------------------+----------+-----------+
|    1087 | Customer22 | 2,20,20,None~Item 485,Item 234,Item879,None | 6/2/2015 | yes       |
|    1088 | Customer33 | 3,4~this,that                               | 6/5/2015 | yes       |
|    1087 | Customer22 | 2-Item 485                                  | 6/2/2015 |           |
|    1087 | Customer22 | 20-Item 234                                 | 6/2/2015 |           |
|    1087 | Customer22 | 20-Item879                                  | 6/2/2015 |           |
|    1088 | Customer33 | 3-this                                      | 6/5/2015 |           |
|    1088 | Customer33 | 4-that                                      | 6/5/2015 |           |
+---------+------------+---------------------------------------------+----------+-----------+

In this example there is no separation between old and new format, because appendRow puts a row directly under the existing data. But if you put something like "new format" at the bottom of spreadsheet, it will be appended after it.

But it's probably better to put the exported data on another sheet, so that the existing one can still receive new entries without messing things up. E.g., add

var targetSheet = ss.getSheetByName("Exported");

at the beginning, and then use targetSheet.appendRow(... instead of sheet.appendRow(....

Related Topic