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.
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 via the API'. From the next menu, click on 'Query all fields as JSON'.
- 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."