How to Create an Online Data Entry Form that can Perform CRUD Operations on Google Sheets

Online data entry forms are a powerful tool that you can use to collect many types of data more easily and accurately. Google Sheets is the most powerful cloud-based spreadsheet application that you can use absolutely free. Google is also offering another free tool named Google Apps Script (GAS) to increase the power of your favorite Google apps. In this post, I will explain to you how you can create a free online form (or Web App) that can perform Create, Read, Update, and Delete (CRUD) Operations on Google Sheets using Google Apps Script and Google Sheets API v4.

Why I Should Create Online Data Entry Forms

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 and 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…

All the above-mentioned apps and services are packed with your Google Account completely free. So, you can build an absolutely free data entry form or any type of online form to perform CRUD Operations on Google Sheets. So, let’s get started.

In a previous post also, I explained, “How to Create Data Entry Form with Google HTML Service and Submit Data to Google Sheets“. However, using that code you can only write data to Google Sheets. If you only need to submit data to Google Sheets, you may use that method because that code is less complex than this.

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.

Overview of creating an Online Data Entry Form that can Perform CRUD Operations on Google Sheets

In this tutorial, we are going to create a web form that can Create, Read, Update, and Delete (CRUD) records in Google Sheets.

For creating this online form I am using Google Apps Scripts. There are two ways that you can use Google Apps Scripts namely Container-bound Scripts and Standalone Scripts. Here I am using the Standalone Scripts. So you have to create Google Sheets and the App Script files separately in your Google Drive.

We are going to perform CRUD operations on Google Sheets using Google Sheets API v4. (in the previous post that I mentioned above create the HTML form without using APIs)

The Concept

To perform CRUD operations, you need to create a unique ID for your records. For this purpose, I will use a universally unique identifier (UUID). However, this identifier is not guaranteed to be unique across all time and space. Therefore, you may need to use another method, if you need this for high-frequency data entry work.

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

Create/ Insert Data

The user fills 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 (UUID) and append data to the specified range.

Read Data

The server-side script request data for a given range using APIs. after you perform, create, update, or delete actions, The web app updates the data table in the front 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, replaces 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.

Live Demo

Use the following links to access the live form and the Data Sheet.

How to Create this online form using Google Apps Script and perform CRUD operations on Google Sheets

Here I am not going to explain the code line by line. Instead, I will briefly explain the purpose of each function, and file, and present you with the relevant code below.

The following video demonstrates how to copy these files to your Google Drive and run the code. In this video, from 4:00 onwards, I explain how to add a new field to the form.

If you cannot see the video, please follow this link to watch it on YouTube.

You can get a brief idea of the code by following the below steps. I assume that you have already logged into your Google Account.

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 two sheets namely “Data” and “Helpers”. The data you inserted from the web form is saved to the “Data” Sheet. The “Helper” sheet includes a list of countries required for populating the “Country of Origin” drop-down list.

Step 02: Make a Copy of the Google Apps Script File

You can also make a copy of the App Script used in this example file from the below link.

Link to Google Apps Script File – Make a copy to your Google Drive

The Apps Script file contains the following files.

1. Code.gs
2. Index.html
3. JavaScript.html
4. FormProductDetails.html
5. DataTable.html
6. CSS.html
7. SpinnerModal.html

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

I have moved the HTML codes related to our main components to separate files namely, FormProductDetails.html, DataTable.html, and SpinnerModal.html to make the code more readable. These files are included in the Index.html file using the server-side function include().

The JavaScript and CSS are written in the JavaScript.html and CSS.html files respectively. Those two files are also included in the Index.html file using the same include() function mentioned above.

See the Google HTML Service guides on Separating HTML, CSS, and JavaScript

Code Snippets

The following are the codes included in the Google Apps Script file mentioned above.

1. Code.gs

The Code.gs file includes the server-side functions.

I have declared several variables in the global scope so that we can use them inside other functions without declaring them again and again.

Most of the functions are self-explanatory, and I have added comments to make them easy to understand. The sources of some code sections are also added in the comments.

I will explain how this code works later in this post.

2. Index.html

This file contains the HTML, CSS, and JavaScript code that defines the user interface and functionality of the web app.

The main HTML component such as Form, Data Table, and Spinner are moved to separate HTML pages to improve the readability of the code. And also, the CSS and JavaScript codes also moved to separate HTML pages with the same purpose. The external CSS and JavaScript libraries such as Bootstrap and JQuery are also included in these separate CSS and JavaScript files.

Then, all those separate HTML pages are included in the Index.html file using the include() function, using <?!= include('file_name'); ?> code.

3. JavaScript.html

This file includes the JavaScript codes which run on the visitor’s browser. It also includes relevant external JavaScript libraries.

4. FormProductDetails.html

This file includes the HTML codes which create the HTML data entry form.

5. DataTable.html

This file includes the HTML code required to generate the data table. The data table is inserted by a JavaScript function inside the id dataTable.

6. CSS.html

This file contains a CSS class that changes the button size. You can add your custom CSS inside the following file.

7. SpinnerModal.html

This file contains the HTML codes required for the SpinnerModal. It is an animated modal window that appears on top of content and is used to indicate that the content is being loaded or processed.

Step 03: Change the SpreadsheetID in Code.gs file

Copy the spreadsheetID of the Google Sheet, you copied to your drive in Step 01. Replace the SPREADSHEETID in line 8 of the Code.gs file.

Learn how to find SPREADSHEETID.

Step 04: Enable Google Sheets API

You must enable Google Sheets API in Advance Google Services to run this script. To enable Google Sheets API for your Apps Script,

1. Open the Script Editor (the file you copied to Google Drive in Step 02).
2. Click on the Plus (+) icon in front of the Services tab.
3. Then select Google Sheets API and click Add button.

Step 05: Deploy as a Web App

To deploy this script as a web app.

1. Click the Deploy button at the top Right
2. Then, chose the New deployment.
3. In the New deployment window, click the gear icon and select Web app.
4. Then type a description for this deployment in the Description text box (Optional).
5. Under the Web App, Execute as option select, Me(Your Email)
6. Select Anyone, for the Who has access option to enable everyone who has access to use the web app. You may change this according to your requirement.
7. Then click Deploy.
8. If you run this for the first time, it will require your authorization to run the code in your Google Account. If so, click Authorize access button.
9. In the next window, click on your email address.
10. Then click the Advanced link.
11. Click the link with the name of your Google Apps Script code.
12. Click Allow.
13. Then Click on the web app URL to load it.

Read more about Deploying a script as a web app in Google Guide.

How this Script perform CRUD Operations on Google Sheets

Create New Record

1. After you correctly fill the form and hit the submit button, the function handleFormSubmit() in the JavaScript file is called.
2. This function calls the function processForm() in the Code.gs file and passes the formObject as the parameter.
3. The function processForm() check whether the formObject has an ID (recId) and if exist validate using checkID() function.
4. If the formObject has recId, it means it is an update request. So, it gets the range relevant to the ID using getRangeById() function. Then it calls the updateRecord() function with the data in formObject and the range found earlier.
5. If the formObject does not have a valid recId, it means it is a request to create a new record. Then it calls the generateUniqueId() function and get a new unique ID. Then it calls the createRecord() functions with the values extracted from the formObject and the unique ID.
6. Then the function processForm() call the function getLastTenRecords() and it returns the last 10 rows of your data range. The function processForm() also returns the same to the handleFormSubmit() mentioned in no 1 above.
7. Then the JavaScript function handleFormSubmit() call the function createTable() function with the last 10 rows as a parameter.
8. Then the function createTable() update the data table next to the Form.

Update Record

1. The user clicks the update button in the row which data need to be updated.
2. It calls the editRecord() JavaScript function which you can see in the JavaScript file.
3. Then, the function editRecord() calls the serverside function getRecordById() (it is in the Code.gs file) with the recId as the parameter.
4. The getRecordById() returns the relevant row of data as an array to the editRecord() JavaScript function.
5. Then, the editRecord() function calls the JavaScript function populateForm() with the data array (it is in the JavaScript file).
6. The function populateForm() populates the form fields with the data.
7. Then the user can make the changes and hit the submit button.
8. Hereafter, almost the same process in the above “Create New Record” is followed. Since here form passes a recId, in step 4 it calls the updateRecord() function. It replaces the row which matches the recId.

Delete Data

1. The user clicks the Delete button in the row which data need to be deleted.
2. Browser requests to confirm the action.
3. It calls the JavaScript function deleteRecord() which you can see in the JavaScript file.
4. Then, the function deleteRecord() calls the serverside function deleteRecord() (the second is in the Code.gs file) with the recId as the parameter.
5. The serverside function deleteRecord() validate and delete the row that matches the recId. After deleting the record function returns the last 10 records of the data range.
6. Then the JavaScript function deleteRecord() (the function in the JavaScript file) calls the function createTable() with the last 10 rows as a parameter.
7. Then the function createTable() updates the data table next to the Form.

Form Validation

Form validation is very important when submitting data to Google Sheets. For form validation, I have used browser defaults. However, depending on the requirements you may need more customization. You can learn more about Bootstrap form validation from this link.

We recently updated this post. If you have been working with our previous post you can access the archived version from this link.

What Next

This form covers most of the common form elements that you need to build a data entry form or any type of online form to perform CRUD operations on Google Sheets. So, you can customize this form to match with your requirements using those elements. You can learn more about this using the links provided in this article as well as from the URLs commented in the code.

When you have data on your hand, you must use data visualization techniques to spot the signals in your data. If you can build an online dashboard for your data, you can make decisions even while your data collection project is ongoing. You can learn more about building a free online dashboard from my previous post, “How to Create an Online Dashboard for free to Share and Visualize Your Data“.

Wrapping Up

Google Apps Scripts has been introduced to increase the power of your favorite Google apps. So, by using Google Apps Script with Google Apps you can build various types of custom solutions to boost your collaboration and productivity.

In this example, I explained to you how to build an Online Data Entry Form that can Perform CRUD Operations on Google Sheets. Where I used Google Sheets APIs with Google Apps Script HTML service. I have added most of the form elements that are required to build most of the forms used in your day-to-day work. So, you can expand this form to meet your requirements even if you do not have much knowledge of coding.