We are passing a few parameters:
format=csvas we need the entries in csv format.
per_page=1000to get the maximum number of entries on a single request (1000)as export responses are paginated. Google Sheets could give an error if requesting too many entries though.
If your project has more than 1000 entries, multiple requests need to be made adding an incremental
pageparameter specifying the next page on each request (
page=3and so on).
The first thing to do is to leave the first row empty for the time being, and click on cell A2.
We are going to use the following formula in that cell:
passing the URL described above.
The URL to get the entries for a public project is always the same, just the project slug will be different. For another project, we would just need to replace the
ec5-demo-projectslug with the new one. You can find each project slug in the API section of your project details page, or by just looking at the URL in your browser on your project home page.
Now it is time to add the custom headers. Microreact requires a column
idto uniquely identify each row. With data coming from Epicollect5, that will always be the most left column.
You can use a downloaded csv from Epicollect5 as a reference if you do not remember your headers. After adding them manually, we have:
Important: if you have Date or Time questions, you have to set the format for those columns, otherwise by default they get converted to numbers (Google trying to be smart here).
To do that, select the Date or Time columns, click on Format > Number and pick Date or Time.
Now the sheet is ready to be published. Go to File > Publish to the web...
It needs to be published as comma-separated values (csv) and we need to grab the generated link to use on Microreact
To refresh the data set automatically (so when new entries are added to Epicollect5, they appear on the sheet) we can set an auto refresh to one minute or one hour. Go to File > Spreadsheet settings
On the calculation tab, select "On change and every minute" (or hour)
We are ready to head off to Microreact with the url we just generated:
Please notice the "output=csv". If your url does not have that, check your publish settings.
Paste the project url where it says CSV file and click on "Continue (without tree)"
Enter some basic project details and click on "Create Project"
The project is generated!