Using Cell References to Dynamically Update the Query (Google Sheets)

You can use native capabilities of spreadsheet formulas and cell references to dynamically update query configuration settings. Below is a use case for how to do this with the "Filter" setting for Supermetrics for Google Sheets, though this same idea can be extended to other settings as well. There are also some known issues to be aware of.

Setting up Dynamic Filters

This is done by editing the query row in the SupermetricsQueries tab of the file to replace the normal filter syntax with a formula that reads a cell reference. Then the next time the query is refreshed, it will use the cell data instead of the default setting and thus can be changed dynamically without editing the query each time in the sidebar. 

IMPORTANT - this does not refresh the query with the new settings automatically. A Supermetrics for Google Sheets license holder still needs to execute the refresh to use the referenced cell or set up a scheduled refresh trigger.

  1. 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.
  2. Open the SupermetricsQueries tab and find the row for the query you want to change. (If hidden, click Add-ons->Supermetrics->Manage queries).
  3. Scroll right to find the "Filters" column (should be column AA).

    Orange box highlights the Excel sheet column AA, which will contain the filter data for each query in the SupermetricsQueries sheet

  4. Take the existing data from the query row, and convert that into a formula that recreates the same syntax, but adds a cell reference in for where the value will be in the filter.
  5. Example 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"":"";""}]"

  6. Place the new formula into the cell directly for the Filter, or you can use another cell reference to the formula (which may be handy in case it gets overwritten - see the Known Issues section below).

    Orange arrow shows the special formula that is placed in column AA for the filter of that query

  7. Then in Y1 is the campaign name piece we want the final data to contain:

    Showing cell Y1 is set to "GSheets" which will be what we want the campaign name to be filtered by

  8. Load the query in the sidebar and click Refresh (but DO NOT MODIFY, this will strip out the formula - see Known Issues below).
  9. The query should now use the cell reference to drive what is filtered:

    Example of resulting data with the formula-based filter, showing only campaign names that contain "GSheets"

Known Issues

If you set this up, you cannot ever edit the query in the sidebar going forward. Otherwise, the system will overwrite your 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 through the UI and 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 cannot 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 you will run into problems due to timing issues.

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.