Supermetrics queries in Google Sheets are tied to the connection that created them. Use this guide to transfer queries to different connections or manage multiple logins. These steps ensure that your copied sheets use the right access credentials and keep your scheduled updates going.
Note
You can also replace the data source connections to keep the queries running with new credentials on the same Google Sheets file. Learn more about adding and replacing data source connections.
As a first step, we recommend that you try replacing the connection. If replacing the connection doesn't work or if you don't want to replace the connection in all queries that use the same data source, follow the steps in this article.
On the Supermetrics Hub, you can connect to data sources and manage your data source connections. From the Hub, you can also share an authentication link with someone who has access to a data source you need to connect to.
You can also transfer queries by duplicating the Google Sheets file to another account. Note that this will create a copy of the original file.
Before you begin
You have to make these changes for every single query that you want to transfer. Because of this, we recommend that you first try replacing the connection.
Before transferring queries to a different user, make sure that:
- The user you're transferring the queries to has been assigned to a Supermetrics for Google Sheets license and has access to the sheet where the queries are or will be moved to. If you can't access the license or the Supermetrics Hub to assign the license to a different user, contact our support team for help.
- The user has access to all the data sources and connections in the queries. Learn more about data source permissions.
Any scheduled report refreshes associated with the queries have been transferred to the new user.
- If you want to transfer the queries to a different user in an old sheet, transfer the scheduled refreshes to a different user before transferring the queries.
- A query's scheduled refreshes are removed when you transfer the query to a different user in a different file. If you want to transfer the queries with the refreshes to a completely different file, set up new scheduled refreshes.
To prevent other users from using your access rights to refresh or edit queries, open the Google Sheet with the queries you want to transfer to a different user, then log out from the data sources connected to your credentials. Logging out from the data sources removes your authentication information. Other users that have access to these data sources will be able to refresh the queries using their own access credentials.
Instructions
Transfer queries to a different user
- Create a new query using the needed connection.
- Open the SupermetricsQueries tab (if hidden, click Extensions → Supermetrics → Manage queries).
- Copy the value in the "Refresh with user account" column (column AU) from the newly created query.
- Paste the copied value to all query rows you want to transfer.
- For each query to be transferred, delete the "Query ID" (column A) value in that query's row.
- Once all the queries have been prepared, open the Supermetrics sidebar. Open the Home tab, scroll down to Templates and workflow, and click Refresh all queries.
After you refresh, you'll see a new Query ID value in column A. Every time you transfer a query and refresh, verify that the query has a value in column A.
The simplest way to transfer your queries is to create a new copy of a report for the account you'd like to transfer the queries to. Learn how to duplicate a Supermetrics report in Google Sheets.
You can also transfer the queries to a different user manually:
- Open the new Google Sheets file that you want to transfer the queries to.
- Open the Supermetrics sidebar.
- Log in to each data source the queries pull data from.
- On the new sheet, create a new query using your connection.
- Open the original Google Sheets file that contains the queries to be transferred.
- In both files, open the SupermetricsQueries tab. (This is hidden by default. Click Extensions → Supermetrics → Manage queries to open it.)
- In the original Sheet, select and copy the whole rows in the SupermetricsQueries that contain the queries you want to transfer.
- Paste these rows into the SupermetricsQueries tab in the new Sheet.
- Don't edit any rows above Row 21 in the SupermetricsQueries tab.
- Staying in the same tab, delete the values in the Query ID (column A).
- Copy the value in the "Refresh with user account" (column AU) from the newly created query.
- Paste the value to all query rows you want to transfer.
- Once all the queries have been prepared, open the Supermetrics sidebar. On the Home tab, scroll down to Templates and workflow, and click Refresh all queries.
After you refresh, you'll see a new Query ID in column A. Every time you transfer a query and refresh, verify that it has a value in column A.
Transfer scheduled refreshes from one user to another
To transfer scheduled refreshes from another user — or your own old user ID — to the Google Account that you're currently logged in with, transfer the refresh ownership to yourself in the Google Sheets file.
- In the Supermetrics sidebar, navigate to the Schedules tab.
- Click the 3-dot icon next to the schedule you want to transfer.
- Click Transfer to me to transfer the refresh ownership to your current user account.
The alternative is to read the refresh's properties on the add-on's schedule tab, make a note of its contents, and create copies manually.
Troubleshooting
Query range
If a transferred query fails because it can't get a range for the query, it could be because the new query can't recognize the range for the new data to be written on. Update the query range to solve this issue.
- Open the SupermetricsQueries tab. If hidden, click Extensions → Supermetrics → Manage queries.
- Delete the values in the "Sheet name" (column D) and "Range address" (column E) columns.
- In the "Sheet name" column, add the name of the spreadsheet you want your query to be in.
- In the "Range address" column, add the cells you want to have as the query range. It can help to copy this information over from your original spreadsheet, so your query appears in the same location in the new spreadsheet.
- Open the Supermetrics sidebar, and on the Home tab, scroll down to Templates and workflow, and click Refresh all queries.