In this post we’ll be setting up our AppSheet spreadsheet.
AppSheet supports Excel, Google Sheets and Smartsheet as spreadsheet plug ins. We’ll be using Google Sheets in this series.
You can think of the spreadsheet as your database and AppSheet as your interface.
When organizing your data into your Google Sheet, AppSheet recommends thinking about structure in terms of entities and their properties. For example, an entity could be a customer and properties of the customer could be name, address, contact person, etc.
In Part 1 of this series, we discussed how to figure out what types of information would be useful in an AppSheet app for our fictional company, GTA HVAC Services. What could we pull out as some entities that would be useful? Here’s my list:
- Work orders
- Purchase orders
Fleshing it out further, some useful properties of our entities:
For work orders:
- Job number
- Assigned to technicians name
- Customer name
- Customer address
- Customer contact name
- Customer contact phone number
- Reported issue
- Drive time start
- Site time start
- Site time end
- Drive time end
- Parts used
- Part number
- Part description
- Part barcode
- Inventory quantity
- Technician name
- Technician location
- Technician email
- Customer name
- Customer address
- Customer contact info
For purchase orders:
- PO number
- Order date
- Vendor name
- Requested by
- Items ordered
- Items received
The good thing about figuring out entities and their properties is that they can be changed relatively easily when developing your application. It’s easiest if you take a list like this and just get started. Things will always change and adapt over time, so don’t wait until you think your structure is perfect before starting.
Building the spreadsheet
Let’s build our initial spreadsheet. I’m assuming you know how to set up a Google account and use a Google Sheet.
When setting up the spreadsheet you can think of each “sheet” within your main spreadsheet as a table. In AppSheet each entity should have its own table. Let’s call our spreadsheet “GTA HVAC Services work order app”.
We’ll set up our sheets and label them as the 5 entities we’ve decided we currently need. You can change the color on your sheet labels if you helps you visually organize.
Next we need to plug in our properties. In AppSheet, each property should have its own column header in the appropriate sheet.
Take a look at the example image showing headers for the work order table and properties.
This is all we need to do to start! Your spreadsheet is set up.
There’s a few quick items that will help you save time later on.
- Format your column header text in bold type. This will help AppSheet better detect your property names and types when it sets up the app from your spreadsheet.
- For smaller applications, keep all of your headers in the default “Sheet 1”. AppSheet will automatically detect your data types from your headers. When using multiple sheets, subsequent sheets, ie. “Sheet 2,” will require a double check to ensure data types are chosen correctly by AppSheet. We’ll go over what these data types are in the next lesson.
- If you have 5 or 10 rows of data to plug into each of your tables, it will assist AppSheet in determining the data types of your column headers.
Next we’ll dig into how to ensure your entities and properties have have the correct data types!