Skip to main content

Google Sheets

The Transform Connector for Sheets add-on allows you to pull your defined metrics and dimensions from MetricFlow directly into Google Sheets for analysis. This add-on supports a custom menu that allows you to select data and query it much like you would from Transform's User Interface and API's. The add-on also supports a custom function with similar functionality to the menu.

Requirements​

  1. Google Sheets
  2. Active account with the Transform application
  3. A Transform API key (you can get an API key from the Transform application here)
  4. Recommended: A web browser only signed in to one Google account

Instructions​

  1. In Google Sheets, navigate to Extensions --> Add-Ons --> Get Add-Ons.
  2. Search for Transform Connector for Sheets and install the add-on.
  3. Open the add-on, which will show a custom Transform menu on the right-hand side
  4. This menu will first ask for a Transform API key for authentication purposes. Enter the API key

Using the Custom Menu​

After authentication, you should see a custom menu that has various selections including the ability to select metrics, dimensions, constraints, time granularity, time over time comparisons, limit, and order.

Gsheetmenu

Selecting Metrics and Dimensions

Metrics will be listed by their display names as specified in the definition. Dimensions are de-underscored from their source names and are organized by their identifiers of the data source they come from. For example, if the dimension is defined as org_name in MetricFlow, and it is from a data source called org, you will find org as an identifier in grey with "Org Name" listed underneath it.

When you select multiple metrics, we will show only dimensions that are common between them, so we can group and create the dataset in a table format. The primary time dimension for your Transform model is consistent across all metrics and will be at the top of the selection menu. This time dimension and any other time dimension will have a calendar icon next to it.

We will automtically select metric_time when you select your metrics. You can always unselect metric time and query without a dimension, which will simply aggregate the metric over all time by default or the time constraint (if you have one defined).

Time Constraint

You can optionally constrain the primary time range across all metrics you've selected, and you can select multiple operators - is, is on or before, is on or after, and is between and is (Date Presets). To filter by other time dimensions that aren't the primary time for your Transform model, you can use the Where constraint option.

The date presets option will open a list of presets, such as "Last 7 Days, "Month to Date", "Last 90 Days" and so on. You can also input a custom number of days to offset. These presets will offset with the latest day of data.

Where Constraint

You can optionally filter the dimension values in the dataset using the "Where" selection. All possible dimensions you can filter will be in a dropdown list. The menu will prompt you to first select the dimension you want to filter by. It will then prompt you to select the operator (<, >, =, !=, >=, <=), and then an option to type free-form text for the actual value you want to filter by. If the dimension you are filtering on is a time dimension, you will get a calendar option when filtering.

The input for string dimension values does not require quotes around the value. Instead, the Google Sheets connector will automatically format the string values for you.

Granularity and Time over Time

You can slice and dice the metrics by various supported granularities. We will only surface the maximum granularity supported across the metrics. For example, if you select a metric that's defined at a monthly level, we will only show monthly or greater granularity choices.

Time over time will return the metric as a percentage change over a granularity window rather than the raw metric values. It requires a granularity selection. By default we will default to the most granular selection if you haven't explicitly made a selection.

Running the Query

The data will return in the selected cell where your cursor is clicked into. Each selection will include a note in the cell that summarizes information about your selection. If you are running a query and clicked into a set of cells that already has data selected from Transform populated in them, the menu will surface a warning confirming that you want to overwrite the data.

Saving the Query and Refreshing on Load

Transform has the concept of Saved Queries in Google Sheets. This is currently separate from the concept of Saved Queries in the Transform App.

In Google Sheets, you can choose to "Run and Save Query" after you make selections. Additionally, you can choose to save the query as a private or public view. If saved as a public query, all editors of the Google Sheet will be able to refresh the query and see the query details but only the creator of the query can edit or delete it.

After you save a query, you can then navigate to the "Saved Queries" tab on the Connector, which lists all your Saved Queries split out by public or private. In this menu, you can choose to view details about the query (e.g., selections and cell reference), edit queries, delete the queries, or refresh queries automatically on load. If you select to refresh on load, every time you open the Transform Connector, all your saved queries will be refreshed (if you have many queries, this might take a few minutes).

Retrieve Custom Function Command from Menu

If you would prefer to use the custom function, we have a toggle on the custom menu that allows you to retrieve the custom function command that's identical to the custom menu selections.

note

The maximum custom menu runtime is six minutes.

Using the Custom Function​

The custom function is called MQL_QUERY() and matches the inputs of the custom menu. The custom menu requires the metric and dimension names as defined in MetricFlow.

The syntax is as follows, and you can see example inputs directly in the help tab of the Google Sheet. 

MQL_QUERY(metrics, dimensions, where, timeConstraint, granularityOption, timeOverTimeOption, orderBy, limit)

Examples

Selecting a single metric and dimension:

MQL_QUERY("mymetric", "mydimension", "mydimension > 10", "date > '2020-01-01'", "month", "MOM", "mydimension", 10)

Select multiple metrics and dimensions:

MQL_QUERY("metric1,metric2", "dimensiona,dimensionb", "dimensiona > 10", "date > '2020-01-01'", "month", "MOM", "mydimension", 10)

The ordering of inputs is respected. For example, if you only want the first three elements (metric, dimension, constraint), you only need to specify the first three. However, if you want elements one (metrics), two (dimensions), and four (date constraint), you must include empty double quotes for element three. 

MQL_QUERY("mymetric", "mydimensions", "", "date > '2020-01-01'")
note

  1. The maximum custom function runtime is 30 seconds.

  2. The custom function can only be authenticated with the owner of the given Google Sheet credentials. This means that if you share the sheet with the custom function, the data won't populate for a user that is not an Owner. If you plan to share the sheet across teams, we recommend you use the custom menu, which will drop in the data from any previous selections. You can still share the data from the custom function, but you must copy and paste the values to save them.