Google App Script Tutorial: Example of html form and Google Spreadsheet database

Jordi Rosell

Traditionally, web applications have been implemented in client-server infrastructures. The paradigm has been to have our own servers that receive requests from our users’ browsers. With the increasing use of javascript frameworks, more and more websites are being implemented as simply static websites that require nothing more than serving HTML code, CSS styles and images.

GitHub pages allows web hosting of static websites totally free for public repositories that we own. One of the main drawbacks is not being able to permanently save data in a database or having some interactivity with the server. Ex: send emails.

With this article we are going to see an example of how a static websites could save information in a Google Spreadsheet.

1. Create a Google Spreadsheet and its associated Google Apps Scripts

Create a blank Google Spreadsheet from sheets.new. Give it a descriptive name, for example the name of your website and the type of form you want to implement. Eg: mydomain.com – Contact form

From the Tools menu option, select Script Editor. I recommend that you put the same name to the Google Apps Scripts as to the previous Google Spreadsheet to be able to locate them quickly.

2. Copy the code and customize fields

Then you can copy below code and copy it to your Google Apps Script.


var SHEET_NAME = "Sheet1"; // Enter sheet name where data is to be written
var inputFields = ["nombre", "email", "comentarios"];
var SCRIPT_PROP = PropertiesService.getScriptProperties();
function doRequest(e) {
var lock = LockService.getPublicLock();
lock.waitLock(30000); // wait 30 seconds before conceding defeat.
try {
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
var lastColumn = sheet.getLastColumn();
if(lastColumn==0){
sheet.getRange(1, 1, 1,inputFields.length).setValues([inputFields]);
}
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]; //getRange(row, column, numRows, numColumns)
var nextRow = sheet.getLastRow()+1;
var row = [];
for (i in headers){
if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
row.push(new Date());
} else {
if(headers[i]){
row.push(e.parameter[headers[i]]);
}
}
}
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
return HtmlService.createHtmlOutput(JSON.stringify({"result":"success", "data": e}));
} catch(e){
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally {
lock.releaseLock();
}
}
function doPost(e){return doRequest(e);}
function doGet(e){return doRequest(e);}
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}
function testSheetsWrite() {
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
var lastColumn = sheet.getLastColumn();
if(lastColumn==0){
sheet.getRange(1, 1, 1,inputFields.length).setValues([inputFields]);
}
}

view raw

google-apps-script-contact-form.js

hosted with ❤ by GitHub

Make sure to customize the SHEET_NAME and inputFields variables by the tab name you want to use in your Google Spreadsheet and the form fields you want to receive. In this case we are using the Sheet1 tab and the name, email and comments fields (in spanish).

3. Setup the Google Apps Scripts

Run the setup function. This way we add the Key property on the Google Apps Scripts with the ID of the Google Spreadsheet we use. If you wish, you could add it manually.

4. Publish the web application

From the Publish menu, select the Deploy as web app option. Be sure to select the option “Anyone, even anonymous” in “Who has access to the app:” and post.

Don’t worry if you see the “This app isn’t verified” alert. You have to go to the advanced options and select the “unsafe” option.

Write down the URL since it is in this URL where you will have to send the data of your form.

5. Test the form

On your website you can already use the form. Copy and paste this HTML to your website and modify the URL that was saved on line 22


<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Formulario de contacto</title>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery-serialize-object/2.5.0/jquery.serialize-object.min.js"></script>
</head>
<body>
<form id="contact-form">
<h2>Formulario de contacto</h2>
<p>
<label>Nombre<br><input type="text" name="nombre"></label><br>
<label>Email<br><input type="email" name="email"></label><br>
<label>Comentarios<br></label><textarea name="comentarios" cols=40 rows=5></textarea></label>
</p>
<input id="submit-contact-form" type="submit" value="Submit">
</form>
<script>
(function() {
var $form = $('form#contact-form'), url = 'https://script.google.com/macros/s/key/exec';
$('#submit-contact-form').on('click', function(e) {
e.preventDefault();
var jqxhr = $.ajax({
url: url,
method: "GET",
dataType: "json",
data: $form.serializeObject(),
success: function(data) {
console.log(data);
$('form#contact-form').hide();
$('body').append("<p style='color:green'>Gracias por tu interés. Contactaremos contigo en breve.</p>");
}
});
});
})();
</script>
</body>
</html>

view raw

contact-form.html

hosted with ❤ by GitHub

Try it and customize what you want.

 

 

Leave a Reply