Category Archives: Analytics Edge Basic Add-in

Hacking the Query Worksheet

CAUTION: this is an advanced topic with limited support.

Starting with version 5.0 of the Analytics Edge Basic Add-in, you can now see and interact directly with the worksheet contains all of the queries. To expose the worksheet, you can select the Show/Hide > 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 parameters used by the queries, and each column after that contains a query. It is strongly recommended that you use the query wizards to create a sample of queries to see how the various parameters are used. They are mostly undocumented and subject to change without notice, but backward compatibility is usually maintained.

You can manually change any field, but parameter names that are not recognized will simply be ignored (no error message). Be consistent with the query name (top row) and the worksheet and topleftcell for the WriteToWorksheet function.

You can also enter Excel formulas in any cell, but you will have to change the cell from Text formatting to General formatting for the formula to be recognized as such.

You can copy a query column, but you will need to change the name (top row) and worksheet/topleftcell for the WriteToWorksheet, as well as whatever difference you want between the queries.

Note that many of the parameters use a combination of API terminology and/or Analytics Edge-specific conventions, and that those conventions may differ between connectors (and will change over time).

Keep a backup of your workbook before you start. Good luck.

! Keep it Free and Unrestricted !

The Analytics Edge Basic Add-in is free, as is the included Free Google Analytics Connector. There are no account restrictions, no query restrictions, and the product is updated, maintained and supported. To keep it that way, I need your help to SPREAD THE WORD!  Twitter, Facebook, Reddit, at conferences, in meetups, or just mention it over a beer with colleagues.

Please take a few seconds to add a comment and rating to the product listing in the
Google Analytics Partner Listings.

leave-a-commentFirst, make sure you are LOGGED IN (upper right corner).

Just below the listing is a Write a comment link,
and make sure your select a Rating!

A quick rating is fine, but if you can add a few words about what you like (and don’t like) about the product, it all helps.

Keep it Free! Keep it Unrestricted!

 

 

01 Formatting Notes

Once installed and registered, the Analytics Edge Basic Add-in can be used to quickly refresh multiple queries in your workbook. Simply select a destination cell and make a query, then repeat. To refresh all of the queries, click the Refresh All button on the Analytics Edge ribbon.

Some things worth pointing out:

Formatting will be preserved. If you format the cells in Excel, a refresh should not change the format (including conditional formatting) you have done. Note: if the data changes from text, date or numeric to a different type, the cell formatting may be confused by the new data and you may need to reset it to General.

Table formatting will be preserved. If you format a range as an Excel table, a refresh should preserve the table formatting EXCEPT: added columns, rows or calculated values will be lost. The table will be auto-resized to the data coming from the query.

Existing data blocks will be cleared. Analytics Edge looks for an empty row or column to determine the limits of the existing data ‘block’ or table in the worksheet. Everything inside that block will be cleared before the new data is written to the worksheet.

No merged cells. Analytics Edge does not support writing data to merged cells in Excel. Trying to do this will cause an error.

notes

 

Optional Free Trials

The Analytics Edge Basic Add-in comes with two free connectors by default, but you can also install free trials of many others with a simple click. You can start as many free trials as you want, but remember: the clock starts ticking right away so make sure you have to to evaluate them all.

ribbon-free-trials-available

Once the connectors have been installed, restart Microsoft Excel to activate them. They will automatically self-register for a fully functional trial period, and you can start using them right away.

Moving a Query

Move a Query in the Same Workbook

To move an Analytics Edge query, simply move the comment to another location in the same worksheet, or another worksheet in the same workbook.

Move to Another Workbook – Cut and Paste

To copy queries between workbooks, select the worksheet cell where the query is, then select Edit Query – Cut from the Analytics Edge ribbon.

 

remove-a-query

Then select the new location and select Edit Query – Paste from the Analytics Edge ribbon.

 

Copy a Query

Copy in the Same Workbook

To make a copy of an Analytics Edge query, simply copy the comment to another location in the same worksheet, or another worksheet in the same workbook.

Copy to Another Workbook

To copy queries between workbooks, select the worksheet cell where the query is, then select Edit Query – Copy from the Analytics Edge ribbon.

edit-copy

Then select the new location for the copy and select Edit Query – Paste from the Analytics Edge ribbon.

 

Refreshing Queries

Refresh All Queries

Click the Refresh All button on the Analytics Edge ribbon to refresh all of your queries in the workbook at once.

refresh-all-queries

Refresh Current Query

click image to enlarge

click image to enlarge

To refresh only one query, select the worksheet cell where the query is based and click the Refresh – Current menu item from the drop-down below the Refresh All button.

Refresh Selected Queries

click image to enlarge

click image to enlarge

If you want to select which queries to refresh, click the Refresh – Selected menu item from the drop-down on the Refresh All button. In the dialog that opens, select the queries you want to refresh and click OK.

Auto-Refresh Pivot Tables

click image to enlarge

click image to enlarge

Microsoft Excel does not automatically update Pivot Tables in your workbook when you bring in new data, forcing you to manually refresh them from the Data ribbon. Analytics Edge has an option (turned on by default) that will trigger Pivot Table refreshes automatically whenever an Analytics Edge Refresh occurs. You can turn this feature off from the Refresh – Options menu item.

Edit a Query

To edit a specific query, click on the cell where the query is located (remember a comment was placed in that cell so you could find it easily). Click on the Edit button on the Analytics Edge ribbon bar. The original query wizard will open with all of your previous settings.

edit-a-query

Make changes as desired and save the query. See the new results in your worksheet.

Delete a Query

To delete an Analytics Edge query from your worksheet, you must select the cell where the query is located, then either delete the comment or select the Edit QueryCut menu item from the Analytics Edge ribbon bar.

remove-a-query

Adding Queries

With the Analytics Edge Basic Add-in and optional Connectors, you can automate the download of data from a number of sources directly into Microsoft Excel worksheets. You can mix queries from multiple sources, and use different accounts for each query if you want; Analytics Edge makes it easy to refresh them all with a single click.

Here is a simple example of adding 2 Google Analytics queries to a worksheet.

Configuration

setup-google-analytics-account

click the image to enlarge

If you haven’t already logged in to the Google Analytics connector, you need to set up an account to use (see Accounts).

Make Your First Query

google-analytics-query

click the image to enlarge

Click on the Excel worksheet cell where you want your query results, and create a new Google Analytics query (see Your First Query).

See The Results

google-analytics-query-results

click the image to enlarge

The results will appear in the worksheet at the cell you had selected. Note there is a comment on the cell to remind you of the location of the query. Do NOT remove the comment or you will delete the query.

Make Another Query

click image to enlarge

click image to enlarge

Select a different cell to place the second query.

Create the new query and see the results in your worksheet.

* Analytics Edge Basic Add-in

Love the product? Leave a comment in the Google Analytics Partners Gallery or share

ANALYTICS EDGE v5.0.3.0 RELEASE CANDIDATE

2017-12-11 Welcome to the beta release of the all-new Analytics Edge Basic Add-in!  Big changes have been made to the underlying code, so this is a BETA release warning. The previous stable release can be downloaded here.  — Mike Sullivan

Much of the documentation has yet to be updated, so please tolerate a few out-of-date references for a while. There are 2 big changes to v5: 

1 – Comments

  • comments are now used ONLY to copy or move queries and cell references
  • comments can be turned off – use the Show/Hide button on the ribbon to toggle them on/off
  • when comments are ON, the interface may be slower to respond; comments are OFF by default
  • comments appear for all query locations and any cell references in your queries
  • if you move comments, the associated queries and cell references will adjust automatically
  • if you copy comments, the associated queries will be copied (cell references remain at the original location)
  • IMPORTANT: after a move or copy operation, SELECT A CELL in the worksheet to trigger the synchronization

2 – ᴁ Analytics Edge Queries Worksheet

  • a worksheet containing all your queries is now available for manual editing
  • the worksheet is hidden by default – you can Unhide it easily in Excel of use the Show/Hide pull-down menu
  • the first ‘ᴁParameters’ column is controlled by the add-in, and each subsequent column is a separate query
  • some documentation will be released on the syntax in the near future, but it is recommended that you use the wizards to edit your query if you can’t figure out the various parameters are
  • query parameters can be manually edited, and you can use cell formulas if desired (formulas will not be affected if you use a wizard to edit the query later)
  • you can copy columns and edit parameters to make multiple, similar queries
  • query names (top row 1) should be in the format of Sheet1!A1  (<worksheet>!<cell>) and be consistent with the WriteToWorksheet_worksheet/_topleftcell values

Problems?

If you experience any problems with the product, please post to the Forum (the button is still on the ribbon bar).

Mike Sullivan


Your first step is to Register the add-in you have installed. Just start Excel and click the Register button on the Analytics Edge ribbon!

ribbon

Installation problems
Руководство к Analytcs Edge на Русском

Click the Activate 30-Day License to start your free trial period. No payment is necessary and the product is fully functional. OPTIONAL: Enter your email address to stay informed of updates to the product and make it easier to reset or transfer your product licenses after computer updates.

register

Help Is Just a Click Away

help-and-supportClick the Help button on any wizard or on the ribbon bar menus for additional information about any specific function or wizard feature. If the information available doesn’t answer your question, post to the Analytics Edge Simply Free support forum for assistance. A button is provided right in the ribbon bar for your convenience. Note: email support is NOT provided for the free products.

query-commentBasic Add-in Operation

The Analytics Edge Basic Add-in allows you to assign queries to cells in your worksheets. Simply select a cell and open a Connector wizard. The results of the query will be written into the worksheet starting with the selected cell. A comment will be placed on the cell as a reminder of the query location.

Note: the comment contains a pointer to the query. Copy, delete or move the comment within the same workbook and you will copy, delete or move the query.

See the Results

The results are automatically written to the worksheet, overwriting any previous content. Note that you can format the results and refreshes will not alter the formatting — you are free to put your own personal look to the data. CAUTION: if you change the query, you will be asked if you want to clear the formatting; this is useful if you want to insert a column and do not want to use the  previous formatting in the cells.

Refresh All, Current or Selected

Click Refresh All to re-run all the queries in the workbook. To refresh only the query for the selected cell, select the drop-down menu below the Refresh button and pick Current. To refresh several queries at once, pick Selected from the drop-down menu.

Edit the Query at Any Time

You can modify the query by simply selecting the cell and clicking Edit. The previous settings are automatically inserted into the wizard, and you can make changes or cancel.

cut-copy-paste-queriesCut, Copy and Paste

If you want to delete, copy or move any of the queries, simply delete, copy or move the comments. If you want to copy or move the query to a different workbook, the Edit Query button has a drop-down menu with Cut, Copy and Paste functions that work across workbooks.

Sync Comments and Queries

If you copy or move any queries, or if you insert columns or rows causing the queries to move, the comments will be out-of-sync with the new locations. Simply click on one of the query cells to re-synchronize them.