Add-in: Hacking the Query Worksheet

The Analytics Edge Core Add-in allows you to place Quick Queries anywhere in your workbook, but all those queries can be edited in a single place: the ‘ᴁ Analytics Edge Queries’ worksheet. Caution is strongly recommended–make a backup of your workbook before you start!To expose the worksheet, you can select the Edit Mode > Show Query Worksheet menu from the Analytics Edge ribbon, or simply use Excel’s Unhide function to unhide the worksheet (“ᴁ Analytics Edge Queries “).

The left column contains the name of parameters, and each column after that contains the values for a specific query. You should use the query wizards to create a sample of queries to see how the various parameters are used.

The parameters are mostly undocumented and subject to change, but backward compatibility is usually maintained. Most parameters align with a selection in the wizard, so it should not be hard to figure out what everything does.

You can manually change any query value, but any parameter names that are not recognized or inappropriate combinations will simply be ignored (no error message). You will notice that queries are named with their location like Sheet1!A1. This location is repeated in the WriteToWorksheet_worksheet and WriteToWorksheet_topleftcell values, but if they get out of sync, the name at the top will be used.

Making Copies of Queries

You can copy a query column, change the name (and the WriteToWorksheet_worksheet / WriteToWorksheet_topleftcell values) to make a copy of the query in another location.

You can then change any of the other query values, making it easy to create identical queries for a series of web properties or with different filters.

Use of Formulas

Instead of entering values into the cells, you can also use Excel formulas. If you want to do this, you will have to first change the cell from Text formatting to General format, then enter the formula.

This makes it easy to make a change to one query and have all the rest automatically match (like start and end dates).

CAUTION: when you enter formulas in the query worksheet, the wizard interface will NOT change that specific value if you edit the query using the wizard interface. The formula will ‘lock’ that cell from change.

Weird Values

Note that many of the values in the queries may use a combination of API terminology and/or Analytics Edge conventions, and that those conventions may differ between connectors (and will change over time). In case of doubt, use the wizard interface to edit the query.

Remember to keep a backup of your workbook before you start. Have fun!