MailerSend Bulk Email - Create batches from long list


I wish to send bulk emails with Mailersend based on a list in Google Sheet.

At the moment my flow is as follows:

  1. Pull in a column of e-mails from Google Sheet
  2. Aggregate rows (containt e-mail adresses) to JSON
  3. Send bulk e-mail with MailerSend based on the JSON

Mailersend accepts up to 500 objects in one bulk email, but the list in Google Sheet is 500+.

I am searching for an idea to automatically create batches of 500 rows, aggregate the JSON and make the Mailersend call for each batch.

I could do it manually by making separate columns in Google Sheet with each 500 rows and then run the scenario one at a time, each time using another column.

Any ideas? :slightly_smiling_face:

Hi @Mathias,

Not sure how you will be triggering this sceanario, and I assume that you will be running this on a scheduled basis.

What you can possibly do is, Use Repeater Module under Flow in to iterate over Google Sheets, Get Range values, that will return data in batch of 500.

You can give it a try, but my problem with GSheets, is there is no way to fetch the number of rows in a particular sheets, by which I mean rows that has data in it, it will return if your are using a default sheet, a 100 or 1000 based on the visible rows in Gsheets. But give this flow a try and see if it works for you,

  1. Instead of Get Range Values, you will need to use GSheet > List Sheets module, this will return all the sheets in a particular spreadsheet, alongside the number of rows in it.
  2. Add a filter, that will only select the sheet that you want to use for this automation
  3. Now add a Repeater Module, which will divide the number of rows returned for your spreadsheet by 500, so, for eg, if the 1200 row number is returned then the repeater witll need to run 3 times. See the screenshot attached
  4. After repeater use Get Range Values, and now use the iterator results to set start and end range for the range values you want to use.
  5. Add a filter after the Get Range Values, that will continue only if there is email in the Get Range Values Response
  6. Use existing Aggregrate to JSON module
  7. Add a filter to check if JSON being returned is empty or not, if not then continue to MailerSend

Hi Runcorn

Thank you very much - that did the job :clap:t3:

1 Like