Connecting Excel to ActivityInfo
You can connect Excel to your ActivityInfo form via ActivityInfo's API. This will allow you to refresh your Excel worksheet and see updates from ActivityInfo automatically.
The Query API Response Size limit is 32 MB. To reduce the data size you can do the following:
- reduce the number of columns included in the query
- shorten the labels of columns
How to connect Excel to ActivityInfo
- From Excel, choose the "Data" ribbon, and then click the "Get Data" button.
- Then, choose "From Web" from the "From Other Sources menu."
- Find the URL of the form. Click on 'Export' in the Table View of the Form, and select 'Export'. From the next menu, click on 'Query as JSON'.
From the options, select either "Entire form" or Currently selected columns" and then Copy the URL that appears.
- Copy and paste that URL from ActivityInfo to Excel.
- In the next step, provide your ActivityInfo credentials. If you are using a personal API token you may copy it into the Password field:
If you have an ActivityInfo password, you may use this to authenticate with our API from Power BI as explained in step 3 below. If you do not have an ActivityInfo password, then you need to generate a personal API token in your ActivityInfo account as explained in the Using an API token to authenticate with ActivityInfo article.
The option to authenticate with the ActivityInfo API using your ActivityInfo password is scheduled to be removed in March 2021, therefore we recommend that you use a personal API token instead.
- The query editor will open. Click "To Table" from the main ribbon:
- Accept the default options and click "OK" to continue.
- Click the expand button to expand the records into columns. In the dropdown that appears, keep all columns checked, but uncheck "Use original column name as prefix." Then, click OK.
- Finally, click "Close and Load" to close the query editor, return to Excel, and load your data.
Refreshing your data
To refresh your Excel worksheet with the latest data from ActivityInfo, go to the "Data" ribbon and then click "Refresh All."
Updating your credentials
After you've been prompted to enter your credentials the first time, Excel will store your credentials for subsequent queries to ActivityInfo.org. If you want to update or remove your credentials:
- Go to the "Data" Ribbon
- Click the "Get Data" menu
- Choose "Data Source Settings"
- This will open a dialog box where you should see the ActivityInfo URL.