Google Sheets makes it easy to share and store important data in spreadsheets. Grow allows you to make the most of the data in your Google Sheets files by transforming it into clear, powerful visualizations. This article will give you an overview of the Google Sheets integration
NOTE: This data source is available for accounts on the Basic and Professional plans.
How to Connect
Before starting, please collect your Google login information and sign in.
- Log in to your Grow account and select Add Metric. You will need to authorize your account before you can build metrics.
- Click connect and login through Google.
And that's it! You are good to go.
Google Sheets is a little bit different from other custom integrations. Instead of having set endpoints to build from, you can build metrics from the spreadsheets that you have access to in your Google Sheets account.
FAQs + Tips and Tricks
It's not Google Drive.
You'll have access to all of your Google Sheets, but not to your Google Drive files. If you have files you want to use, you can convert them to the Google Sheet format ("Open with Google Sheets"), use the File Upload option, or connect another storage service like Box, DropBox, or OneDrive.
Accessing Shared Sheets
If a sheet is shared with your account, you can still access it it in Grow. You don't have to be the creator to be able to use that data.
One use of this that we've seen is setting up a specific Google Sheets account to connect to Grow, and only sharing those sheets with that account. That way only the data that you need to use in Grow will be accessible.
One Sheet per Report
If you have a spreadsheet with multiple sheets, you need to create a report for each sheet.
Uniquely Named Headers
If there isn’t a unique column header in your chart, you will have difficulty using Grow’s transform tools as we wont know which columns to filter, group, pivot, etc. Read more about how to format your spreadsheets to use in Grow.
Google places has some size limits on spreadsheets. If you are getting close to any of these limits, you'll get a warning at the top of your spreadsheet (in Google Sheets) telling you which limit you are about to reach. The sheets can have a total of 400,000 cells across all sheets (within one spreadsheet), 256 columns per sheet, 40,000 cells containing formulas, and 100 sheets per workbook.
Remove Colons from Spreadsheet Titles
There shouldn't be a colon in name of the spreadsheet. If you try to pull the sheet into Grow, the app will return a "totalRowCount Undefined" error.
Dynamics Dates in Google Sheets
Google Sheets has some functions that can help you make your sheets more dynamic.
Start of Week (Sunday)
If you want to return a date that sets Sunday as the beginning of the week, enter the following function into your spreadsheet:
- =today()-WEEKDAY(today(),2) will give you the Sunday of the current week.
- =A1-WEEKDAY(A1;3) will return the Sunday from two weeks ago, assuming you entered the previous function into cell A1.
In regular person English, today() returns the current date as a date value, and WEEKDAY(date,type) tells Google to number the days either starting on Sunday or Monday. If you want to read up a bit more on this function, Google has a help article here that explains a bit more about it.
Start and End of the Month
If you want a cell to return the beginning of the month:
- =EOMONTH(today();-1)+1 will give you the first day of the current month.
- =EOMONTH(today(),-2)+1 will give you the first day of last month.
- =EOMONTH(today();0) will show you the last date of the current month.
Let's unpack some of that jargon: the function syntax is EOMONTH(start_date, months). Telling the cell to start at today's date, and leaving with a 0 actually returns the last day of the month, but a -1 inside the parenthesis takes you back one month, and the +1 outside of the parentheses takes you forward one day to the first day of the current month. You can alter these numbers to tell the cell to return any month you want to, relative to today's date.
For the extra inquisitive, Google has another helpful article here about the EOMONTH function.
Renaming Tabs or Sheets
Changing the name of either the Google Sheet itself or any of its tabs will not break the metric or the connection.