Google Apps Script and Custom Form For Google Sheet

This tutorial is based on http://railsrescue.com/blog/2015-05-28-step-by-step-setup-to-send-form-data-to-google-sheets/

Read https://github.com/AishwaryT/Google-app-script-crud as well.

1) Create a new Google Sheet

Create columns as shown below:

Timestamp

name

phone

position

Select Menu/Script editor...

A Script Editor window will be opened in a new browser tab. By default a code file Code.gs is created.

Paste the following code.

// 1. Enter sheet name where data is to be written below

var SHEET_NAME = "Sheet1";

// 2. Run > setup

// 3. Publish > Deploy as web app

// - enter Project Version name and click 'Save New Version'

// - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)

// 4. Copy the 'Current web app URL' and post this in your form/script action

// 5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

// If you don't want to expose either GET or POST methods you can comment out the appropriate function

function doGet(e){

return handleResponse(e);

}

function doPost(e){

return handleResponse(e);

}

function handleResponse(e) {

// shortly after my original solution Google announced the LockService[1]

// this prevents concurrent access overwritting data

// [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html

// we want a public lock, one that locks for all invocations

var lock = LockService.getPublicLock();

lock.waitLock(30000); // wait 30 seconds before conceding defeat.

try {

// next set where we write the data - you could write to multiple/alternate destinations

var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));

var sheet = doc.getSheetByName(SHEET_NAME);

// we'll assume header is in row 1 but you can override with header_row in GET/POST data

var headRow = e.parameter.header_row || 1;

var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

var nextRow = sheet.getLastRow()+1; // get next row

var row = [];

// loop through the header columns

for (i in headers){

if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column

row.push(new Date());

} else { // else use header name to get data

row.push(e.parameter[headers[i]]);

}

// more efficient to set values as [][] array than individually

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 { //release lock

lock.releaseLock();

}

function setup() {

var doc = SpreadsheetApp.getActiveSpreadsheet();

SCRIPT_PROP.setProperty("key", doc.getId());

}

Save.

2) Run Setup

Before deploying the script, we need to run the setup function to register the worksheet id.

To check that the setup process is successful, select menu File/Project Properties.

The Script Properties tab should display the key value.

Then, deploy as web app.

Execute the apps as: me.

Who has the access to apps: anyone, even anonymous.

Take note of the web app URL.

Paste the web app URL to the string <YOUR WEB APP URL> in the JS codes below.

3) Create a new CodePen

HTML

<head>

</head>

<body>

<div class="waves-light" style="height:50px;background-image: url(https://lh5.googleusercontent.com/NplHsFMjyO6ML5EvR9xHtFi-nxa61W58LE1uaPB_c_VnTtnspJnCOzr9uuHcbTdn_ZNGWW8dJA=w400);

/* background-size: cover; */

/* background-position: center; */

color: rgba(255, 255, 255, 1);"></div>

<h3 style="text-align:center;">Registration</h3>

<i class="material-icons prefix">account_circle</i>

</div>

<i class="material-icons prefix">picture_in_picture</i>

<label for="pidno">Personal ID</label>

</div>

<i class="material-icons prefix">email</i>

<label for="email">Email</label>

</div>

<i class="material-icons prefix">phone</i>

<label for="phone">Phone</label>

</div>

<option value="" disabled selected>Position</option>

<option value="Assistant">Assistant</option>

<option value="Secretary">Secretary</option>

<option value="Others">Others</option>

</select>

</div>

<!-- Future extension

<span>Reference Document</span>

</div>

</div>

-->

<br/>

<button class="btn btn-large waves-effect pink accent-3" type="reset" >Reset

<i class="material-icons right">settings_backup_restore</i>

</button>

</div>

<button class="btn btn-large waves-effect waves-light" id ="send" type="submit" name="send">Send

</button>

</div>

</form>

</div>

$(document).ready(function() {

$('select').material_select();

});

</script>

</body>

// Variable to hold request

var request;

// Bind to the submit event of our form

$("#register").submit(function(event){

// Abort any pending request

if (request) {

request.abort();

}

// setup some local variables

var $form = $(this);

// Let's select and cache all the fields

var $inputs = $form.find("input, select, button, textarea");

// Serialize the data in the form

var serializedData = $form.serialize();

// Let's disable the inputs for the duration of the Ajax request.

// Note: we disable elements AFTER the form data has been serialized.

// Disabled form elements will not be serialized.

$inputs.prop("disabled", true);

// Fire off the request to /form.php

request = $.ajax({

url: "<YOUR WEB APP URL>",

type: "post",

data: serializedData

});

// Callback handler that will be called on success

request.done(function (response, textStatus, jqXHR){

// Log a message to the console

console.log("Hooray, it worked!");

console.log(response);

console.log(textStatus);

console.log(jqXHR);

$("#register")[0].reset();

});

// Callback handler that will be called on failure

request.fail(function (jqXHR, textStatus, errorThrown){

// Log the error to the console

console.error(

"The following error occurred: "+

textStatus, errorThrown

);

});

// Callback handler that will be called regardless

// if the request failed or succeeded

request.always(function () {

// Reenable the inputs

$inputs.prop("disabled", false);

});

// Prevent default posting of form

event.preventDefault();

});

Test Run.

The console window should display success message.

The Google Sheet should contain the new record.

HTML5 Form With App Script Google Sheet: http://codepen.io/notarazi/pen/WppKGe

See the Pen HTML5 Form App Script Google Sheet by notarazi (@notarazi) on CodePen.

Basic Steps

Labels

Learn the powerful enterprise adaptable database:

Getting Started With ADABAS & Natural

Friday, February 3, 2017

HTML5 Form App Script Google Sheet

1) Create a new Google Sheet

2) Run Setup

3) Create a new CodePen

1 comment:

...

ABOUT ME

MY FB