Google Apps Script Attendance Log Application

Here's another quick project that utilizes simple scripts. We are going to create a simple attendance logger application written in Google Apps Script. It's a cool project you can replicate instead of just using a conventional Google Form. Although, in this demo, you could say a Google Form is the better approach. But, just want you to know the basic steps in creating a function web application that uses Google Sheet as a database.

I've posted an article before, with basically, same approach. That was more of pulling from a database. Now we are going to do writing to a database. This is going to be interesting, since after this you both have the read and write flow with Google Sheet as a database. Also, if you need to learn Google Apps Script further, click Buy Now button below for the book. Available in Amazon.

Now, into the script First, let's picture out the overall process. It has two components;

1. The form - this is a web application. The HTML code and the apps script that will handle the interface between HTML and Google Sheets. With the interface, I'm introducing an alert if the log function was successful or not. You will see below script a google.script.run function for handling this interface. Plain HTML javascript cannot communicate with Google Apps Script. In other words we cannot log to Google sheets directly. Unless we are using an API. That is of another discussion. You will see also, that I've separated the javascript component. I'm introducing it this so you could see before hand the advantage in doing it this way. Instead of writing it together with the HTML file. The good thing about this approach, is that, you can modularize your code. Very helpful when your code grows. It's not so good with comes to debugging if it's integrated directly with the HTML component.
2. The Database - this is the Google Sheet where we log the attendance. No need to discuss further on this aspect. But, point to note though, when you pass a date time format from javascript to app script needs attention. You need some data type conversion before you write them into the database.

The Script:

Google Apps Script Web App Sheets as Database

Building your own web app or website takes amount of your money. Although there some low cost web hosting platform out there, it isn't cheap either. You can build the page with default templates. The next thing you need to consider is the database.

Database don't come cheap either and it will cost your per usage. Or, you could choose from various package. But, then again, you need to anticipate your space usage over time.

Google Sheets as database

Sheets already has the basic feature of a database. Which is,structured tables and can be accessed through script. We are going to use Google Apps Script for this and let me show you an overall process on how will this work.

The 3 components

1. Login page - this is a web page. For demo we are using this as to where user will provide login credentials. 3 parts for this. When you create a new Apps Script, you will need to have Code.gs for Google Apps Scripts, HTML for the login page and SCRIPT file for the javascript. I'm showing below a working example.
   
   
   Login Page Scripts:
2. User Credential Database - this is the repository of the user credentials. It could also be some other database needed to validate user id/passwords or user sessions. Here's how the spreadsheet was structured for application. You can use the credentials shown to preview the demo web application. Click LINK for the login page
   
3. Web app - this could be the main web page and where the services are in a successful login
   
   
   The Script:

This is a very quick setup or scripting session for this demo. This is enough to develop your application with same approach. This is foundational you may tweak a little to some degree. To learn more about Google Apps Script, click Buy Now button below. It's a good reference for your study.