Articles in this section

See more

Formatting Spreadsheets for Use in Grow

Overview

Spreadsheets typically consist of 1. Automated exports from Grow or 2. Files you are updating 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.

This applies to spreadsheets in Dropbox, Google Sheets, Google Drive, OneDrive, Egnyte, Box, Amazon S3, File Uploads, or FTP/SFTP connections.

Contents in this Article:

Basic principles

Here are a few basic principles to formatting spreadsheets:

  1. Google Sheets cannot have a colon in the name of the spreadsheet. This will return the error "totalRowCount Undefined."
  2. 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.
  3. Column headers belong in the top row (row 1).
  4. All column headers need to be unique.
  5. Remove any empty rows or columns.
  6. Dates should be in YYYY-MM-DD format.
  7. Currencies and percentages should just be number values. Don’t include special characters like $, £, €, %, etc.
  8. Remove any commas from numbers.
  9. 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.
  10. Do not include a "Total" row. 

Here are two examples of spreadsheets before and after applying these guidelines:

spreadsheet_formatting.png

spreadsheet-formatting2.png

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 isn’t a unique column header in your chart, you will have difficulty using Grow’s transform tools as we won't know which columns to filter, group, pivot, etc. If it's 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.

Related articles