We’ve come a fair ways now. We’ve discussed how to think about business processes in the context of building an AppSheet app, set up our Google Sheet and connected Appsheet to our Google Sheet. It’s time to start hacking together our app!
AppSheet will infer your data types from your Google Sheet using a few different methods. Easy things like bold typing your headers and ensuring your entire column spreadsheet column has a consistent data format help. I’ve found that most of the time AppSheet gets it right. But you’ll want to double check them all to be sure.
AppSheet already has a ton of great documentation outlining all of its possible data types so we won’t review it here. But we will double check all of our table columns to ensure they are correct.
So how do we do this?
The first thing you’ll want to do is login to your AppSheet account, go to your My Apps tab and find your app. Click on the Edit button to open it.
Once your app opens, click on the Columns tab at the top and then the Work orders table.
The Work orders table will open and you’ll notice a left column titled Name that will match the headers of each column in your Google Sheet. The column immediately to the right will be titled Type. Each row will have a type associated to the column name. AppSheet figured these out when you connected your Google Sheet!
So we can unpack this a bit. We have the following columns and data types in our app:
_RowNumber – you didn’t add this column explicitly in your spreadsheet but AppSheet will add it so it is able to identify the row number being referenced in the table at any time. It is as it reads, the number of the row.
Date – let’s look deeper into this one. In the image above we can see AppSheet picked the data type of Date for date. Sounds about right. If you click on the square with the pencil overlaid immediately to the left of the Name column, it will allow you to drill deeper into the details of that column.You can see a couple of examples below.
In the left image above, you can see the column name, a Show? toggle switch from which you can choose to allow users to see the column or not, and a Type pull down menu. The date type can be long date format or a numeric year/month/date format. There are options for determining if the data is valid or not and auto-compute options, one of which we are using, an initial value of TODAY(). This inputs today’s date into the date field of any new work order but also allows the user to change it if needed.
In the right image above, there are some further options, the Key option being the one I want to quickly highlight. When you connect a Google Sheet, AppSheet will try to determine a key column for any table or sheet. This column should have a value in each row that uniquely identifies the row. This ensures that when data is added, deleted or changed, AppSheet ensures it picks the correct row for the action to occur. Common sense tells us that multiple work orders can occur on a single date and we could end up with many rows having the same key. In this case AppSheet is incorrect and we’ll turn the Key toggle switch off. The next column, Job number would make more sense as a key column instead.
Job number – this is a data type of Number which make sense. We’ve also now made this our key column.
Assigned to technician name – this is an Email type because we input the technician email address into the initial rows in our Google Sheet. Maybe we’ll need to change the column heading to email instead of name. Or maybe we can figure out a way to show the name when referencing the email address in a row. We’ll leave this for now.
Customer name – Name type which makes sense.
Customer address – Address type which makes sense.
Customer contact phone number – Text type. If we click the Type pull down menu we’ll find a Phone type. We’ll change it to this.
Reported issue – Text type. Sounds fine. If we dig deeper though, we can find a LongText option which we’ll choose instead to give us the ability to enter more details regarding the service issue. It essentially gives us a bigger text box.
Drive time start – Time data type. Makes perfect sense.
Site time start – Time data type. Ditto.
Site time end – Time data type. Ditto.
Drive time end – Time data type. Ditto.
Parts – Text data type. This is fine if you want the technicians to be manually typing in parts details on each work order. But it would be more efficient to have them be able to pick inventory parts from your Google Sheet. To do this we’ll review adding a Parts table and using a Ref column in our next post. For now we’ll keep this as Text.
Status – Text. This is ok but it would require having a technician type in a phrase to determine if a ticket is open or closed. It would be better to pick what AppSheet calls an Enum type. This will allow the app creator to enter phrases as options that can be picked from within the work order. We’ll change this to Enum and enter Open and Closed as options. Check out the images below.
You can see we picked Enum from the pull down. In the right hand image, there is a Values field where we enter Open and Closed as our values. You can allow other values via a toggle switch but if you want to tie any events such as sending a work order completion form to your job status, it’s best we only allow certain values. We’ve picked Buttons as our Input mode at the bottom of the image.
We’ve gone through all the data types in our first app table. All other tables as you add them would follow the same process. We’ll do a short post next showing how to add a new table and reference it to another. Specifically to allow our technicians to pick inventory parts from a pull down menu in their service tickets.