AppSheet Apps

Recently a colleague created a spreadsheet but was unaware of the power lurking underneath. Google Sheets include a very comprehensive toolset that can be used to extend functionality.

Overview

In this blog post learn how to access a Google Sheets document to extend functionality. To get started there are a couple of things to know.

• You dont need to know how to program to extend Google Sheets
• Spreadsheets have security built into them, so you need to be an authorised user to access them

For the purposes of this blog, the following actions are required:

1. Create a Google Sheets spreadsheet
2. Introduce AppSheet
3. Enhance the functionality of the spreadsheet

Create a Google Sheets spreadsheet

Imagine we have a business that requires apppointment bookings. As part of the booking process, we need to take the Customer details and the date and time of the requested appointment. Appointments are a duration of 1 hour.

1. Open a Google Sheets and add the above data
2. Rename the tab as data
3. Rename the worksheet as Appointments

For appointment information we take the First Name, Surname, Date and Time.

4. Enter the following data into the spreadsheet

NOTE: In the above table:

• Id is a reference to the appointment
• Date is in the format DD/MM/CCYY
• Time is 24 Hour format

AppSheet Introduction

AppSheet is a no-code tool for GSheets. It enables applications to be built using Spreadsheets.

Pricing varies based on usage, further information can be found here

Watch this YouTube video on AppSheet

NoCode development with AppSheet

From the open spreadsheet, go to the Tools bar and select AppSheet. Run the generated app. It is clear that AppSheet has inferred alot of functionality from our GSheet.

Without writing any code we can display the information from the GSheet and we can also update the values in the spreadsheet!

Note: Accessing a document requires authentication.

Appointment Listing

Now that we have a basic app, update the view to sort the details. Use the UX menu item to update the information.

1. Select the Views/View Option
2. Amend the Sort by field to use the Surname field with ascending order
3. Add a Group by option using the Date with ascending order
4. Add a second Group by option using the Time with ascending order

Now the appointments in the Google Sheets are displayed in chronological order.

Note: Google Sheets also allows users to "Share" documents with others via a email address. Adding guest authentication will be covered in another blog post.

Test: AppSheet refresh

Now the application has been built a two way synchronisation exists. Add a new appoint to the GSheet to ensure the application can be refreshed with update information

1. Update the GSheet with a new appointment

1| Id | FirstName | Surname | Date  | Time |
2|----|-----------|---------|-------|------|
3| 4  | Deborah   | Qwery   | 23/07/2021 | 13:00:00 |

2. Refresh the AppSheet application
3. The updated data will be available

Test: GSheet refresh

What about if the application is updated via the App? Does the GSheet also get updated?

1. Use the AppSheet application to create a new appointment

1| Id | FirstName | Surname | Date  | Time |
2|----|-----------|---------|-------|------|
3| 4  | Evan      | Penfold | 23/07/2021 | 14:00:00 |

2. The updated data will be added and also available in GSheet