It is possible to create much richer visualizations of data that is captured via an AppSheet app. In this post, I will describe the case for why it can be beneficial in the context of our latest project where we are working with a Golf Car Rental company who have many types of golf carts that they rent out. A customer can call the rental company and request (up to the maximum number of available carts) 8 different types of carts (standard or 2 passenger, four passenger, six passenger and utility) which may either be Gas or Electric for any length of time. Sometimes the bookings are made up to a year in advance by clients and it is hard for the owners to forecast if they are going to be in short supply of a certain type of cart in their inventory.
A typical booking view in a spreadsheet may look as shown in the graphic below. There are 8 cart types that are tagged as SE, SG, FPG etc.
While this table can be easily turned into an app, the issue of how best to view the bookings over a given period of time and how best to know how many carts of a given type would be available in order not to end up overbooking became an interesting challenge to do within the app. There were three main challenges:
- How to visualize the above tabular data as more of a timeline?
- How to shape the booking data into a table where the number of carts booked (or available) every single day can be depicted as a line or bar chart? (Note: one can create bar and line charts in AppSheet only if the data to create these charts is in the right row/column format).
- How best to use the data created in 2 above to visualize the data?
Visualizing a timeline
There are many different kinds of charts supported by Google charts and a Timeline chart is one of them. The solution we came up with is to read the data from the spreadsheet that is attached to the app and use the Google Visualization API to create a chart as shown in the chart below. Color coding each booking as well as the distinct number of carts of a particular type helps to draw attention to key information that is needed to get a big picture view for a month, two months or whatever duration of interest. Adding a slider to control the start and end dates makes it easy to zero in on exactly the dates of interest.
Shaping data to create an expanded table
Whereas the booking table is simple and contains start and end dates and number of carts of each type booked, we need a table that shows for every day how many carts of each type have been booked. The desired table is shown below.
Although it may be possible to create this table from the booking table using sheet formulas, the solution we came up with is to write a custom function using Google Apps Script (GAS).
Visualizing cart availability
A stepped area chart allows for plotting the number of carts of each type available on a given date or date range. Color coding each cart type and stacking the area chart for each on top of each other provides for a compact visualization of cart availability. Adding a slider to control the start and end dates makes it easy to zero in on exactly the dates of interest. The chart that was linked to from within the app is shown below.