If the source URL of your custom JSON/CSV/XML data supports start and end dates in its request URL, you can set up dynamic date selections to your Google Sheets or Looker Studio (formerly Data Studio) report using Supermetrics.
Before you begin
To set up dynamic date ranges with custom JSON/CSV/XML data, the request URL for the data should support date ranges. If you are unsure whether date ranges are supported, see the appropriate product or API documentation for what is allowed in the URL request.
Instructions
URL setup
In your source URL, find the locations where start and end dates are defined. You can set up dynamic date ranges by using placeholder strings instead of hardcoded dates.
Make sure the date format matches what the service is expecting. For example, if the date is expected in YYYY-MM-DD, then you would use #sdate# and #edate#. Here are all the available formats for start dates and end dates:
| Required date format | Placeholder text |
|---|---|
Start date, y-m-d format | #sdate# |
| Start date, ymd format | #sdate_ymd# |
| Start date, unix timestamp format | #sdate_u# |
| End date, y-m-d format | #edate# |
| End date, ymd format | #edate_ymd# |
| End date, unix timestamp format | #edate_u# |
Example
Original request URL:
https://my-web-service.com/v1/projects/?start-date=2020-01-01&end-date=2021-01-01
Modified URL with date placeholders:
https://my-web-service.com/v1/projects/?start-date=#sdate#&end-date=#edate#
Destination setup
- Open a new Google Sheets file.
- Click Extensions → Supermetrics → Launch to open the Supermetrics sidebar.
- Under Data source, select JSON/CSV/XML.
- Under Report configuration, fill in the original request URL with some fixed date values to the Type URL field and select the appropriate data type.
- Click Get data.
- Once your query has been run with the static date range, open the SupermetricsQueries tab.
The tab is hidden by default. Open it from the tab menu at the bottom of your spreadsheet, or through Extensions → Supermetrics → Manage queries. - Locate the query and its request URL value in the Accounts/views column (W).
- Use cell references to replace the fixed date values in the URL.
- This can be achieved in various ways with Google Sheets formulas. Here’s an example:
1. Set up two cells for filling in start and end date values: in this example, W10 and W11.
2. Set up a formula to the cell in column W of the query to rebuild the cell and URL format with references to the cells:
=CONCATENATE("[""https://api.domain.com/query?id=12345&start_date=",W10,"&end_date=",W11,"""]")
3. The formula appends the URL and cell references to a working format:
["https://api.domain.com/query?id=12345&start_date=2021-08-01&end_date=2021-08-31"]
- This can be achieved in various ways with Google Sheets formulas. Here’s an example:
- Once the request URL uses the cell references, you can adjust the date range through the cells’ values. To use the newly adjusted date range to get data, refresh the query.
Once you’ve set up the cell reference formula, you can never modify the same query through the sidebar again. Any modification made to the query through the sidebar will overwrite your custom formula and break the dynamic date range. If you need to edit the query, modify it directly in the SupermetricsQueries sheet.
- Log in to Looker Studio.
- Create a new data source file or edit an existing one with the JSON/CSV/XML by Supermetrics data source.
- In the Parameters view, paste the request URL with the date placeholders to the Source URL or Google Drive path field.
- Connect or reconnect the data source.