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 - World

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 real-time and let you make proactive decisions
  • And so many…

All the above-mentioned app 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 on HTML, CSS, JavaScript

Basic Knowledge on Google Apps Script

You need to have basic programming knowledge on 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 Sheet 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, this script uses the current time in milliseconds (since 1970) as explains here.

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

Create/ Insert Data

Fill 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 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 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.

Live Demo

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

How to Create this online form using Google Apps Scripts 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, file, and present you the relevant code below.

If you are already familiar with Google Apps Script, you can copy the Google Apps Script file and the Google Sheet to your Google Drive from the following links and start modifying. I have added comments at all the locations that you need to change when you are adding new fields.

Google Sheets1
Link to Google Sheet
(Make a copy to your Drive)

You need to change the Google Sheet ID (Spreadsheet ID) in the Apps Script file as explained below in order to send the data to Google Sheet.

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

You can get a brief idea of the code 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 saving to the “Data” Sheet. The “Helper” sheet includes a list of countries, that are required for populating the “Country” drop-down list.

Google Sheets CRUD App - The data sheet

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 contain following files.

  2. Index.html
  3. JavaScript.html
  4. Form.html
  5. DataTable.html
  6. CSS.htm

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

I have moved the HTML codes for form and the data table into separate files namely Form.html and DataTable.html files to make the code more readable. Those two files are included in the Index.html file using include() function in the file.

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.


The file includes the server side functions.

Inside the function globalVariables(), you can define all the variables used in the script. So, you can call them later inside the other functions when you need them.

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

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

# URL:

function doGet(request) {
  return HtmlService.createTemplateFromFile('Index').evaluate();

function globalVariables(){ 
  var varArray = {
    spreadsheetId   : '1ZibOmtjn8RUlG-ULtj72INWCQx0Xt6ptGVrIAc3OrbM', //** CHANGE !!!
    dataRage        : 'Data!A2:G',                                    //** CHANGE !!!
    idRange         : 'Data!A2:A',                                    //** CHANGE !!!
    lastCol         : 'G',                                            //** CHANGE !!!
    insertRange     : 'Data!A1:G1',                                   //** CHANGE !!!
    sheetID         : '0'                                             //** CHANGE !!! Ref:
  return varArray;

# PROCESSING FORM ---------------------------------------------------------------------------------

function processForm(formObject){  
  if(formObject.RecId && checkID(formObject.RecId)){//Execute if form passes an ID and if is an existing ID
    updateData(getFormValues(formObject),globalVariables().spreadsheetId,getRangeByID(formObject.RecId)); // Update Data
  }else{ //Execute if form does not pass an ID
    appendData(getFormValues(formObject),globalVariables().spreadsheetId,globalVariables().insertRange); //Append Form Data
  return getLastTenRows();//Return last 10 rows

function getFormValues(formObject){
  if(formObject.RecId && checkID(formObject.RecId)){
    var values = [[formObject.RecId.toString(),
    var values = [[new Date().getTime().toString(),//
  return values;

## CURD FUNCTIONS ----------------------------------------------------------------------------------------

function appendData(values, spreadsheetId,range){
  var valueRange = Sheets.newRowData();
  valueRange.values = values;
  var appendRequest = Sheets.newAppendCellsRequest();
  appendRequest.sheetID = spreadsheetId;
  appendRequest.rows = valueRange;
  var results = Sheets.Spreadsheets.Values.append(valueRange, spreadsheetId, range,{valueInputOption: "RAW"});

function readData(spreadsheetId,range){
  var result = Sheets.Spreadsheets.Values.get(spreadsheetId, range);
  return result.values;

function updateData(values,spreadsheetId,range){
  var valueRange = Sheets.newValueRange();
  valueRange.values = values;
  var result = Sheets.Spreadsheets.Values.update(valueRange, spreadsheetId, range, {
  valueInputOption: "RAW"});

function deleteData(ID){ 
  var startIndex = getRowIndexByID(ID);
  var deleteRange = {
                      "sheetId"     : globalVariables().sheetID,
                      "dimension"   : "ROWS",
                      "startIndex"  : startIndex,
                      "endIndex"    : startIndex+1
  var deleteRequest= [{"deleteDimension":{"range":deleteRange}}];
  Sheets.Spreadsheets.batchUpdate({"requests": deleteRequest}, globalVariables().spreadsheetId);
  return getLastTenRows();//Return last 10 rows

## HELPER FUNCTIONS FOR CRUD OPERATIONS --------------------------------------------------------------

function checkID(ID){
  var idList = readData(globalVariables().spreadsheetId,globalVariables().idRange,).reduce(function(a,b){return a.concat(b);});
  return idList.includes(ID);

function getRangeByID(id){
    var idList = readData(globalVariables().spreadsheetId,globalVariables().idRange);
    for(var i=0;i<idList.length;i++){
        return 'Data!A'+(i+2)+':'+globalVariables().lastCol+(i+2);

function getRecordById(id){
  if(id &amp;&amp; checkID(id)){
    var result = readData(globalVariables().spreadsheetId,getRangeByID(id));
    return result;

function getRowIndexByID(id){
    var idList = readData(globalVariables().spreadsheetId,globalVariables().idRange);
    for(var i=0;i<idList.length;i++){
        var rowIndex = parseInt(i+1);
        return rowIndex;

function getLastTenRows(){
  var lastRow = readData(globalVariables().spreadsheetId,globalVariables().dataRage).length+1;
    var range = globalVariables().dataRage;
    var range = 'Data!A'+(lastRow-9)+':'+globalVariables().lastCol;
  var lastTenRows = readData(globalVariables().spreadsheetId,range);
  return lastTenRows;

function getAllData(){
  var data = readData(globalVariables().spreadsheetId,globalVariables().dataRage);
  return data;

## OTHER HELPERS FUNCTIONS ------------------------------------------------------------------------

function getDropdownList(range){
  var list = readData(globalVariables().spreadsheetId,range);
  return list;

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)

2. Index.html

I am using the Bootstrap framework for styling the page. I have included it in the header section of the Index.html using BootstrapCDN. The JavaScript.html, CSS.html, Form.html and DataTable.html files are included using <?!= include('file_name'); ?> code in 8,9,15 and 20 lines of the following code snippet.

<!DOCTYPE html>
        <base target="_top">
        <link href="" rel="stylesheet" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
        <script src="" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
        <script src="" integrity="sha384-xrRywqdh3PHs8keKZN+8zzc5TX0GRTLCcmivcbNJWm2rs5C8PRhcEn3czEjhAO9o" crossorigin="anonymous"></script>
        <?!= include('JavaScript'); ?> <!-- See JavaScript.html file -->
        <?!= include('CSS'); ?> <!-- See CSS.html file -->
    <body onload="createCountryDropdown()">
        <div class="container">
            <div class="row">
                <div class="col-lg-6">
                  <?!= include('Form'); ?> <!-- See Form.html file -->
                  <div id="output"></div>
                <div class="col-lg-6">
                  <?!= include('DataTable'); ?> <!-- See DataTable.html File -->

3. JavaScript.html

  // Prevent forms from submitting.
  function preventFormSubmit() {
    var forms = document.querySelectorAll('form');
    for (var i = 0; i < forms.length; i++) {
      forms[i].addEventListener('submit', function(event) {
  window.addEventListener("load", functionInit, true); 
  function functionInit(){  
  function handleFormSubmit(formObject) {;
  function getLastTenRows (){;
  function getAllData(){
    //document.getElementById('dataTable').innerHTML = "";;
  function createTable(dataArray) {
      var result = "<table class='table table-sm' style='font-size:0.8em'>"+
                   "<thead style='white-space: nowrap'>"+
                     "<tr>"+                               //Change table headings to match witht he Google Sheet
                      "<th scope='col'>Delete</th>"+
                      "<th scope='col'>Edit</th>"+
                      "<th scope='col'>ID</th>"+
                      "<th scope='col'>Name</th>"+
                      "<th scope='col'>Gender</th>"+
                      "<th scope='col'>Date of Birth</th>"+
                      "<th scope='col'>Email</th>"+
                      "<th scope='col'>Phone</th>"+
                      "<th scope='col'>country</th>"+
      for(var i=0; i<dataArray.length; i++) {
          result += "<tr>";
          result += "<td><button type='button' class='btn btn-danger btn-xs deleteBtn' onclick='deleteData(this);'>Delete</button></td>";
          result += "<td><button type='button' class='btn btn-warning btn-xs editBtn' onclick='editData(this);'>Edit</button></td>";
          for(var j=0; j<dataArray[i].length; j++){
              result += "<td>"+dataArray[i][j]+"</td>";
          result += "</tr>";
      result += "</table>";
      var div = document.getElementById('dataTable');
      div.innerHTML = result;
      document.getElementById("message").innerHTML = "";
      var div = document.getElementById('dataTable');
      div.innerHTML = "Data not found!";

  function deleteData(el) {
    var result = confirm("Want to delete?");
    if (result) {
      var recordId = el.parentNode.parentNode.cells[2].innerHTML;;
  function editData(el){
    var recordId = el.parentNode.parentNode.cells[2].innerHTML; //;

  function populateForm(records){
    document.getElementById('RecId').value = records[0][0];
    document.getElementById('name').value = records[0][1];
    document.getElementById(records[0][2]).checked = true;
    document.getElementById('dateOfBirth').value = records[0][3];
    document.getElementById('email').value = records[0][4];
    document.getElementById('phone').value = records[0][5];
    document.getElementById("country").value = records[0][6];
    document.getElementById("message").innerHTML = "<div class='alert alert-warning' role='alert'>Update Record [ID: "+records[0][0]+"]</div>";
  function createCountryDropdown() {
  function countryDropDown(values) { //Ref:
    var list = document.getElementById('country');   
    for (var i = 0; i < values.length; i++) {
      var option = document.createElement("option");
      option.value = values[i];
      option.text = values[i];

4. Form.html

<!-- ## The html code of the form goes here -->
<!-- ## This is included in the Index.html page using "include('Form')" function -->

<form id="myForm" class="p-2 border border-light rounded bg-light" onsubmit="handleFormSubmit(this)"> <!-- Call JavaScript function "handleFormSubmit" -->
    <p class="h4 mb-4 text-center">Contact Details Form</p>
    <div id="message"></div>
    <input type="text" id="RecId" name="RecId" value="" style="display: none">
	<div class="form-group">
		<label for="name" >Name</label>
		<input type="text" class="form-control" id="name" name="name" placeholder="Name" required>
	<div class="form-row">
		<div class="form-group col-md-6">
			<div class="form-check form-check-inline">
				<input class="form-check-input" type="radio" name="gender" id="male" value="male">
				<label class="form-check-label" for="male">Male</label>
			<div class="form-check form-check-inline">
				<input class="form-check-input" type="radio" name="gender" id="female" value="female">
				<label class="form-check-label" for="female">Female</label>
		<div class="form-group col-md-6">
			<label for="dateOfBirth">Date of Birth</label>
			<input type="date" class="form-control" id="dateOfBirth" name="dateOfBirth">
	<div class="form-group">
		<label for="email">Email</label>
		<input type="email" class="form-control" id="email" name="email" placeholder="Email">
    <div class="form-row">
      <div class="form-group col-md-6">
          <label for="phone">Phone Number</label>
          <input type="tel" class="form-control" id="phone" name="phone" placeholder="Phone Number">
      <div class="form-group col-md-6">
        <label for="exampleFormControlSelect1">Country</label>
        <select class="form-control" id="country" name="country">
          <option>Select Country</option>
	<button type="submit" class="btn btn-primary">Submit</button>
    <input class="btn btn-secondary" type="reset" value="Reset">

5. DataTable.html

The data table is inserted by JavaScript function inside id dataTable.

<p class="h4 mb-4 text-center">Contact Details Database</p>

<div id="dataTable" class="table-responsive">
  <!-- The Data Table is inserted here by JavaScript -->
<button type="button" class="btn btn-success btn-sm" onclick="getAllData()">Get ALL Data</button>

6. CSS.html

The following CSS class change the button size. You can add your custom CSS inside the following file.

.btn-group-xs > .btn, .btn-xs {
  padding: .25rem .4rem;
  font-size: .875rem;
  line-height: .5;
  border-radius: .2rem;

Step 03: Change the SpreadsheetID in file

Copy the spreadsheetID of the google sheet, you copied to your drive in Step 01. Replace the spreadsheetId in line 15 of 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 you Apps Script,

  1. Open the Script Editor (the file you copied to Google Drive in Step 02)
  2. Go to Resources > Advanced Google services….
  3. In the Advanced Google Service dialog box, go down and enable Google Sheets API
  4. Click Ok

Step 05: Deploy as a Web App

To deploy this script as a web app.

  1. In the Script Editor, Go to Publish > Deploy as web app
  2. In the Deploy as web app dialog, select Project Version as New.
  3. Under Execute the app as: select your email address.
  4. under Who has access to the app, select your preference.
  5. Click Deploy.
  6. In the next dialog box, copy the Current web app URL
  7. Paste it in your browser and your online form will be loaded.

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 file and passes the form object as the parameter.
  3. The function processForm() check whether form object has an ID (RecID) and if exist validate using checkID() function.
  4. If the form object does not have a valid recId, the function appendData() is called.
  5. The function getFormValues() (the first parameter of the appendData() function) process the form object and returns a value array. It also creates a new ID if recId not exists.
  6. The function appendData() is executed and append the form data ta to the given range.
  7. Then the function processForm() call the function getLastTenRows() and it returns the last 10 rows of your data range. The function processForm() also return the same to the handleFormSubmit() mentioned in no 1 above.
  8. Then the JavaScript function handleFormSubmit() call the function createTable() function with the last 10 rows as a parameter.
  9. 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 editData() JavaScript function which you can see in the JavaScript file.
  3. Then, the function editData() call the serverside function getRecordById() (it is in the file) with the recId as the parameter.
  4. The getRecordById() returns the relevant row of data as an array to the editData() function.
  5. Then, the editData() 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 updateData() 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 request to confirm the action.
  3. It calls the JavaScript function deleteData() which you can see in the JavaScript file.
  4. Then, the function deleteData() calls the serverside function deleteData() (the second is in the file) with the recId as the parameter.
  5. The serverside function deleteData() 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 deleteData() (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.

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 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 required to build most of the forms used in your day to day works. So, you can expand this form to meet your requirements even if you do not have much knowledge of coding.

4.7 3 votes
Article Rating
Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Inline Feedbacks
View all comments

very, very nice!


Wow ! Thank You !
Is there a way to open the form in a popup on the sheet instead of in a new tab ?

Vuong Luu (@kingreyvn)

Can you make the Form as a Pop Out box ?
Also when I click an item in the table , it took about 2 seconds to load, maybe a Loading spinner is needed


For the popup window, try using Bootstrap’s modal component.

When you click the edit button, it calls the serverside function and reads the data in the Google Sheet. However, you can speed up the process by populating the form using the data already loaded in the table. You need some javascript functions to read data in the HTML table.


Plz suggest code for second drop down or dependent drop down.

Sandeep Bondre

I am find your work usefull , though trying to understand the coding at the moment ( VB Coder ) , in the CRUD example you as showing last 10 records , or Get all records what i would like to do is to show only those records where a column name which has the userid and another column which has an amount so basically the condition to populate the rows is where userid = 1001 and amount > 100 or may trn_date > dt1 and trn_date<= dt2

Frédéric Beaupré

Is it possible to specify how to add a second dropdown?
Thank you

jewel rana

yes if you add Dynamic Dependent Dropdown with your form its will be good for us tnx

Abaid Ahmed

Plz suggest code for second dropdown or dependent drop down. It will be AMAZING.

PirateSteve Brown

WOW!! THANK YOU!! I have searched for Google Sheets CRUD example and could not find one that worked. This is great! Thank you!!


I am trying to integrate a function that checks when inserting a new record to see if it is already present in the google sheet.

It would be optimal to make a check based on the emails on the sheet. You could help me out. The work is perfect.

PirateSteve Brown

Thanks again. Have been able to modify this for my data & sheet layout and it works wonderfully.
But…have not been able to figure out how to do a file (image) upload on the form. The other changes were reasonably straightforward just adapting what was there, but no matter how I try a form element with type=”file” it not only does not load the file, it ceases to load any of the other form data. I can change that one type back to “text” and it works. ???

Any help GREATLY appreciated. PS noob here.


Some times Edit option is not working.What could be wrong?


It is great. I have two questions, please help.
1/. How can we insert records from HTML form to the second row in google sheet (except header row)?
2/. How to create pagination for this case?
Many thanks.


Very good. But the whole datatable just disapeared, why is that?


How do you make data in the database clickable?

A Mendz

Thanks for this example. Quick question, after submitting data the browser page goes blank and don’t show the web form nor the contact detail database. Refreshing the browser doesn’t bring back the web form. I have to deploy the app to see it again. Any thoughts?

Murad Rahman Saud

How to add search box for the data, Please help. I will be greadfull.

Murad Rahman Saud

Thank you for everything This will benefit me more if there are search options

Suryo Bintang



its helped me a lot thank you so much , can you upload user authenticate base crud taking notes app using with GAS


Hi! Thank you so much for the script!

How can I combine the doGet(request) function with a doGet(e) to pass url parameters as a globalVariable. I tried using doGet(request) with a var = request.parameter.v, but is not working. Please help!

Hey, wait!

Before you go, Subscribe to Blog via Email