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.
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 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.
Connect to the GAS OData API as a Data Source in Excel.
Open a new or existing workbook in Excel
Open the Data tab
Choose Get Data->From Other Data Sources->From OData Feed
Identify the OData feed to connect to:
Enter the URL for the service root of the GAS OData feed
Click the OK button
Set the authentication method for the OData feed:
Select Basic authentication
Enter the Connector account credentials for the Username and Password
Click the Connect button
Select the GAS dataset to load
Choose one or more datasets from the Navigator pane
Click the Load button
Start working with the data!
