Spreadsheets typically consist of either automated exports from Grow or files you update manually. Generally, an automated export of your data will be formatted correctly in Grow; however, if you are manually entering data, you may need to do some prep work.
Here are a few basic principles to formatting spreadsheets:
- Google Sheets cannot have a colon in the name of the spreadsheet. This will return the error "totalRowCount Undefined."
- The spreadsheet should be set up so the dates are in a column. This way you can filter for specific date ranges and select the data to display much easier.
- Column headers belong in the top row (row 1).
- All column headers need to be unique.
- Remove any empty rows or columns.
- Dates should be in YYYY-MM-DD format.
- Currencies and percentages should just be number values. Do not include special characters.
- Remove any commas from numbers.
- Categories and values need to be consistent. For example, if you have a column of states or cities, make sure they are all using the same spellings or abbreviations.
- Do not include a "Total" row.
Here are two examples of spreadsheets before and after applying these guidelines:
Cleaning Up A Spreadsheet
This video will go through the process of fixing up the most common formatting issues that could cause issues in Grow:
One Sheet per Report
For Excel-based files, you can connect one sheet per report. If you want to connect multiple sheets from the same Excel spreadsheet, you will need to create one report for each sheet. The easiest way to do this is duplicate a report that's already linked to your file, select the new sheet you want to connect, and click Get Data.
Uniquely Named Headers
If there is not a unique column header in your chart, you will have difficulty using the transform tools as we will not know which columns to filter, group, pivot, etc. If it is not feasible to reformat your existing sheet to add column headers, consider creating a separate reference tab within the same spreadsheet, adding appropriate column headers, and linking the columns to your reference data.
File size Limits
Most files must be smaller than 15 MB in order to successfully pull them into Grow. If you try to connect a file over this size, you may encounter an error. If possible, consider some of the following suggestions to reduce the size of your spreadsheet:
Remove unnecessary worksheets, data, and charts
Convert your file to a CSV
Compress or remove images
Avoid/remove volatile formulas
RAND, TODAY, OFFSET, INDIRECT, CELL, NOW, INFO, etc.
Delete unused rows/columns
CTRL+END (PC) or CONTROL+ FN+Right Arrow (Mac), right click and select Delete
Supported File Formats
XLS, XLSX, XLSM, and CSV. If you're looking to connect JSON or XML files, Grow can pull those in, but we suggest converting to a CSV first.