This guide covers the manual way of copying queries built with Google Sheets to Excel. Even though the queries built in both products are stored in a sheet called 'SupermetricsQueries', simply copying the query list doesn't work and need a manual fix as there are some column offset differences.
- Make sure you have created a similar connection to the data sources needed with the same username in Excel. See How to log into a data source in Supermetrics for Excel add-in
- If you are copying queries into a fresh new file, you must first create at least one test query in the Excel file to activate the 'SupermetricsQueries' sheet. Please see How to configure an example query with Supermetrics for Excel
- If you are copying queries from multiple Google sheets tabs, you should create a similar number of sheets and structure in the new Excel file.
After going through the checklist above, manually copy and paste the list of queries from the Google Sheet's 'SupermetricsQueries' sheet to the Excel 'SupermetricsQueries' sheet.
- In your Google sheet file, open the SupermetricsQueries tab.
- Find the row of the query you want to export
- Highlight the full row and copy it.
- Paste the row into the destination Excel file
- Delete value in the following cells for the just copied query: Query ID, Sheet name, Range address.
With the current configurations, you will need to manually fix two columns: 'Range address (static)' and 'Refresh with account' columns.
- Scroll to the far right and find the second Range address (static)column. This should be in column AO. The values might be in another column so copy and update the value in the row to the place in the file you want the query to exist in, both the sheet name and the range.
- Remove the similar value from the other columns.
- Scroll to the right again and find the 'Refresh with account' column. This should be in Column AT but the actual copied value from the Google sheet query is in Column AU. Copy and update the value accordingly.
- Again remove the value from column AU.
- If there are Facebook queries among the copied queries, the values of this column is usually the user ID numbers. And it might be copied as a scientific notation. If it did, convert the cell to text and correct the ID.
- Highlight the test query you created in the start and select "Refresh all" from the menu.
- This should work and create new query IDs after pulling the data.
Note that we have a feature in the backlog to implement an automatic export functionality in to the tool and we will send out updates once it is released.