Creating a CRUD Web Application for Dissertation Catalog

In this post, I show how to create a CRUD application using Google Web App and Google Sheets for our (library) purpose. I was thinking of having an online catalog for the dissertations submitted by UG/PG students of my college. My conditions were the system should be available 24/7 with a search option. This application was actually created from that query. Let us see how we can create a 100% free web application with CRUD (Create, Read, Update and Delete) operation using Google tools only

We will be creating the following

An online form, for entering metadata
A Google Spreadsheet that works as the database in the backend
Google Awesome table that functions as an interface (catalog)

Why should we create an online Data Entry Form?

You can improve the productivity of your work by creating an HTML form to submit data to Google Sheets than directly typing data on the Sheet.

Easy to share the form and ask all your staff or team members to submit data to the same Sheet at the same time.
You need not give Google Sheet edit access to your team, so your data is secure
Reduce human errors
Increase the reliability of your data -form validation
Fast data entry (if you are doing mass data entry work) – Save both time and money
Monitor data entry work in real-time and let you make proactive decisions
And so many…

What you need

A Google Account
Basic knowledge of HTML, CSS, JavaScript
Basic Knowledge of Google Apps Script

You need to have basic programming knowledge of HTML, CSS, and JavaScript to modify the code in this post. However, I will try my best to make the code readable and editable by non-programmers too.

The Concept

To perform CRUD operations, you need to create a unique ID for your records. For this purpose, this script uses the current time in milliseconds (since 1970) as explained here.

I briefly explain the logic behind the CRUD operations used in this method below.

Create/ Insert Data

Fill out the form and hit the submit button. If it passes the browser validation data is sent to the server-side script. If it is not an update request (does not contain an ID) server-side script adds a new id (it is the timestamp) and appends data to the specified range.

Read Data

The server-side script requests data for a given range using APIs. after you perform, create, update, or delete an action the web app updates the data table in the frontend with changed data.

Update Data

When you click the update button, it passes the record ID to the server-side script. The server-side script validates the ID, and if it exists, it reads the relevant row. The data passes to the form for editing.

Once you hit the submit button, it passes data to the server-side script. It validates the ID and if it exists, updates the replace the relevant row with new data.

Delete Data

When you click the delete button, it passes the record ID to the server-side script. The server-side script validates the ID. If the ID exists, delete the relevant row and shift the cells up.

Step 01: Make a Copy of the Google Sheets

You can make a copy of the Google Sheets used in this example from the following link.

Link to Google Sheet – Make a copy to your Google Drive

This spreadsheet contains one sheet namely “Data”. The data you inserted from the web form is saved to this “Data” Sheet.

Step 02: See Apps Script File (optional)

You can also see the App Script appended with the google sheet by clicking tab Extensions -- App Script unless you want to modify the Web App

The Apps Script file contains the following files.

Code.gs
Index.html

The Code.gs file contains the server-side scripts, which include the function that calls Google Sheets API. and the other files make up the online form.

Step 03: Deploy the Application

Once you are ready, click on the tab Extensions -- App Script you will be redirected to the App Script page from where you have to deploy your application

In the end, you will get a link to your Web App interface (online form), from there you can start your data entry, and the data will be added to your google sheet (database)

Step 03: Append the Google Sheet with Google Awesome Table

The awesome table is used for the catalog interface

Go to the Google Awesome Table do signing with your google account select your Google sheet and the awesome table will display your data with the searching and filtration option.

See the screenshot of the interfaces