Accessing Connector Read APIs from Excel

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, Excel and other tools 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 Excel, following an industry standard called OData.

This article provides an example set of steps to connect an Excel workbook 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.

1. Connect to the GAS OData API as a Data Source in Excel.

   1. Open a new or existing workbook in Excel

   2. Open the Data tab

   3. Choose Get Data->From Other Data Sources->From OData Feed

Open

Connect to an OData Feed

2. Identify the OData feed to connect to:

   1. Enter the URL for the service root of the GAS OData feed

      1. Example: https://eval.grandavenue.com/Acme/odata/v2

   2. Click the OK button

Open

Enter the URL for the GAS OData Feed

3. Set the authentication method for the OData feed:

   1. Select Basic authentication

   2. Enter the Connector account credentials for the Username and Password

   3. Click the Connect button

4. Select the GAS dataset to load

   1. Choose one or more datasets from the Navigator pane

   2. Click the Load button

5. Start working with the data!