# Google Sheets

Using the Epicollect5 API, it is possible to export entries in `csv` format.

{% hint style="warning" %}
The project must be **public** to work with Google Sheets.

If you have a private project, have a look at the Survey Toolkit code [**here**](https://github.com/EnAccess/Survey-Toolkit/blob/main/Epicollect_5-Sheets_Integration.gs)**.**
{% endhint %}

Google Sheets features the `=IMPORTDATA()` function to import data at a given url in .csv (comma-separated value)

{% hint style="info" %}
IMPORTDATA() Official Docs -> <https://support.google.com/docs/answer/3093335?hl=en>
{% endhint %}

Create a new sheet and click on the first cell. Paste the following in:

`=IMPORTDATA("`[`https://five.epicollect.net/api/export/entries/ec5-demo-project?form_ref=b963c3867b1441b89cb552b982f04bc8_5784e0609397d&format=csv&per_page=1000&page=1`](https://five.epicollect.net/api/export/entries/ec5-demo-project?form_ref=b963c3867b1441b89cb552b982f04bc8_5784e0609397d\&format=csv\&per_page=1000\&page=1)`")`

After the entries are loaded, it will look like below.

{% hint style="success" %}
For this example, the public [**EC5 Demo Project**](https://five.epicollect.net/project/ec5-demo-project) was used.
{% endhint %}

![Entries loaded in Google Sheets](https://3293478884-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F32OIF30CgrNUuRY6IjkW%2Fuploads%2Fgit-blob-8e4d4b3b29a33c5026522b47116665aa9401bc84%2FScreenshot%202020-12-04%20at%2017.17.02.png?alt=media)

The URL passed in the `IMPORTDATA()` function will load the latest 1000 entries as we passed the parameter `per_page=1000` and `page=1`

To get more entries (if any) we need to add more `IMPORTDATA`() calls and tweak the URL to get a different page ie. 2, 3, 4 and so on.

{% hint style="warning" %}
It is possible to have up to **50** `IMPORTDATA()`calls on a single spreadsheet in Google Sheets.
{% endhint %}

One way to do that would be to create another sheet on the same file and repeat the procedure above, this time using a parameter`page=2`in the URL.

![Loading entries in separate sheets](https://3293478884-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F32OIF30CgrNUuRY6IjkW%2Fuploads%2Fgit-blob-f49e4c187620ced46e5d8bd229cf074f5ed419cd%2Fgs-1.jpg?alt=media)

Another option is to load the first 1000 entries and the headers on the first cell, then on row 1002 load the next 1000 entries omitting the headers in the request by passing the parameter `headers=false.` This way 2000 entries will be loaded on the same sheet.

![Loading entries in the same sheet](https://3293478884-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F32OIF30CgrNUuRY6IjkW%2Fuploads%2Fgit-blob-d17d9d7eccacd0952c5f51c3c6a2d31f1b31c94d%2Fgs-2.jpg?alt=media)

The spreadsheet in the example can be viewed at [**https://docs.google.com/spreadsheets/d/1XibVZ5RXxT9ZfrDUx403I0ZTS0MHe1h26tlFP0dM-bU/edit?usp=sharing**](https://docs.google.com/spreadsheets/d/1XibVZ5RXxT9ZfrDUx403I0ZTS0MHe1h26tlFP0dM-bU/edit?usp=sharing)

Full API guide at [**https://developers.epicollect.net**](https://developers.epicollect.net/)

{% hint style="danger" %}
***`Resource at URL content exceeded maximum size`***

This error on Google Sheets means the dataset you are trying to import is too big in size.

Try to lower the number of entries on each`importdata()` call.
{% endhint %}

![](https://3293478884-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F32OIF30CgrNUuRY6IjkW%2Fuploads%2Fgit-blob-b7ddf6c13a61e6f00f5c9e167cc3e7fdba9e3b6d%2Ferror-gs.jpg?alt=media)