Building A Facilities Access Records Tracker App Using Glide & Google Sheets
In this article, I walk-through how I built a simple building access records management mobile app using a low code solution. Interested ? Well then, let’s get started….
Why This Topic In Particular ?
Unless you’ve been living under a rock or been on a hermitage somewhere up a mountain/ or in a desert wasteland , you’ve probably seen or used a “Safe Entry” system or some variant thereof when entering a mall or maybe your office (assuming your office is still even open).
So this latest project is sort of related — it’s also an offshoot of a conversation with an ex colleague where we got to talking about how difficult it would be really to set up a cheap and cheerful solution for a Building Entry/Exit Management system with some LIMITED (*) Contact Tracing functionality that Small Medium Enterprises could apply. So rather than building something new from scratch I wanted to see if it could be done in Glide….
(* Note — This is just tracking which individuals stayed in the same room within a building and for how long — This is not the fancy anonymized Bluetooth detection stuff that Google/Apple are working on (read about that at the end of this article)
In an earlier post <https://www.linkedin.com/posts/eu-zhijing-25a4362_demo-glide-app-activity-6653494910852919296-lsRg> I described how I used Glide Apps to build a very simple project schedule/task management app with almost zero code that just runs off a Google Sheet.
Glide Apps is a pretty spiffy low-code solution that allows you to build your own apps that run off information stored on a linked Google Sheet.
A Walk Through Of How I Did It…
You can either watch this 5 minute video walk-through or read the rest of this article…
The first step is to set up a Google Sheet that stores the information you need to capture (Name, Room, Time In or Out, Temperature Readings , etc) like so:-
Inside the Glide app, I then created a Layout tab that can add new rows of info into the Google Sheet . Some of the useful functionality I applied was the “TimeStamp” component that references the user’s Device System clock and inputs that as the submission time and the Unique ID identifier (to create a run number for every entry/exit transaction)
[Note since this is only a Proof of Concept app, I didn’t bother with creating User Specific logins with different access levels so everyone can see all the functionality but Glide allows you to easily change how different users access your app and what yhey can see <https://docs.glideapps.com/all/guides/quick-starts/getting-started/controlling-who-can-access-your-app>]
The rest of the app is cosmetic in nature. I populated the Google Sheet with some data for a fictional company that has 10 employees working in a single building location that has 3 floors and 4 rooms (A,B,C,D) and set up some tabs that allow the user to :
i) View the Building Layout / Room Names
ii) View the Employee Directory (Name,Role,Contact,Workdesk-Location)
iii) View and Search entry/exit logs to see who has been in which room at what time (If I were developing this app for real, I’d probably set it up so only administrators can see this page but since it’s a demo I got lazy)
There are a few other bits I used such as the linked choice drop downs where certain fields (like the Temperature reading of employees) only triggers for the Main Entrance access gate.
However, by far the trickiest bit I had to figure out was how to allow users to scan a QR Code instead of having to manually input the Floor / Room information.
The problem is that Glide doesn’t have a native component that reads QR Codes (A lot of people have been asking for this though <https://community.glideapps.com/t/scan-barcode-and-qr-code-directly-in-glide-into-a-field-in-a-form/1132> )
Glide does however have a Image Picker component that allows users to upload photos that Glide will host for them. <https://docs.glideapps.com/all/reference/components/pickers/image-picker>
So my round-about solution was to set up a script inside Google Sheets (it uses Google AppScript which to me looks a lot like Javascript https://developers.google.com/apps-script/overview ) that does the following
a) grabs the uploaded image URL that Glide inputs into Google Sheets (It’ll look something like https://storage.googleapis.com/glide-prod.appspot.com/uploads-v2/ ########/pub/########.jpeg)
b) uses a function extractQRcode to send that URL to free API that decodes QR codes (Graciously hosted by GoQR.me at <http://goqr.me/api>) where I already created some QR Codes that are just plain text csv-s where each Floor/Room has it’s own code.
c) the function then returns the data into a variable and uses a regular expression (regex) formula to parse out from the QR code output (which will look like “[{“type”:”qrcode”,”symbol”:[{“seq”:0,”data”:”Level 1, Room A”,”error”:null}]}]” the relevant Floor and Room details and output them to the relevant cells in the Google Sheets.
Professional coders will likely grumble that this solution is ugly and probably not the most efficient way to do this. I agree — I suspect this may not scale if you make thousands of API calls per day which is probably the volume you’d have in real life. However since this is just a Proof of Concept app (Shrug?)
For anyone interested here are the links to to app itself and the supporting Google sheet.
https://dooraccessapp.glideapp.io
Conclusion : My Thoughts On Low Code vs Full Programming Solutions
You might argue that Glide has it’s limits and you would probably be correct as there are a lot of things in the background that you don’t have control over since the data is hosted on Google Sheets. HOWEVER as a hobbyist who has gone thru the “do-it-yourself” approach , my experience in building even the simplest of apps was quite painful. It took some effort to learn how to use Android Studio (I.e Spending hours wondering why Gradle kept crashing, hoping I didn’t miss some obscure dependency or leave out a “;” somewhere in my Java script) and how to configure Firebase as the back-end data store.
Therefore if I consider only the “difficulty in app-building” perspective , when comparing that experience to using Glide is a lot like buying milk from the supermarket versus raising my own cows, feeding them grass everyday , shoveling their poop and milking them myself — sure the milk is fresh and I know exactly where it came from but the sheer convenience of an off the shelf solution is amazing.
Despite of this I think it’s not a simple either-or decision. These low code solutions are a good entry point for application development as long as you understand the trade-offs between ease of set-up/maintenance versus flexibility/security/infrastructure control. Just like how Windows GUI replaced DOS and opened up general computing for everyone by making it simple, Low Code solutions (i.e highly visual UI-s for programming) are definitely a trend that will remain.
#madewithglide
#MobileAppsDevelopment
#Programming
#COVID19
#BuildingAccessRecordsManagement
#ContactTracing
BONUS TOPIC : The Controversy Surrounding Data Privacy For Contact Tracing Apps
I don’t mention it anywhere in this article but obviously data privacy and security is a BIG BIG concern area for these sort of Contact Tracing type apps (I admit, I could have added an entire boilerplate page to the app around data privacy / user data collection etc but like I mentioned it’s just a Proof Of Concept/Demo app :P )
However for those of you who want to learn more about this tricky topic (and it will be important as governments require more and more surveillance as part of the “new normal”), here are few interesting articles to get you started:-
And a few more “local” examples closer to home:-
