Excel Spreadsheet Automation with the QUERY Formula

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

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

  YouTube Analytics uses the OAuth authentication standard. You can use the embedded CData OAuth credentials or you can register an application with Google to obtain your own.

  In addition to the OAuth values, to access YouTube Analytics data set ChannelId to the Id of a YouTube channel. You can obtain the channel Id in the advanced account settings for your channel. If not specified, the channel of the currently authenticated user will be used.

  If you want to generate content owner reports, specify the ContentOwnerId property. This is the Id of the copyright holder for content in YouTube's rights management system. The content owner is the person or organization that claims videos and sets their monetization policy.

• 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 YouTube Analytics data, such as Mine.
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 Groups WHERE Mine = '"&B3&"'","ContentOwnerId="&B1&";ChannelId="&B2&";Provider=YouTubeAnalytics",B4)
3. Change the filter to change the data.