Google Sheets testing
Overview
Mode’s Google Sheet integration allows users to connect to their individual Google Accounts to upload Google Sheet data directly into Mode Datasets for reporting and visual exploration.
Requirements
- Before you get started, you’ll need to make sure the Google Sheets Integration is enabled for your Workspace. Admins can enable the feature from the Google Sheets feature page in Workspace Settings.
- Once the feature is enabled, Workspace Members can connect Mode to their individual Google Accounts from the Google Account page in Workspace Settings.
Tips when preparing your Google Sheet data for use in Mode
- Avoid using merged cells, and ensure each cell only contains data (graphs and images won’t be uploaded).
- Add headers to the first row, as column names will be auto-generated from the first row.
- Consider using a dedicated worksheet tab to prep and narrow down the scope of your data. When you upload a Google Sheet into Mode, the entire range is used (including any hidden and filtered cells). TIP: Use Google Sheet functions to copy data from one tab to another more quickly. For example:
='Worksheet 1'!A1- copies the contents of cell A1 in Worksheet 1
Creating a Google Sheet Dataset
To create a new Google Sheet Dataset, select the Create button in the top right corner of your Mode home screen and choose the Upload a Google Sheet option in the Datasets section.
Alternatively, navigate to My Work and click the shortcut tile to Upload a Google Sheet.
This will open up the Google Sheets upload modal, where you can browse for and select individual Google Sheets. If you haven’t connected to your account before, you’ll first be prompted to do so.
Click on the Set up account button to access the Google Account page in Workspace Settings. From here, you can give Mode access to your Google Drive account.
Once connected, Google Sheets you have access to will appear in the upload modal. From here, you can browse and search for the Spreadsheet you’d like to use, or paste in its URL. After you’ve selected a Spreadsheet and Worksheet tab to upload, you can also give your Dataset a name and description. When you’re finished, click Create Dataset.
After your Sheet data has been successfully uploaded and your Dataset has been created, you’ll receive a notification that it’s ready for use. From here, you can choose to edit the Dataset directly or start visually analyzing its data in a new Report.
Editing your Google Sheet Dataset
Once your Google Sheet data is uploaded, you’ll be able to see the results in the Data View. This shows you the columns that were generated from the Google Sheet.
You can also see a list of all of the available fields in the Dataset from the Fields tab.
To pull new data in from the underlying Google Sheet, click the sync button.
Describing the fields in your Dataset
We recommend adding field descriptions to all Workspace Datasets to build shared understanding around business and logic definition.
To add a new field description, edit any Dataset and navigate to its Fields tab. From here, double click on the row you’d like to edit. Descriptions support up to 350 characters, and text formatting such as bolding, italicizing, emojis, line breaks, and hyperlinks.
Once added, field descriptions will appear in the Fields tab of a Dataset and when hovering over the fields list in the chart builder.
Here are some use cases to think about when adding field descriptions to Dataset fields:
| Use case | Sample field | Sample Description |
|---|---|---|
| Shared understanding Codify business definitions and logic, especially when using calculated fields | Engagement rate | Calculates the % of users who are engaged, where engaged is defined as any user who has logged in and taken >1 action in the past 14 days |
| Expectation setting & data previews List out the possible values you’d expect the field to return | Order status | Segments orders by canceled, completed, or returned |
| Disambiguation Help colleagues quickly discern between fields with similar names or meaning, ex. Organization (business) vs Customer (user) | Order amount | Total amount of order supply placed per order, ex. 8 units of paper ⭐️ Tip: if you're looking for Total price, use Order price |
| Education Provide best practice and how-tos when charting. Consider including links out to sample Reports or charts | Total signups | Shows the total number of signups by week for every single row. ⚠️ Note: be careful when using to not overcount total signups. Use this field only when you have the data aggregated by week. See example chart here |
Adding calculated fields to your Google Sheet Dataset
You can add new calculated fields to the Dataset from the Fields tab. Doing so will add the calculated field to the list of available fields, and also make it available in any reports created from the Dataset.
To add a new calculated field, first select the New field button.
Then enter the formula for your calculated field, along with adding a name. To save the calculated field, select the Apply & Close button.
Viewing source information
Information about the underlying Google Sheet that was used to generate the Dataset can be accessed from the Source tab.
Updating your Dataset’s name & description
You can change the name and description for your Dataset at any point. To do so, select the caret next to the Dataset name in the header. From the dropdown, select Rename.
Enter the desired Dataset name and description. Then select Save. We recommend using consistent naming conventions and adding detailed descriptions to your datasets. Doing so will help other team members find and understand how to use the Dataset.
Scheduling a Google Sheet Dataset
You can set a schedule for your Dataset to sync in data from the underlying Google Sheet on a cadence. After a Google Sheet Dataset finishes syncing, all associated Reports built using the Dataset will be able to pull in the fresh data.
To create a new schedule, select the caret next to the Dataset name and choose Schedule. Then, select Create New Schedule to open the scheduling options. From here, you can set the refresh frequency, as well as the specific time and timezone.
Moving a Google Sheet Dataset to a Collection
The final step when creating a Dataset is to move it into a Collection. You can think of this action as publishing the Dataset, as it makes the Dataset available for other team members to access and use it.
To move the Dataset to a Collection, select the Move to a Collection button in the top right corner of the Datasets editor.
This will open a modal displaying all of the available Collections.
Select the Collection you want to add the Dataset to, then select Move.
Viewing a Google Sheet Dataset
To view a Dataset you’ve just created, select the View button in the top right corner of the Datasets editor.
From here you can view the Data, Fields and Source tabs, Dataset details, as well as export or copy the data.
In the Details pane, you can see information about the Dataset including the Collection it lives in, description, when it was last synced, any schedules it has,and which Reports are built from the Dataset. To view a list of Reports created from the Dataset, select the Used in link to open a modal displaying all child Reports.
Reports you don’t have access to will still appear in the count, but will be obfuscated/un-viewable.
Using your Google Sheet Dataset in a Report
You can add a Dataset to any Report for which you have edit access. There are a few ways to use your Google Sheet Dataset in a Report:
-
After your Sheet data has been successfully uploaded and your Dataset has been created, you’ll receive a notification that it’s ready for use. From here, you can choose to edit the Dataset directly or start visually analyzing its data in a new Report.
-
While editing or viewing a Google Sheet Dataset, click the Use in New Report button located in the header.
-
From within an existing Report, select the + Add Data button located in the left navigation panel. This will open up the Datasets browser so you can search for existing Datasets to add to your Report.
NOTE: Personal Datasets won’t appear in the Datasets browser or when searching, and can only be added to existing Reports via URL. To make your Google Sheet Datasets more discoverable, move them to a Workspace Collection.
Once your Report is created and the Dataset is added, a flat table visualization will also be created by default. You can filter and sort the data on the table visualization or create additional visualizations using the data in the Dataset.
Refreshing data in a Dataset-based Report
New Google Sheet Dataset data can be synced into Mode via a Dataset schedule, or manually from within the Datasets editor. When you run your Report or refresh an individual Dataset from within a Report, Mode will check to see if there’s a newer Google Sheet Dataset sync available, load it in, and snapshot its results within your Report’s Run History.
Datasets will be badged in the Report edit view when there is a fresher sync available, so you know when to refresh your Report.
Google Sheet Dataset permissions
After you’ve authorized access to your Google Account, you’ll be able to browse and upload any Google Sheets you have access to in Google Drive.
Once you upload a Google Sheet into a Mode Dataset, access to the Dataset will be determined solely by the Collection it lives in. Datasets you create can continue to sync new data in from Google so long as you’re connected to your Google Account.
FAQs
Q: Can I access my Google Sheet Datasets in the SQL editor for querying/joining?
No. Currently, Reusable Datasets can only be used for visual analysis via Quick Charts and the Visual Explorer. The ability to query and join Reusable Datasets is on the roadmap, and will be introduced as a future feature.
Q: Can I access my Google Sheet Datasets as dataframes in the Notebook?
Currently, only query results within a given Report can be referenced as dataframes. However, the ability to reference Datasets that you’ve added to your Report as dataframes is a feature on the roadmap and will be coming soon!
Q: Can I add custom HTML to Reports that are using Google Sheets Datasets?
You can customize the styling of your Report’s layout using the HTML editor, but any custom JavaScript including example gallery code that uses alamode is unsupported for Reports using Google Sheets Datasets.
Q: How big can my Google Sheets Datasets be?
Google Sheet Datasets adhere to your Workspace’s data plan, and follow the same per query result cap. Google also limits the amount of data per Sheet to 10M cells, or up to 100MB.
Q: Can I sync my Google Sheet Datasets to Github?
Currently, only query-backed Datasets can be synced to Github. There is also a known bug when syncing Reports that contain Google Sheet Datasets to GitHub, causing any Google Sheet Datasets to appear as empty .sql files.
Q: If I make changes to my underlying Google Sheet, how are they reflected in Mode?
To update your Google Sheet Dataset to reflect any changes made to the underlying Google Sheet, click the Sync button in the Datasets editor or set up a schedule to sync new data in on a schedule.
Q: Can I select a range of data to upload when creating a Google Sheet Dataset?
No. Today when you select a Google Sheet tab, the entire tab is uploaded (including any hidden or filtered cells). You might consider using a dedicated Worksheet tab to prep and narrow down the scope of your data.
Q: Can I upload multiple Sheets at the same time?
No, you can only select one Google Sheet tab to upload at a time, as Datasets can only contain a single table.
Q: What happens if I delete a Google Sheet Dataset?
The Dataset will be permanently removed from your Workspace, and any dependent Reports, Charts, or Calculated Fields will break
Q: What happens to my Google Sheet Datasets if I disconnect my Google account from Mode?
Disconnecting your Google Account will prevent you from creating new Datasets using Google Sheet data until you reconnect.
Existing Datasets built on the connection will continue to use the last successful sync but won’t be able to sync in new data unless you reconnect, which could break existing schedules.
Q: As an admin, what happens to existing Google Sheet Datasets if I disable the Google Sheets integration for my Workspace?
Disabling the Google Sheets integration will log out any Members currently connected to Google, and prevent them from re-connecting or creating new Datasets from Google Sheet data.
Existing Datasets built via the Google Sheets integration will continue to use the last successful sync but won’t be able to sync in new data, which could break existing refresh schedules.
Was this article helpful?