Supermetrics queries in Excel are tied to the connections used to create them. Use this guide to transfer queries to a different account or manage multiple accounts. These steps ensure that your copied spreadsheets use the right access credentials and keep your scheduled updates going.
If you have queries that use a connection you can't manage, for example, if the connection belongs to a user who left the organization, you can replace the connection to keep the queries running. This means that all queries that use the replaced connection will use the substitute instead. Learn more about replacing a connection.
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.
Before you begin
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 Excel license and has access to the Excel files 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 triggers associated with the queries will be removed when you transfer the query to a different user. To keep the triggers working, delete your old triggers and set up new triggers for the new user.
To prevent other users from using your access rights to refresh or edit queries, open the Excel spreadsheet 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 with access to these data sources can refresh the queries using their own access credentials.
Instructions
- The new user should log in to Microsoft Office and then open the Excel file that will be transferred to them.
- Open the Supermetrics sidebar.
- Log in to each data source the queries pull from. This is to set the permissions for this user for the next steps. Learn more about logging in to data sources in Excel.
- Create a new query for each data source using your connection. Close the sidebar after your queries are complete.
- Open the SupermetricsQueries tab. (This is hidden by default. To open the tab, right-click on a tab, click Unhide..., and select SupermetricsQueries.)
- For each query to be transferred:
- Copy the value in the "Refresh with user account" column (column AT) from the query that uses the new connection.
- Paste the value to the "Refresh with user account" column (column AT) for the query rows you want to transfer to use the new connection.
Pay attention to paste the "Refresh with user account" values to the correct query row to match the data source. - Delete the "query ID" (column A) value in that query row.
- Once all the queries have been prepared, open the sidebar, click the 3-line menu in the top left (
), and then Refresh all.
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 values in these cells.
You can transfer the queries to a different user manually:
- Open the new Excel file that you want to transfer the queries to.
- Open the Supermetrics sidebar.
- Log in to each data source the queries pull from.
- On the new spreadsheet, create a new query for each data source using your connection. Close the sidebar after your queries are complete.
- Open the original Excel file that contains the queries to be transferred.
- In both files, open the SupermetricsQueries tab. (This is hidden by default. To open the tab, right-click on a tab, click Unhide..., and select SupermetricsQueries.)
- In the original spreadsheet, 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 spreadsheet.
- Don't edit any rows above Row 16 in the SupermetricsQueries tab.
- Staying in the same tab on the new spreadsheet, delete the values in the Query ID (column A).
- For each query to be transferred:
- Copy the value in the "Refresh with user account" column (column AT) from the query that uses the new connection.
- Paste the value to the "Refresh with user account" column (column AT) for the query rows you want to transfer to use the new connection.
Pay attention to paste the "Refresh with user account" values to the correct query row to match the data source.
- Once all the queries have been prepared, open the sidebar, click the 3-line menu in the top left (
), and then Refresh all.
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 values in these cells.
Scheduled report triggers are removed when you transfer the query to a different user. To keep scheduled refresh triggers working after transferring the queries to another user, you have to delete the old triggers and create new triggers for the new user.
Troubleshooting
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, right-click on a tab, click Unhide..., and select SupermetricsQueries.
- 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 sidebar, click the 3-line menu in the top right (
), and click Refresh all to complete the process.