You can use functions in Google Sheets to create and modify Supermetrics queries.
Before you begin
- Compared to normal data queries, using functions is faster.
- Query parameters can easily be modified — they’re right above the data, and not in the SupermetricsQueries tab.
- Whenever the query parameters are modified, the data will automatically be updated to reflect the changes.
Things to consider
- Google allows custom functions to run for only 30 seconds, so this method of fetching data is suitable only for quick queries. If the time limit is exceeded, the function will display "#ERROR".
- The query information won't be visible along with your normal queries on the SupermetricsQueries tab.
- The authentication information for accessing the data is stored as an authentication token right inside the query. Anyone that has access to this token can fetch data from the specified data source using the credentials of the user account you used for creating the query. Don’t share the token with anyone that shouldn't be able to get that data.
- The file where you use the function must have the SupermetricsQueries tab. The SupermetricsQueries tab is hidden by default. Open the Supermetrics sidebar to create the SupermetricsQueries tab in case the file doesn't have it.
- Select an empty cell in a Sheets file.
- Open the Supermetrics sidebar.
- Click the arrow icon next to Get data to table.
- Click Function or Function (+params).
With either option selected, the "Get data to table" button changes to "Get data using function". Clicking it will insert the function into the sheet along with the results. If you click on the upper-left portion of the results in the table, you should then see the function:
If you use "Function (+params)", you will then see this, with updatable parameters added to the sheet.