Excel Spreadsheet Automation with the QUERY Formula

The CData Excel Add-In for Google Data Catalog provides formulas that can query Google Data Catalog data. The following three steps show how you can automate the following task: Search Google Data Catalog data for a user-specified value and then organize the results into an Excel spreadsheet.

The syntax of the CDATAQUERY formula is the following: =CDATAQUERY(Query, [Connection], [Parameters], [ResultLocation]);

This formula requires three inputs:

• Query: The declaration of the Google Data Catalog data records you want to retrieve, written in standard SQL.

• Connection: Either the connection name, such as GoogleDataCatalogConnection1, or a connection string. The connection string consists of the required properties for connecting to Google Data Catalog data, separated by semicolons.

  Google Data Catalog uses the OAuth authentication standard. Authorize access to Google APIs on behalf on individual users or on behalf of users in a domain.

  Before connecting, specify the following to identify the organization and project you would like to connect to:

  • OrganizationId: The ID associated with the Google Cloud Platform organization resource you would like to connect to. Find this by navigating to the cloud console.

    Click the project selection drop-down, and select your organization from the list. Then, click More -> Settings. The organization ID is displayed on this page.

  • ProjectId: The ID associated with the Google Cloud Platform project resource you would like to connect to.

    Find this by navigating to the cloud console dashboard and selecting your project from the Select from drop-down. The project ID will be present in the Project info card.

  When you connect, the OAuth endpoint opens in your default browser. Log in and grant permissions to the application to completes the OAuth process. For more information, refer to the OAuth section in the Help documentation.

• ResultLocation: The cell that the output of results should start from.

Pass Spreadsheet Cells as Inputs to the Query

The procedure below results in a spreadsheet that organizes all the formula inputs in the first column.

1. Define cells for the formula inputs. In addition to the connection inputs, add another input to define a criterion for a filter to be used to search Google Data Catalog data, such as ProjectId.
2. In another cell, write the formula, referencing the cell values from the user input cells defined above. Single quotes are used to enclose values such as addresses that may contain spaces.
=CDATAQUERY("SELECT * FROM Schemas WHERE ProjectId = '"&B2&"'","ProjectId="&B1&";Provider=GoogleDataCatalog",B3)
3. Change the filter to change the data.