I want the value in the ‘Value’ field to be pulled in from a cell in my sheet (e.g. E2) that would contain the Product Title i want to filter from.
Is this possible?
Solved
Dynamically pulling in values into the "Value" field in google sheet query
Best answer by Robert White
I use this functionality a good bit myself and find it very useful for doing exactly what you are trying to do
If the sheet with your current extract is called Report (as in my sheet in the screenshot above), to see the SupermetricsQueries sheet, you can click on the three horizontal lines in the screenshot and click “SupermetricsQueries” to unhide the sheet.
If cell E2 in the Report sheet contains the value “Family Eternal Ring”, you can insert the below formula into cell AA21 in the SupermetricsQueries sheet (the cell is highlighted in the screenshot below):
="[{""field"":""ProductTitle"",""operator"":""=="",""value"":"""&Report!E2&""",""combineToPrev"":"";""}]"
Now when you refresh your query in the Report sheet, it will automatically filter based off the value in Cell E2 in the Report sheet.
What I find very handy is creating a dropdown list in Cell E2 using Data → Data Validation (then select “+ Add rule” and, under “Criteria”, select “Dropdown (from a range)”. )
As
+1Hi
Did you try using “Product title” as a dimension?
If I understand your question correctly, that might do the trick 😊
Let me know if it works, and if not, I’ll reach out to some of my Sheet-wizard colleagues! 🤝
Thanks for getting back to me. Not quite what i am after. I want to pull a value from my sheet into the VALUE field in the supermetrics query so i can change via the sheet rather than opening the modify query panel. e.g. I want the VALUE in my filter to = E2 - where E2 is a field in my sheet where i enter the filter data.
I suspect i might not be able to do this!
+1Hey
I believe this here is what you’re looking for:
Once you unhide the SupermetricsQueries sheet, it gives you access to every query parameter as normal cell values which you can manipulate. It takes a bit of trial and error first to not break the formatting but once you have dialled that in correctly, you can achieve true wizardry :)
I would love to hear back from you if this is useful. My team and I are responsible for creating tutorials and templates that help master Supermetrics products and features and I’m always looking for topics we can tackle next
I use this functionality a good bit myself and find it very useful for doing exactly what you are trying to do
If the sheet with your current extract is called Report (as in my sheet in the screenshot above), to see the SupermetricsQueries sheet, you can click on the three horizontal lines in the screenshot and click “SupermetricsQueries” to unhide the sheet.
If cell E2 in the Report sheet contains the value “Family Eternal Ring”, you can insert the below formula into cell AA21 in the SupermetricsQueries sheet (the cell is highlighted in the screenshot below):
="[{""field"":""ProductTitle"",""operator"":""=="",""value"":"""&Report!E2&""",""combineToPrev"":"";""}]"
Now when you refresh your query in the Report sheet, it will automatically filter based off the value in Cell E2 in the Report sheet.
What I find very handy is creating a dropdown list in Cell E2 using Data → Data Validation (then select “+ Add rule” and, under “Criteria”, select “Dropdown (from a range)”. )
As
Ian
Sign up
Already have an account? Login
Ask questions from other Community members, comment on other people's posts and get access to exclusive Community content by logging in.
Log in with Facebook Login with LinkedInor
Login to
Supermetrics Community
Don't have an account? Sign Up
Ask questions from other Community members, comment on other people's posts and get access to exclusive Community content by logging in.
Log in with Facebook Login with LinkedInor
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.