Excel Spreadsheet Automation on JSON Services with the QUERY Formula

The CData Excel Add-In for JSON provides formulas that can edit, save, and delete JSON data. The following three steps show how you can automate the following task: Search JSON services 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 JSON services records you want to retrieve or the modifications to be made, written in standard SQL.

• Connection: Either the connection name, such as JSONConnection1, or a connection string. The connection string consists of the required properties for connecting to JSON services, separated by semicolons.

  See the Getting Started chapter in the data provider documentation to authenticate to your data source: The data provider models JSON APIs as bidirectional database tables and JSON files as read-only views (local files, files stored on popular cloud services, and FTP servers). The major authentication schemes are supported, including HTTP Basic, Digest, NTLM, OAuth, and FTP. See the Getting Started chapter in the data provider documentation for authentication guides.

  After setting the and providing any authentication values, set to more closely match the data representation to the structure of your data.

  The property is the controlling property over how your data is represented into tables and toggles the following basic configurations.

  • Document (default): Model a top-level, document view of your JSON data. The data provider returns nested elements as aggregates of data.
  • FlattenedDocuments: Implicitly join nested documents and their parents into a single table.
  • Relational: Return individual, related tables from hierarchical data. The tables contain a primary key and a foreign key that links to the parent document.

  See the Modeling JSON Data chapter for more information on configuring the relational representation. You will also find the sample data used in the following examples. The data includes entries for people, the cars they own, and various maintenance services performed on those cars.

• 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 JSON services, such as [ personal.name.last ].
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 people WHERE [ personal.name.last ] = '"&B3&"'","URI="&B1&";DataModel="&B2&";Provider=JSON",B4)

👁 Formula inputs used in this example. (Google Apps is shown.)

3. Change the filter to change the data. 👁 The outputs of the formula. (Google Apps is shown.)