Excel Spreadsheet Automation on Cosmos DB Data with the QUERY Formula

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

• Connection: Either the connection name, such as CosmosDBConnection1, or a connection string. The connection string consists of the required properties for connecting to Cosmos DB data, separated by semicolons.

  To obtain the connection string needed to connect to a Cosmos DB account using the SQL API, log in to the Azure Portal, select Azure Cosmos DB, and select your account. In the Settings section, click Connection String and set the following values:

  • AccountEndpoint: The Cosmos DB account URL from the Keys blade of the Cosmos DB account
  • AccountKey: In the Azure portal, navigate to the Cosmos DB service and select your Azure Cosmos DB account. From the resource menu, go to the Keys page. Find the PRIMARY KEY value and set AccountKey to this value.
• 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 Cosmos DB data, such as Name.
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 Customers WHERE Name = '"&B3&"'","AccountEndpoint="&B1&";AccountKey="&B2&";Provider=CosmosDB",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.)