Quartz 4

build a URL in a calculated field in Google Data Studio

2 min read

We wanted to link titles in a table in one report to another report that would show data only for the single item that was clicked. The single-item report was created by using tables to show all data (even single bits like the name of the entity), adding filters for the tables’ data sources, and exposing those filters in the URL.

With filters exposed in the URL, I would now just need to link the original report to the new report and place the unique identifier for the item (in this case the title of a learning object) in the URL in the link. This would create the link to the single-item report filtered to display only the selected item.

My first idea was to create a calculated field with the single-item report URL, adding a replacement marker (I tried !) in place of each filter’s value, and replacing the marker with the REPLACE function like this:

REPLACE('https://whatever.com/?filters={filter1:!,filter2:!}', '!', <entity-name-field>)

That didn’t work because REPLACE requires that the item targeted for the replacement (the first argument) either be or include a field.

To work around this, I performed multiple regex replacements with the entity name field as the basis for the replacement:

CONCAT(
  REGEX_REPLACE(
    REGEX_REPLACE(<entity-name-field>, '^', 'https://whatever.com/?filters={filter1:'),
    '$', ',filter2:'
  ),
  REGEX_REPLACE(<entity-name-field>, '$', '}')
)

The innermost replace adds the first part of the URL in front of the unique identifier used for filtering. The one surrounding that adds everything after that before the next occurrence of the unique identifier. That creates a single string of everything up to the second occurrence. That is concatenation with another occurrence of the unique identifier, followed by the trailing part of the URL.

Here’s the actual code that was used in the implementation:

HYPERLINK(
  CONCAT(
    REGEXP_REPLACE(
      REGEXP_REPLACE(
        URL Safe Title,
        '^',
        'https://datastudio.google.com/u/3/reporting/cdf6bb21-4639-4e14-afb0-dc2f7a06b6fa/page/QwUQC?params=%7B%22df2%22:%22include%25EE%2580%25800%25EE%2580%2580EQ%25EE%2580%2580'
      ),
      '$',
      '%22,%22df22%22:%22include%25EE%2580%25800%25EE%2580%2580EQ%25EE%2580%2580'
    ),
    REGEXP_REPLACE(
      URL Safe Title,
      '$',
      '%22%7D'
    )
  ),
  lo_title
)

These REGEXP_REPLACE calls could be simplified as CONCAT calls instead.

Final Solution

This evolved to contain an additional parameter. When I revisited to add that parameter, I refactored the solution to use a single CONCAT call.

HYPERLINK(
  CONCAT(
    'https://datastudio.google.com/u/3/reporting/cdf6bb21-4639-4e14-afb0-dc2f7a06b6fa/page/QwUQC?params=%7B%22df2%22:%22include%25EE%2580%25800%25EE%2580%2580EQ%25EE%2580%2580',
    URL Safe Title,
    '","df22":"include%25EE%2580%25800%25EE%2580%2580EQ%25EE%2580%2580',
    URL Safe Title,
    '","df33":"include%25EE%2580%25800%25EE%2580%2580EQ%25EE%2580%2580',
    URL Safe Title,
    '"%7D'
  ),
  lo_title
)

Graph View

Backlinks