Google Sheets can be a powerful to for visualizing metrics, particularly when you have a lot of data that doesn't reside within a specific cloud-based app. Here are some things you can expect as you pull your spreadsheets into Grow.
- Changes in Google Sheets: When you make a change in one of your Google Sheets, the change is automatically reflected in Grow as long as you're logged into your dashboards. There's no need to reconnect or upload anything again.
- Spreadsheet Formatting: Spreadsheets need to be formatted a certain way, with clearly labeled column headers, in order for them to work in Grow. This is especially important when dealing with dates and numbers. To make sure your spreadsheets are formatted correctly, check out our help article on here. Additionally, 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.
Dynamic 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.
Other questions about using Google Sheets in Grow? Email us at firstname.lastname@example.org or chat in on the site.