By editing queries using cell references, you can make changes without changing any settings in the Supermetrics sidebar. The changes will be applied the next time someone manually refreshes the query, or the next time a scheduled refresh takes place.
This guide shows you how to do this using the filter setting, where we edit the query in the worksheet to replace its normal filter syntax with a formula that reads a cell reference. The principle can be applied to other settings, too.
Instructions
- Open the Supermetrics sidebar in Excel.
- Set up a query with a filter that will be in the style you want to use for your dynamically updated one. (This is so you can see the syntax to recreate with the formula.)
- Open the SupermetricsQueries tab in your worksheet and find the row for the query you want to change. This tab is hidden by default — to open the tab, right-click on a tab, click Unhide..., and select SupermetricsQueries.
- Scroll right to find the Filters column (this should be column AA).
- Take the existing data from the query row, and convert that into a formula that recreates the same syntax, but adds in a cell reference for where the value will be in the filter.
- Place the new formula into the cell directly for the filter, or you use another cell reference to the formula (which may be useful in case it gets overwritten).
- Load the query in the sidebar and click Refresh. Don’t modify the row — this will strip out the formula.
Example
This is the formula for replacing the CONTAINS filter string for Facebook Ads Campaign Name with a cell reference for data in Y1 of the same sheet.
="[{""field"":""adcampaign_name"",""operator"":""=@"",""value"":"""& Y1 &""",""combineToPrev"":"";""}]"
The query should now use the cell reference to drive what is filtered.
Avoid sidebar edits
Once you've set this up, don't edit the query in the sidebar. Doing so will cause the system to overwrite your customizations with the standard setting syntax.
Next steps
To keep your customization working, you need to edit the query configuration directly, or make a copy of the query so you can edit it and then paste the changes from the copy into the real query.
It can also help to set the formula up in another cell that can’t be overwritten, and then use a cell reference to the filter cell.
It's best to keep the data being referenced simple, such as having the correct value listed as a plain value. If you fetch the reference data with another formula or script, there's a chance it may not be loaded in time for the query to read it.