How to Properly Format a Google Sheet or CSV File for Reference Data

Reference data is a handy automation tool that uses data uploaded to your account to automatically populate fields. Reference data makes it simple to keep your forms up to date, consistent, and error-free. Automating this on the back-end will save you loads of time in both the field and the office.

With reference data, users will simply choose the right option from a list, rather than try to memorize it. Reference data can be implemented for many use cases, such as:

• Customer database,
• Product and pricing information,
• Inventory,
• Employee database,
• Other data specific to your business or industry.

This is not an exhaustive list and there are many creative ways that reference data can be utilized. We recommend applying it to your forms early and as often as possible. 

Two Purposes for Reference Data

Before we start formatting our spreadsheet, we should consider how this data will be used in the form and which of these two functions are needed for the use case:

• Populate a Drop Down list: Use a reference data file to provide the values for a drop down field so you don't have to type them in manually. Keep one file up to date that can be automatically pushed to every place that needs the same drop down menu choices.
• Auto-Populate Fields: Autofill a field based on a value chosen in a previous field. So when you choose a product from a drop down menu, the price, description, model number, and more, can fill in automatically based on the product chosen. 

Formatting the Spreadsheet

In order to get to the good part, automatically populating fields, building the reference data file properly is the very important first step. Luckily, it’s the same format whether you use Excel or Google Sheets. For this example, we will use Customer Information as it is an incredibly common use case and demonstrates this feature well. 

Our first field is "Customer Name," which will be called the parent field in future steps. When building either the form or the file, the parent field should be a unique identifier that will be obvious to your users. This field should be what your users could confidently pick from a list. For example, they may know an item name or description but not it's serial number so the item name or description should be the parent field and not the serial number.

After determining the parent field, the rest of the field labels are filled in across the first row. These labels determine not only the data that will fill down the column but the order is also relevant for dependent reference data.

We recommend labeling the column headers either exactly as the fields are labeled in the form or with distinguishable information, like "Billing Address" and "Shipping Address" if there are multiple Address fields within the form. You will see in future steps that this attention to detail will make it easier when associating the columns in the file to the fields in the form. 

Once the labels are added, fill in the corresponding data down the column so that related information goes across one row.

Note: the Default Value setting Blank Default in both Advanced Form Builder and Builder BETA eliminates the need to leave the second row empty as was required in previous iterations of Reference Data. 

Each file can have up to 15 columns of data. If you need additional columns, another separate file will be needed. Check out the article on how to properly format and connect these files for seamless data population in the form. 

If you used Google Sheets, your file will automatically save and be in the format that GoCanvas can read. If you use Excel, you will need to Save As a Comma Separated Values (.csv) file type. 

Note: Do not use the CSV UTF-8 (Comma Delimited) (.csv), or any specialty .csv file (i.e. Macintosh Comma Separated, MS-DOS Comma Separated, etc.) as these file types may introduce random characters or not work as expected.

The next step is to either upload your file to the Advanced Form Builder or to the Reference Data & Images tab, as is required if you are using Google Sheets or Builder BETA.