Learn more
 

In this article

Related articles

Excel Spreadsheet Automation with the CDATAQUERY Formula


The CData Excel Add-In provides formulas that can pull data from different data sources into your spreadsheet. For example, the add-in for Google Apps can create a spreadsheet of directions. This article shows how to use the CDATAQUERY formula to automate spreadsheets.

If you have written database queries before, the format of CData query statements will seem familiar. The syntax of the formula is: =CDATAQUERY(Query, [Connection], [Parameters], [ResultLocation]);. For example, you can create a query that generates a spreadsheet of step-by-step directions: 

=CDATAQUERY("SELECT * FROM Directions WHERE StartAddress='Statue of Liberty' and EndAddress='Golden Gate Bridge'", "OAuthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret; Provider=GoogleApps")

In the example above, the first parameter has the query that will retrieve directions using the CData Excel Add-In for Google, and the second parameter has the connection details needed to connect to Google.

  1. Create a spreadsheet and organize all the formula inputs at the top. In the following example, the user can change the User, Password, StartAddress, EndAddress, and TravelMode.
  2. Write the formula using the cell values from the user input cells defined above. Single quotes are used to enclose values that may contain spaces, such as addresses. 
    =CDATAQUERY("SELECT * FROM Directions WHERE StartAddress='"&B4&"'
		and EndAddress='"&B5&"'", "OAuthClientId="&B1&";OAuthClientSecret="&B2&"; Provider=GoogleApps")
  3. This query retrieves directions from the Directions view. When you change the input, the data changes.

To get started with CDATAQUERY, you can download the resulting spreadsheet.