Connector Octolis <> Google Sheet

You can retreive data from any Octolis Dataset into a Google Sheet (using the importdata Google sheet function).
To live query a dataset from a Google sheet, please use the following syntax :
plain text
=IMPORTDATA("https://api.octolis.cloud/v1/data/audiences/{{audienceId}}/records?api-key={{apiKey}}&pageSize=10000&page=1&format=csv&selection={{columnList}}")

Main parameters

  • audienceId : (mandatory) The ID of the Audience you want to extract data from (ask Support if you don’t find it). Please replace {{audienceId}} in the above.
  • apiKey : (mandatory) Your API key that will be provided by our Support. Please replace {{apiKey}} in the above
  • format : (mandatory) Export format must be set as csv for Google Sheet.

Additional parameters

  • pageSize : (optional) Results page size, must be a positive integer ("50000" by default, which also is the maximum size of a page).
  • page : (optional) Results page number (optional): positive integer ("1" by default).
  • selection : (optional) Selection of columns that should be retrieved. Please replace {{columnList}} in the above. Example: selection=email,firstname,masterid
  • filter : (optional) Equality filters applied on the records (optional). Using several filters on the same column will result in an OR condition. Exemple : filter=firstname:Marc,firstName:Jeanne,consent_sms:true
  • header : (optional) Export headers for CSV format (optional): "true" (default) or "false".
  • order : (optional) Sorting for the results "asc" or "desc" (default), done on our system modified_at column.

Exporting large datasets

When you have more than 50 000 records in your Dataset you need to export several pages using a specific Google sheet syntax combine with some of our query params.
Below is an example for querying up to 150 000 records.
plain text
={IMPORTDATA("https://api.octolis.cloud/v1/data/audiences/{{audienceId}}/records?api-key={{apiKey}}&pageSize=50000&page=1&format=csv&selection={{columnList}}");IMPORTDATA("https://api.octolis.cloud/v1/data/audiences/{{audienceId}}/records?api-key={{apiKey}}&pageSize=50000&page=2&header=false&format=csv&selection={{columnList}}");IMPORTDATA("https://api.octolis.cloud/v1/data/audiences/{{audienceId}}/records?api-key={{apiKey}}&pageSize=50000&page=3&header=false&format=csv&selection={{columnList}}")}
⚠️
Please notice that queries to fetch more than the first 50k records have an increased page param and a header param set to false.

Exporting many columns

When you export a lot of columns, the global size of data might exceed Google sheet capbilities, even though you have less than 50k records.
In such a case, you will need to use the above method for exporting large datasets, with pageSize params set to a smaller number (like 5k or 10k).

Powered by Notaku
Helpful?