Spreadsheet formulas and cell references can be set to dynamically update Supermetrics query configuration settings. This article walks through an example of how to do this using a filter in Google Sheets. The same principle can be applied elsewhere, too.
Before you begin
To create a dynamic query, you edit the query’s configuration in the SupermetricsQueries tab, replacing the normal filter syntax with a formula that reads a cell reference. When the query runs, it’ll use the cell data instead of the default setting, so it can be modified dynamically without needing edits in the Supermetrics sidebar.
When you first set this up, it won’t refresh the query with the new settings automatically — a Supermetrics license holder will need to refresh the query to include the referenced cell, or set up a scheduled refresh trigger.
Instructions
- Create a query with a filter that will be in the style you want to use for your dynamically updated one — this way, you can see the syntax you’ll need to use to recreate the formula.
- Open the SupermetricsQueries tab. This is hidden by default — navigate to Extensions → Supermetrics → Manage queries to open it.
- Find the row for the query you want to change.
- Scroll right to find the Filters column, which should be Column AA.
- Take the data from this query row and convert it into a formula that recreates the same syntax, but adds a cell reference for where the values will be in the filter.
Example
Here’s a 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"":""campaign_group_name"",""operator"":""=@"",""value"":"""& Y1 &""",""combineToPrev"":"";""}]" - Place the new formula into the cell directly for the filter, or you can use another cell reference to the formula (this is useful in case it gets overwritten).
- Load the query in the sidebar and click Refresh (but don’t modify, as this will strip out the formula).
- The query should now use the cell reference to drive what is filtered.
Things to consider
A query set up in this way can’t ever be edited from the Supermetrics sidebar again. If it was edited there, the system will overwrite its customizations with the standard setting syntax.
To keep your customization working, you need to edit the query configuration directly, or make a copy of the query that you can edit. Then, paste the changes from the copy into the real query, then remove the copy. It can also help to set the formula up in another cell that can’t be overwritten, and then use a cell reference in the filter cell to that.
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, and errors could result because of timing issues.