3

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... enter image description here

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...

enter image description here

Hope this makes sense!

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;
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.