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.
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:
- Create a Google Sheets spreadsheet
- Introduce AppSheet
- 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.
- Open a Google Sheets and add the above data
- Rename the tab as
- Rename the worksheet as
For appointment information we take the First Name, Surname, Date and Time.
- 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 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.
Now that we have a basic app, update the view to sort the details. Use the UX menu item to update the information.
- Select the Views/View Option
- Amend the Sort by field to use the Surname field with ascending order
- Add a Group by option using the Date with ascending order
- 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
- Update the GSheet with a new appointment
1| Id | FirstName | Surname | Date | Time | 2|----|-----------|---------|-------|------| 3| 4 | Deborah | Qwery | 23/07/2021 | 13:00:00 |
- Refresh the AppSheet application
- 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?
- 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 |
- The updated data will be added and also available in GSheet