How to get all repeat data to integrate in integromat to MS excel
Hello to the community! I am currently exploring the power of webhook using the integromat in survey 123 and put every values in a MS excel. I am currently stuck in how to collect all data from a repeat function and placed it in an specified cell. I am always getting the first repeat.
Anyone could help me out about this is much appreciated.
I was able to make this work for me. I have been putting a lot of time in on this, and searching for resources far afield! So I want to share my results here for the community. First: I found Make (formerly Integromat) much easier to work with, and more stable/reliable than Power Automate which I was first using.
Yes, the secret to dealing with repeats in Survey123 data is the Iterator tool, and it is pretty intuitive to use. You tell it what to take as its input, and it splits it up into sections, so if you have a section of repeats, the name of the "repeat table" is what you feed to the iterator. If you are handling multiple attachments, feed the attachments to the iterator. Then it depends on what you are trying to do with the data. I have been creating scenarios that will back up Survey123 data to my organizational OneDrive account (note, I had to get my org admin to set up permissions to upload attached photos to a file in OneDrive, but this was easy and Mak walked me through it and it worked first try. Also, the free version only allows you two scenarios running at a time, but the upgrade is inexpensive). I have one section that sends me an email notification (and I can set this to include all attached photos to the email if I want quick visual inspection), one section that writes each data entry (so if repeats, each section from the repeat) to a new row in an Excel workbook hosted on OneDrive, and one that names and saves photo attachments to a folder in OneDrive.
So all of that functionality is possible currently. Below is the scenario as I have it set, using a Router tool to handle multiple tasks simultaneously.
The Survey123 module is pretty straight forward. You can use ADD to create a new webhook. Just know that the name you give it here will NOT show up on your list of webhooks in Survey123. I recommend renaming the webhook in Survey123 to match the name you assign in Make (since I end up going through several versions and trials to get it in final form, and it gets confusing).
The iterator can take your photo attachments:
Or repeats (in this case the repeat section is named "birds"):
For photos, you need to use the HTTP module because they are held in a URL, which now you will get from the Iterator, NOT from the Survey123 module. Notice the dynamic text is colour coded to keep track of the source. Also, not sure if it is needed, but I saw a reference in other online content to adding an access token, so I include this just in case (the URL is from the iterator and the token from Survey123, and "?token" is typed in by hand:
So this now gets each of the photos from the survey entry... but if we now sent that to an email module, we would get an email for each attachment. To avoid this, I then use an array aggregator, which then combines the photos back into a single array to be included as an attachment in an email:
Now this is ready to send to the email module. I use Microsoft365Email. The only trick here is to set the attachment section of the email to map to the array from the aggregator using dynamic text:
For this scenario, there were no repeats in the data, so I can go right into the Excel module as well. For this to work, you need to FIRST create an Excel online workbook. It is easiest if the field names match the field names in Surve123 (if you are working with a Survey that is already published, or even already has data in it, go to the Survey123 website, open the data section, and export Excel, then just save that to your online platform!)
Then, just use dynamic text to match the Survey field names to your spreadsheet names. Note you can select different sheets, so you can have new sheets for each calendar year, for instance, and would just need to reset the designated sheet here annually.
To get the photos to back up to a folder in OneDrive, I had to split them BACK UP, so after aggregating them into an array, I once again broke them down with an iterator (more skilled users may find a way to do this earlier on without all these steps, but I tried and couldn't make that work for me).
This way it will save each photo individually, and each photo can be named individually!
The OneDrive module requires setting up your folder location etc. Here too, I just made the folder in advance, though there are modules for making new folders too.
And here is where I started to get creative with file naming options because I want to have some sense of what photos are from what survey, and there can be multiple photos per entry. I use an ID # that goes with the entry, and then insert the word "pic" with an integer value of which number it is of however many there are (the bundle position from the array!) and a file name (note if you don't end with the actual file name, you will have to have it add the file extension or you will have trouble viewing the files on the other end).
And that's it. It uploads multiple photo attachments to a folder in OneDrive, sends them in an email, and adds data to an online .XLSX I have done the essentially same flow to deal with data in repeats, just have the repeat section (like in the "birds" example above) in the iterator.
I hope this can save someone else the week or two I spent on this!!!
Note, MUCH of this I inferred after working with the content from ESRI at the link below:
And finally, since I was helped along the way by the kindness of strangers, here is my email if anyone wants any further info on this that I may be able to help with: james.serendip@mck.ca
