How to export data from Pigment with APIs

  • 21 December 2021
  • 0 replies
  • 3490 views

Userlevel 5
Badge +8

Pigment allows users to automatically export data from metric, list and table blocks with our REST API. In this article, you will find all the necessary information to help you configure a data export from Pigment with APIs. 

 

Information you need to trigger the export 

 

To trigger an export from the Pigment export API, you need three types of information:

  • Pigment Export API key: this secret key will be used to authenticate the service calling the API. Use the Integration page to get one.
  • View ID: ID of the view you want to export

 

 

Export Metric, Table & List data 

 

When exporting from a block with Scenarios activated, Pigment will use the default Scenario. To export from other Scenarios, navigate to page options and set the Default items to the Scenarios you wish to export from.

 

Description

 

URL

Here is the GET API URL: 

https://pigment.app/api/export/view/{viewId}

 

Parameters

To find {viewId}, open the block (Metric, List or Table) within Pigment’s UI and copy the URL. It should look like  https://pigment.app/application/{applicationId}/tables/{blockId}?displayMode=Grid&viewId={viewId}, retrieve the view Id from that URL as indicated.

 

Note

Create a dedicated view for your API export and retrieve the View ID in the URL.

 

To authenticate yourself to the API, use bearer {token} as your authorization method in the API call’s HTTP headers.

If your API Key has the old format with dashes (e.g. 3jp2e232-9875-42f4-9fcf-63fe2ce568jp) then you need to generate a new key from Pigment following these instructions.

 

 

Output

The API call will return a CSV with ; separators. Your data will be flattened and not formatted.

The output will look like as if all Dimensions in rows and columns were set in rows and Metrics were set in columns.

Here is an example of a view with the expected flattened output. 

 

Country;Month;Cost;Revenue;Margin
FR;Jan 21;50.00;150.00;100.00
FR;Feb 21;50.00;150.00;100.00
FR;Mar 21;60.00;180.00;120.00
FR;Apr 21;65.00;195.00;130.00
FR;May 21;55.00;165.00;110.00
FR;Jun 21;50.00;150.00;100.00
FR;Jul 21;25.00;75.00;50.00
FR;Aug 21;40.00;120.00;80.00
FR;Sep 21;60.00;180.00;120.00
FR;Oct 21;60.00;180.00;120.00
FR;Nov 21;65.00;195.00;130.00
FR;Dec 21;75.00;225.00;150.00
DE;Jan 21;16.66666666666667;50.00;33.33333333333333
DE;Feb 21;16.66666666666667;50.00;33.33333333333333
DE;Mar 21;20.00;60.00;40.00
DE;Apr 21;21.66666666666667;65.00;43.33333333333333...

 

CURL Example

# Template variables
PIGMENT_API_KEY="CHANGE ME"
VIEW_ID="CHANGE ME"

# Get the CSV
curl -X GET "https://pigment.app/api/export/view/${VIEW_ID}" \
-H "Authorization: Bearer ${PIGMENT_API_KEY}"

 

 

Which data can be accessed through API? 

 

Warning

Export API keys have no dedicated Access Rights associated with them. They instead impersonate the access rights of the owner of the API key. For example, if you export data from Pigment by calling the Export API using a key created by John, the API will return the data as seen by John.


This topic has been closed for comments