Splitting up comma separated values in a single cell to new rows [duplicate]

Question

Every night, we run a batch that sends data from a point of sale service that sends data to Google Sheets, with the hope that we can trigger a Mailchimp automation or add subscribers (the POS software doesn't have an integration :().

The data is sent as an array value, which outputs like this...

The data is sent in single cells, nightly. This day in particular, 82 records were added as a single row, with commas separating the values.

What we need is to take those 82 values, and have them as separate rows in a different sheet automatically. Once that's done, we can use Zapier to send each new row to Mailchimp as a subscriber.

Pic below for desired effect...

Hope this makes sense!

Answer 1

There are multiple ways to handle this. One solution is to keep the input sheet down to one row with data, overwriting old data in Row 2 daily. Then the built-in spreadsheet functions can handle splitting:

=transpose(split(rept(A2&",", B2), ","))

creates a column with the date in A2 repeated the number of times indicated in B2; and

=transpose(split(E2, ","))

splits the content of E2 by commas, arranging the result in a column.

---

But if you expect data to accumulate in the input sheet, another solution is needed. Custom functions can be used for this: =daterepeat(A2:B) to produce the dates, and =splitcolumn(E2:E) to split a comma-joined column.

(In your setup, all these cell references will have sheet name as well, which I'm omitting.)

The code of daterepeat and splitcolumn is below. daterepeat takes a range of two columns as its input, the first with dates, the second with the number of records for that date. splitcolumn takes a range with a single column, which it splits by comma and stacks the results into a column.

function dateRepeat(arr) {
  var output = [];
  for (var i = 0; i < arr.length; i++) {
    if (arr[i][1]) {
      for (var j = 0; j < arr[i][1]; j++) {
        output.push([arr[i][0]]);
      }
    }
  }
  return output;
}

function splitColumn(arr) {
  var output = [];
  for (var i = 0; i < arr.length; i++) {
    if (arr[i][0]) {
      output = output.concat(arr[i][0].split(','));
    }
  }
  return output;
}