After connecting to our DB source through supermetrics, I am just trying to write simple query which uses dates from cells in a separate sheet tab.
SELECT count(*) from leaderboard where created_at <= '¶meters!A1' and created_at >= '¶meteres!A2'
I have tried lot of variations, but it always says some syntax error or column doesn't exist. Can somebody help me out here?
I had another user ask the same basic question and I found a solution for it. I don't know if this is the only or even best way, but it does work to accomplish this.
I was trying to originally approach this like you do in Excel - you need a little VB script and a stored procedure to pass the values you want to use in the query. But there was nothing out there on the net about how to do this in Sheets! So scripting might not be workable. However, because Supermetrics stores it's own version of the DB query in the sheet, you can manipulate that using standard Sheets functions.
This is a very basic example of what I did, but I hope it gets you started:
First, create the database query as normal in the Supermetrics sidebar, using a static value for the one you will be replacing with the cell reference, just so that a starting point is stored.
Then open the SupermetricsQueries tab (if not open already, go to Add-ons->Supermetrics->Manage queries).
Find the query you just created in the list, and scroll over to the SQL column. Highlight the cell and replace the SQL query with a function that keeps the SQL query and adds in the cell reference(s) you want to use. For example, I used this:
=CONCATENATE("select * from cities where id = ", Sheet1!A3)
Sheet1!A3 has a number value in it I want to dynamically reference which is 1 right now. The SQL column in the Supermetrics query sheet looks like this:
Now go select that query on your other sheet and refresh it. It should now pull the data with the id equal to whatever is set in A3 every time it is refreshed:
IMPORTANT WARNING - If you then modify that Supermetrics query in the sidebar and save the changes, it will delete the function from that cell, replacing it with a static statement again and break this. So if you do this method, you MUST edit the query in the SupermetricsQueries sheet itself to maintain the function's functionality.
I hope this is helpful. Please let us know if you find any other issues with this method or cool tricks you can do with it.