Enabling Dynamic Date Ranges for Custom JSON/CSV connector in Google Sheets

Dynamic date placeholders #sdate# and #edate# (described in the article Enabling Date Placeholders for Custom JSON/CSV in Data Studio) do work only for Data Studio since it has a built-in date picker element. Google Sheets does not have this functionality, but you can use cell references intead.

You will have to insert cell references into the URL in the query configuration settings that are stored in the SupermetricsQueries tab. Please note, that SupermetricsQueries tab is hidden by default.

Step 1: Run your query with fixed date ranges to create an entry in the SupermetricsQueries tab for it.

Step 2: Click Add-ons -> Supermetrics -> Manage queries.

Step 3: Locate the query you have just created, and scroll over to "Accounts/views" column. The URL for the query will be stored here.

Step 4:  Set up the relevant cell from "Accounts/views" column to recreate the format of the request URL and insert cell references to replace the fixed dates. 

For example:

Here CONCATENATE is being used to rebuild the format the cell needs to work with Supermetrics for Google Sheets and insert the dynamic cell references. So changing the dates in cells W14 and W15 will change the date_start and date_end parts of the URL. Please note, that this is just one example of how to do this. There are many different approaches you can take instead.

Two important notes:
#1 - The date value you add must match what the API is expecting
#2 - You can not ever edit this specific query again in the sidebar. If you open it to edit in the sidebar, it will overwrite the custom settings with the defaults and breaks your formula. So any changes will have to be made in the SupermetricsQueries sheet directly for this particular query.

Did you find it helpful? Yes No

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