Home Advanced Tutorial How to record data from a form to Google Sheet

How to record data from a form to Google Sheet

by vagrant

In some cases, Google Forms come across as same. You are looking forward to having your own form with the extra fields and layouts and record automatically the data to Google Sheets. Following this article, you guys will learn how to make a form then send the responses to Google Sheets.

Firstly, you need make form with basic fields.

<div class="cc-content">
			<div class="title">Register Form</div>

			<form class="submit-form">
				<div class="s-control">
					<label>Username</label>
					<input class="name" name="name" placeholder="Username" type="text">
				</div>
				<div class="s-control">
					<label>Email </label>
					<input class="mail" name="mail" placeholder="Email" type="text">
				</div>
				<div class="s-control">
					<label>Phone</label>
					<input class="tphone" name="phone" placeholder="Phone" type="text">
				</div>
				<div class="s-control">
					<label>Address</label>
					<input class="address" name="address" placeholder="Address" type="text">
				</div>
				<div class="s-control section-btn"><button type="button">Submit</button></div>
			</form>
		</div>

We add some CSS to make a nice form

.cc-content {
				margin: 50px auto;
				width: 450px;
				font-family: 'Montserrat', sans-serif;
			}
			.title {
				color: #08c;
				font-size: 38px;
				display: block;
				margin-bottom: 5px;
				text-align: center;
			}

			.s-control {
				display: block;
				width: 100%;
				margin-bottom: 15px;
			}

			.s-control label {
				width: 100%;
				margin-bottom: 8px;
				display: block;
				color: #08c;
				font-size: 16px;
				text-align: left;
			}

			.s-control input {
				background: #e6e6e6;
				border-radius: 6px;
				padding: 0 17px;
				border: none;
				width: 100%;
				font-size: 14px;
				height: 40px;
				line-height: 40px;
			}
			.s-control textarea {
				background: #e6e6e6;
				border-radius: 6px;
				padding: 10px 17px;
				border: none;
				width: 100%;
				font-size: 14px;
				height: 80px;
			}

			.s-control button {
				background: #00b289;
				border-radius: 6px;
				padding: 14px 50px;
				border: none;
				color: #fff;
				cursor: pointer;
				font-size: 18px;
				font-weight: 600;
			}

			.s-control.section-btn {
				text-align: center;
				margin-top: 30px;
				margin-bottom: 0;
			}

			.cc-loading {
				position: fixed;
				width: 100%;
				height: 100%;
				top: 0;
				left: 0;
				background: #ddd;
				z-index: 992;
				opacity: 0.5;
				display: none;
			}

			.cc-loading.loading {
				display: block;
			}

			.cc-loading span {
				background: url('https://uphinh.org/images/2020/02/19/ajax_loading.gif') no-repeat;
				width: 70px;
				height: 70px;
				display: block;
				left: 50%;
				top: calc(50% - 35px);
				position: fixed;
				font-size: 0;
			}

			.cc-content p {
				color: #08c;
				font-size: 30px;
				margin-top: 40px;
				text-align: center;
			}

We will use ajax to submit form and validate some fields as email, phone

(function ($, window, document) {
			var $form = $('.submit-form'),
				url = 'https://script.google.com/macros/s/AKfycbxD1yYh-RBtho0HOud5iEeWMSrP469teZmP5nu2hq3FDWKEXRU/exec';

			var pattern_phone = /^\b[0-9]*\b$/i
			var pattern_mail = /^\b[A-Z0-9._%-][email protected][A-Z0-9.-]+\.[A-Z]{2,4}\b$/i

			$('.section-btn button').on('click', function (e) {
				e.preventDefault();

				if ($('.name').val() === '' || $('.mail').val() === '' || $('.phone').val() === '' || $('.address').val() === '') {
					$('.no-val').remove();
					$('.submit-form').append('<p class="no-val">Field is not empty</p>');
				}  else if (!pattern_phone.test($('.tphone').val())) {
					$('.no-val').remove();
					$('.submit-form').append('<p class="no-val">Wrong Phone format</p>');
				} else if (!pattern_mail.test($('.mail').val())) {
					$('.no-val').remove();
					$('.submit-form').append('<p class="no-val">Wrong Email Format</p>');
				} else {
					$('.no-val').remove();

					$.ajax({
						url: url,
						method: "GET",
						dataType: "json",
						data: $form.serialize(),
						beforeSend: function() {
							$('.cc-loading').addClass('loading');
						},
						success: function(response) {
							$('.cc-loading').removeClass('loading');
							$('.submit-form').remove();
							$('.cc-content').append('<p>Submit Form Finish.</p>');
						}
					});
				}
			})
		})(jQuery, window, document);

You can see, we have url submit is

https://script.google.com/macros/s/AKfycbxD1yYh-RBtho0HOud5iEeWMSrP469teZmP5nu2hq3FDWKEXRU/exec

This is url app from google sheet, we will say in next step

Now, we need create new google sheet

Please add name from form to 1st row in file.

After created done, please go Tools -> Script Editor to open featured script for google sheet

Please add this code to this script

function doGet(e){
  return handleResponse(e);
}

var SCRIPT_PROP = PropertiesService.getScriptProperties(); 

function handleResponse(e) {
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);
  
  try {
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var SHEET_NAME = "Test";
    var sheet = doc.getSheetByName(SHEET_NAME);
    
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; 
    var row = []; 
    
    for (i in headers){
      if (headers[i] == "Timestamp"){
        row.push(new Date());
      } else { 
        row.push(e.parameter[headers[i]]);
      }
    }

    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
          .setMimeType(ContentService.MimeType.JSON);
  } finally {
    lock.releaseLock();
  }
}

function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

After adding code, you need go Run -> Run function -> setup to run script

The last step, please go Public -> Deploy as a webapp to public your script

Please copy url to url in form submit, we say in step create form.

When you want edit script, please remember alway chosen project version is new.

You can try demo link in :

https://chuyencode.com/tools/google-sheet/

Google sheet to test is : https://docs.google.com/spreadsheets/d/1igl0-jd4FkuCw-5EGyylteKVp4j6TQP39ZSiPXPiRNc/edit#gid=0

You can download full code in here

We are Chuyencode Team and work as the experts in Website development. In case, if you have any question, just leave a comment below or submit a ticket, we are pleased to support you.

Leave a Comment