When the built-in GAS reporting capabilities aren’t sufficient, customers frequently turn to tools like Excel to analyze GAS data and create rich reports and charts. In early versions of the software, users would have to manually export the data out of GAS into a CSV file, and then open that file in Excel and reformat it to suit their needs.
However, tools like Excel and Power BI are also capable of pulling data directly from applications like GAS via APIs, in the same way you might import data into a spreadsheet from an external file or database. The Connector Read module provides a set of APIs that are designed to be compliant with applications like Power BI, following an industry standard called OData.
OData (Open Data Protocol) an ISO/IEC approved, OASIS standard that defines a set of best practices for building and consuming RESTful APIs. More information about OData is available at: https://www.odata.org/
This article provides an example set of steps in Power BI Desktop to connect to the data in a GAS application instance and create a simple report. The example assumes you have already activated and configured the Connector Read module for use.
Connect to the GAS OData API as a Data Source in Power BI.
Create a new report in Power BI
Open the Home tab
Under the Get data menu choose OData feed
If the OData option is not displayed in the top menu, you can find it under the More… sub-menu
Identify the OData feed to connect to:
Enter the URL for the service root of the GAS OData feed and click the OK button
This example uses a fictional customer’s Evaluation site. You will need to use the URL for your own GAS application instance.
Set the authentication method for the OData feed:
Select Basic authentication
Enter the Connector account credentials for the Username and Password and click the Connect button
The Connector account credentials are configured in the GAS application by a System Administrator, using the Configure Connector Account page.
Select the GAS dataset to load
Choose a dataset from the Navigator pane
Click the Load button
Start working with the data!
