Introduction
This guide provides the steps necessary to connect and use Google Sheets on Oviond, including permissions, limitations, and troubleshooting.
Prerequisites
Ensure you have an Oviond account with administrative access.
You need login credentials to a Google Account that has access to the spreadsheet.
Google Sheets Permissions
To connect your Google Sheets account to Oviond, you need to have login credentials to a Google Account that has access to the spreadsheet. There are no permission limitations when connecting Google Sheets with Oviond. The spreadsheet doesn't need to be shareable in order to pull data into Oviond.
Step-by-Step Instructions
Connecting Google Sheets on Oviond
Open Oviond and navigate to the client you wish to connect Google Sheets to.
Select Project or Start a New Project
Select the project you want to work on or start a new project.
If starting a new project, it will automatically open in the Workspace Editor.
Access the Project Workspace Editor
Click 'Edit' to access the Project Workspace Editor if you are working on an existing project.
In the action sidebar menu on the right, select Data Sources.
Select Google Sheets.
An authentication pop-up will appear (The first authenticated account will automatically be called "Default Google Sheets Account") and you can change this in Data Sources.
Click the Google Account you wish to connect to and then click 'Allow'. Follow the prompts in the pop-up window.
Choose 'Default Google Sheets Account' in the integration connection pop-up on the right then click 'Next'.
Choose 'Default Google Sheets Account' and click 'Save'.
Note: Connecting a Google Account will give you access to all the spreadsheets that the email address has access to.
Prepare your Google Sheets source file
The formats that are used in your spreadsheet will be ignored when using the query builder for a Google Sheets widget. You will choose the formats in the Metric Formats section of the query builder.
You shouldn't include any symbols in your spreadsheet as this will affect how the data appears and is calculated in your widget on Oviond (don't use currency or percentage symbols)
Note: Columns including dates need to be formatted in the spreadsheet - the date format must be YYYY-MM-DD
Create a table chart using Date as the dimension with a summary row - Example
For this example, we will create a table from the Google Sheets data, with a summary row showing totals.
Select the Google Sheet
Select the Sheet you would like to use
Select a dimension. This is important as this is how your data is split. As an example, if your dimension is Date and your metric is Sales, you could track sales over time. If your dimension was Device and your metric was Visits you should show Visits by Device
Set your chart type to Table, give the widget a name, and set your default sort direction.
Configure your metric formats
Toggle Show Totals to 'On'
Metric Formats
One of the features that make our Google Sheets integration so powerful, is we give you the ability to assign formats (Currently number, percent, currency, text, and date) to columns and then define how the summary is aggregated (Currently sum, average, count, max, and min) for metrics.
Create a line chart for sales over time using Date as a dimension and Sales as the metric - Example
For this example, we will use our Google Sheet to track sales over time. We will use the Date column as our dimension and the Sales column as our metric.
Select the Google Sheet
Select the Sheet you would like to use
Select a date as your dimension
Set Sales as your metric
Select Line Chart
Name your widget
Configure metric formats
Date format to Date
Sales format to Number and summary to Sum
Create a scorecard for the Average Conversion Rate over a selected date range, using Date as the dimension and Conversion Rate as the metric - Example
For this example, we will use our Google Sheet to create a scorecard showing the average conversion rate over a selected date range. We will use the Date column as our dimension and the Conversion Rate column as our metric.
Select the Google Sheet
Select the Sheet you would like to use
Select a date as your dimension
Set conversion rate as your metric
Select scorecard
Name your widget
Configure metric formats
Date format to Date
Conversion Rate format to Percent and summary to Average
If you have followed these steps and are still experiencing issues with Google Sheets on Oviond, please contact support.
Summary
Following these steps will help you successfully connect Google Sheets to Oviond, enabling seamless data integration for your dashboards and reports.
Related Resources
How to connect a data source from a widget
Learn how to connect a data source from a widget in Oviond with this step-by-step guide for seamless data integration.