Excel Spreadsheet Automation with the QUERY Formula

The CData Excel Add-In for Google Search provides formulas that can query Google Search data. The following three steps show how you can automate the following task: Search Google Search results 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 Search results records you want to retrieve, written in standard SQL.

• Connection: Either the connection name, such as GoogleSearchConnection1, or a connection string. The connection string consists of the required properties for connecting to Google Search results, separated by semicolons.

  To search with a Google custom search engine, you need to set the CustomSearchId and ApiKey connection properties.

  To obtain the CustomSearchId property, sign into Google Custom Search Engine and create a new search engine.

  To obtain the ApiKey property, you must enable the Custom Search API in the Google API Console.

• 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 Search results, such as SearchTerms.
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 VideoSearch WHERE SearchTerms = '"&B3&"'","CustomSearchId="&B1&";ApiKey="&B2&";Provider=GoogleSearch",B4)
3. Change the filter to change the data.