Syncing Data Sheet views to a spreadsheet
You can sync a Data Sheet view directly to a Google Sheets or a Microsoft Excel file. This creates a "live query" that is kept up to date in your spreadsheet.
Changes you make to the spreadsheet are not synchronized to ChartHop. However, you can refresh the data for the spreadsheet periodically or when you expect that the ChartHop data may have changed.
For example, you can create a live query to sync the current hiring plan into a spreadsheet that you can use for financial modeling -- and refresh the data straight from ChartHop as needed.
Live queries are automatically tied to the credentials of the user who generated them, so if that person's access is removed from the organization, the live query will automatically invalidate.
Query URLs expire automatically if they have not been used in 30 days. If a query URL expires, you need to re-create the query URL and link it again to a new spreadsheet.
Once you sync a live query to a spreadsheet outside of ChartHop, anyone with access to the spreadsheet can see the data included in it, regardless of sensitive data settings within ChartHop. When you are finished using your spreadsheet, delete the file to ensure access to the query is secured until it expires.
Query URLs are randomly generated and impossible to guess -- but they are "public" because of how Microsoft Excel and Google Sheets retrieve data.
Use caution when generating or pasting live queries. Do not share them outside of your organization. Remember that when unused, the query URL expires after 30 days.
Within ChartHop, create a query (based on a Data Sheet view) to use in either a Microsoft Excel file or a Google Sheet. You can then use the query in the respective spreadsheet type.
- Go to the Data Sheet page and do one of the following:
- Select an existing Data Sheet view
- Create a new Data Sheet view to sync
- From the Edit menu, select Sync to Excel/Sheets from the Edit menu.
- Select Create Query.
- In the Sync to the spreadsheet dialog box, either download the linked query file for Microsoft Excel or copy the query URL for Google Sheets.
- Select Close.
You'll need to install the ChartHop Sheets extension before you can use your query URL from ChartHop.
- Select Install.
Prerequisite: Before adding a ChartHop query to a Google sheet, you need to install the ChartHop Sheets extension.
- Go to your Google sheet.
- Select Extensions, ChartHop Sheets, Insert Data.
- Paste the query URL and select OK.
You can refresh the data in your Google sheet when you want to ensure your spreadsheet is up-to-date with ChartHop data. Remember that queries and the data they populate automatically expire after 30 days.
- Go to your Google sheet.
- Select Extensions > ChartHop Sheets > Refresh Data.
You can sync a ChartHop query to an Excel spreadsheet. Syncing to a query is only supported when using the Excel desktop app. Syncing data to Excel for the web is not supported.
Be sure you have already downloaded the query file you created in ChartHop.
- Open your Microsoft Excel desktop app.
- On Windows, Select Data > Existing Connections > Browse. On Mac, Select Data > Get External Data > Run Web Query.
- Browse to the .iqy file you downloaded and select Get Data.
- Select New Sheet or choose an existing spreadsheet and select OK.
You can refresh the data in your spreadsheet. When you refresh, the spreadsheet will sync again to all current query data in ChartHop.
- Open your spreadsheet in the Microsoft Excel desktop app.
- Select Data > Refresh.
