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(....
Best Answer
Recently Google improved the security of Google Sheets by introducing the block of share, create a copy, download, print directly from the application but it's not possible to block the access to visible parts to certain users. If they can view the file, they will be able to see all that is visible. For details ee Stop, limit, or change sharing
Viewers can't unhide sheets, rows or columns unless the made a copy of the file. So be careful and block that before sharing your spreadsheet.
We could use IMPORTRANGE to import the data from a master spreadsheet and then use FILTER / QUERY / Pivot Tables to limit the data to be displayed.
AFAIK if we use the Google Sheets UI filters, viewers could override them by using filter views.