Skip to content

Instantly share code, notes, and snippets.

@willpatera

willpatera/Google-Sheet-Form-Post.md

Last active May 3, 2024 12:57
Show Gist options
  • Save willpatera/ee41ae374d3c9839c2d6 to your computer and use it in GitHub Desktop.
Save willpatera/ee41ae374d3c9839c2d6 to your computer and use it in GitHub Desktop.
Download ZIP
Post to google spreadsheet from html form
Raw
Google-Sheet-Form-Post.md

Overview

This collection of files serves as a simple static demonstration of how to post to a google spreadsheet from an external html <form> following the example by Martin Hawksey

Depreciation Warning: This code is not maintained, and should be seen as reference implementation only. If you're looking to add features or update, fork the code and update as needed.

Run example

You should be able to just open index.html in your browser and test locally.

However if there are some permissions errors you can make a quick html server with python. Open terminal and cd to the directory where the gist files are located and enter python -m SimpleHTTPServer. By default this creates a local server at localhost:8000

If you're using python 3 the command differs slightly.

Google Spreadsheet

The spreadsheet is located here

TODO

  • Resolve 405 Error on Safari
  • Workaround for 405 error with Safari browser check
  • Add user feedback while ajax is submitting the request
  • Validation using Bootstrap Validator
Raw
google_script.gs
// original from: http://mashe.hawksey.info/2014/07/google-sheets-as-a-database-insert-with-apps-script-using-postget-methods-with-ajax-example/
function doGet(e){
return handleResponse(e);
}
// Usage
// 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 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());
}
Raw
index.html
<!DOCTYPE html>
<html>
<head>
<title>Post to Google Sheet Demo</title>
<!-- bootstrap & fontawesome css -->
<link href="http://cdn.jsdelivr.net/bootstrap/3.2.0/css/bootstrap.min.css" rel="stylesheet"/>
<link rel="stylesheet" href="http://cdn.jsdelivr.net/fontawesome/4.1.0/css/font-awesome.min.css" />
<!-- BootstrapValidator CSS -->
<link rel="stylesheet" href="http://cdn.jsdelivr.net/jquery.bootstrapvalidator/0.5.0/css/bootstrapValidator.min.css"/>
<!-- jQuery and Bootstrap JS -->
<script type="text/javascript" src="http://cdn.jsdelivr.net/jquery/1.11.1/jquery.min.js"></script>
<script type="text/javascript" src="http://cdn.jsdelivr.net/bootstrap/3.2.0/js/bootstrap.min.js"></script>
<!-- BootstrapValidator JS -->
<script type="text/javascript" src="http://cdn.jsdelivr.net/jquery.bootstrapvalidator/0.5.0/js/bootstrapValidator.min.js"></script>
<!-- Animated Loading Icon -->
<style type="text/css">
.glyphicon-refresh-animate {
-animation: spin .7s infinite linear;
-webkit-animation: spin2 .7s infinite linear;
}
@-webkit-keyframes spin2 {
from { -webkit-transform: rotate(0deg);}
to { -webkit-transform: rotate(360deg);}
}
@keyframes spin {
from { transform: scale(1) rotate(0deg);}
to { transform: scale(1) rotate(360deg);}
}
</style>
</head>
<body>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-lg-12">
<h2>Post to Google Sheets with form validation</h2>
<p>
Based on Martin Hawksey's <a href="http://mashe.hawksey.info/2014/07/google-sheets-as-a-database-insert-with-apps-script-using-postget-methods-with-ajax-example/" target="_blank">example</a>.
<p>
</div>
</div>
<hr>
<form class="form-horizontal" role="form" id="test-form">
<div class="form-group">
<label class="col-lg-3 control-label">First Name</label>
<div class="col-lg-3 inputGroupContainer">
<div class="input-group">
<input type="text" class="form-control" name="firstName" placeholder="First Name"/>
</div>
</div>
</div>
<div class="form-group">
<label class="col-lg-3 control-label">Last Name</label>
<div class="col-lg-3 inputGroupContainer">
<div class="input-group">
<input type="text" class="form-control" name="lastName" placeholder="Last Name"/>
</div>
</div>
</div>
<div class="form-group">
<label class="col-lg-3 control-label">Email</label>
<div class="col-lg-3 inputGroupContainer">
<div class="input-group">
<input type="text" class="form-control" name="email" placeholder="name@domain.com"/>
</div>
</div>
</div>
<div class="form-group">
<label class="col-lg-3 control-label">Address</label>
<div class="col-lg-3 inputGroupContainer">
<div class="input-group col-lg-10">
<textarea type="text" class="form-control" name="address" placeholder="Enter your address here" rows="4" style="resize: vertical;"></textarea>
</div>
</div>
</div>
<div class="form-group">
<label class="col-lg-3 control-label">Notes</label>
<div class="col-lg-3 inputGroupContainer">
<div class="input-group col-lg-10">
<textarea type="text" class="form-control" name="notes" placeholder="Comments or questions?" rows="4" style="resize: vertical;"></textarea>
</div>
</div>
</div>
<div class="form-group">
<div class="col-lg-9 col-lg-offset-3">
<button type="submit" class="btn btn-default" id="postForm">Submit</button>
</div>
</div>
</form>
</div>
</body>
<footer>
<script src="validation-functions.js"></script>
</footer>
</html>
Raw
success-page.html
<!DOCTYPE html>
<html>
<head>
<title>Post to Google Sheet Demo</title>
<!-- bootstrap & fontawesome css -->
<link href="http://cdn.jsdelivr.net/bootstrap/3.2.0/css/bootstrap.min.css" rel="stylesheet"/>
<link rel="stylesheet" href="http://cdn.jsdelivr.net/fontawesome/4.1.0/css/font-awesome.min.css" />
<!-- BootstrapValidator CSS -->
<link rel="stylesheet" href="http://cdn.jsdelivr.net/jquery.bootstrapvalidator/0.5.0/css/bootstrapValidator.min.css"/>
<!-- jQuery and Bootstrap JS -->
<script type="text/javascript" src="http://cdn.jsdelivr.net/jquery/1.11.1/jquery.min.js"></script>
<script type="text/javascript" src="http://cdn.jsdelivr.net/bootstrap/3.2.0/js/bootstrap.min.js"></script>
<!-- BootstrapValidator JS -->
<script type="text/javascript" src="http://cdn.jsdelivr.net/jquery.bootstrapvalidator/0.5.0/js/bootstrapValidator.min.js"></script>
</head>
<body>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-lg-12">
<h2>Success</h2>
<p>
Thanks for submitting the form - check out the responses submitted in the <a href="https://docs.google.com/spreadsheets/d/1p6X_HejWbIBx3eDDSacNvkVI3vFkFMSq4XW4rUDTyAw/edit?usp=sharing" target="_blank">spreadsheet</a>
<p>
</div>
</div>
<hr>
</body>
<footer>
</footer>
</html>
Raw
validation-functions.js
$(document).ready(function() {
$('#test-form').bootstrapValidator({
//submitButtons: '#postForm',
// To use feedback icons, ensure that you use Bootstrap v3.1.0 or later
feedbackIcons: {
valid: 'glyphicon glyphicon-ok',
invalid: 'glyphicon glyphicon-remove',
validating: 'glyphicon glyphicon-refresh'
},
fields: {
firstName: {
message: 'The first name is not valid',
validators: {
notEmpty: {
message: 'The first name is required and cannot be empty'
},
stringLength: {
min: 1,
max: 30,
message: 'The first name must be more than 1 and less than 30 characters long'
},
regexp: {
regexp: /^[A-z]+$/,
message: 'The first name can only accept alphabetical input'
},
}
},
lastName: {
message: 'Last Name is not valid',
validators: {
notEmpty: {
message: 'Last Name is required and cannot be empty'
},
stringLength: {
min: 1,
max: 30,
message: 'Last Name must be more than 1 and less than 30 characters long'
},
regexp: {
regexp: /^[A-z]+$/,
message: 'Last Names can only consist of alphabetical characters'
},
}
},
email: {
validators: {
notEmpty: {
message: 'The email address is required and cannot be empty'
},
emailAddress: {
message: 'The email address is not a valid'
}
}
},
address: {
message: 'Address is not valid',
validators: {
notEmpty: {
message: 'Address is required and cannot be empty'
}
}
},
}
})
.on('success.form.bv', function(e) {
// Prevent form submission
e.preventDefault();
// Get the form instance
var $form = $(e.target);
// Get the BootstrapValidator instance
var bv = $form.data('bootstrapValidator');
// Use Ajax to submit form data
var url = 'https://script.google.com/macros/s/AKfycbzy3UKiaESk9y2ccnBkdSgCo1zxJ0Kx0qE0_eKro7QYE5yFKJVH/exec';
var redirectUrl = 'success-page.html';
// show the loading
$('#postForm').prepend($('<span></span>').addClass('glyphicon glyphicon-refresh glyphicon-refresh-animate'));
var jqxhr = $.post(url, $form.serialize(), function(data) {
console.log("Success! Data: " + data.statusText);
$(location).attr('href',redirectUrl);
})
.fail(function(data) {
console.warn("Error! Data: " + data.statusText);
// HACK - check if browser is Safari - and redirect even if fail b/c we know the form submits.
if (navigator.userAgent.search("Safari") >= 0 && navigator.userAgent.search("Chrome") < 0) {
//alert("Browser is Safari -- we get an error, but the form still submits -- continue.");
$(location).attr('href',redirectUrl);
}
});
});
});
@FutoRicky
Copy link

Thanks for this. I'm getting a 404, what am I doing wrong?

@Doctatur
Copy link

Hello everyone and thks for posting this solution !
I have a question however: how to send adapted post request without ajax, I mean from code like c#. I cannot find a way to construct a post request to correctly present the parameters to the google script handler...

@Alex-Broughton
Copy link

Its having an issue with e.preventDefault(); in validation-functions.js (line 32). What does this line do? When I take it out, the form will accept my input and change the resource locator to file:///.../.../.../index.html?Name=Alex (my input and variable). After that it does nothing. Any suggestions?

@tee-enn
Copy link

tee-enn commented Feb 1, 2018

This has saved me! Thank you.

@ziaongit
Copy link

ziaongit commented Feb 1, 2018

I did search and this solution works for me.

http://www.nanoblogsonline.com/2018/02/how-to-submit-html-form-to-google-sheet.html

@run-cmw
Copy link

run-cmw commented Feb 3, 2018

@mrcomptech Thank you soooo much!! This was the solution to my error:
EDIT: When you are performing the addition of the Google Apps Script remember to run click "Run" --> "Setup" if you don't you may get this error:

@JohanssonMartins
Copy link

JohanssonMartins commented Feb 18, 2018 •
edited

Is there any way that the user can send the attachment from the form and getting it into the google drive??

@teljotom
Copy link

how to fix this error??????????

{"result":"error","error":{"message":"Cannot call method "getRange" of null.","name":"TypeError","fileName":"Code (custom form respons script)","lineNumber":43,"stack":"\tat Code (custom form respons script):43 (handleResponse)\n\tat Code (custom form respons script):25 (doPost)\n"}}

@teljotom
Copy link

how do I show a thank you page instead of showing results like {"result":"success","data":"{"your_email":.............._ this please help me

thanks in advnz

@dftpnd
Copy link

dftpnd commented Feb 21, 2018

Alternative for window.location.href = "http://some-site.com"?

@Ashutosh-Malve
Copy link

thanks man

@mkarczewski85
Copy link

'Cannot call method "getRange" of null' --> any suggestions?

@omer8799
Copy link

omer8799 commented Apr 5, 2018

Hi, Bootstrap Validator is not supported any more. I suggest you look at - http://formvalidation.io/

@akrutibagade
Copy link

hii frds....this code is not work on google spread sheet.... what am i do?
capture

@Redolance
Copy link

Redolance commented Jun 2, 2018 •
edited

For any one how get that error :
"result":"error","error":{"message":"Invalid argument: id"

In some cases it would help :
Replace line 40:
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
With
var doc = SpreadsheetApp.getActiveDocument();
Then re setup the script. and update web deploy again
hope this help someone

@rupaajoshi
Copy link

rupaajoshi commented Jun 29, 2018 •
edited

I am using similar code but little change on my html form to display success/error, I am successfully able to enter the records in the google sheet but i am not getting any response back from the script, if i check for response or data objects on UI its either null or undefined or I get reference error message. Also if I click on the variable which stores my serialized data and click on that link it show The script completed but did not return anything. I think because of this I am getting response or data object as not defined or null. Can you please tell me how should i get this corrected as I can see the records are getting inserted but I cant show the message as successful on UI

@BoomerCZE
Copy link

BoomerCZE commented Jul 24, 2018 •
edited

Hi,

thanks this is actually amazing.
Anyone knows how to open the index.html site in sidebar inside google sheets?

Thanks in advance,
Matt

@rc1021
Copy link

rc1021 commented Sep 24, 2018

@mkarczewski85
@teljotom
@AVEmedia

Please, confirm your sheet name in google_script.gs.

// Enter sheet name where data is to be written below
var SHEET_NAME = "Sheet1";

@jacobsilver2
Copy link

I'm having the same issue of "Cannot call method "getRange" of null."
I've confirmed var SHEET_NAME = 'Sheet1'; is correct...
Any ideas?

@robertmccarson
Copy link

I follow the example above, with the exception of deleting the word Timestamp from my sheet, and nothing posts to my sheet

@JackVanson
Copy link

Hi,

I am having the same issue with "Cannot call method "getRange" of null." Has anyone worked out why this is and how to fix it?

Thanks

Jack

@KarlPiper
Copy link

KarlPiper commented Sep 22, 2019 •
edited

Got things up and running, but how do you get data back out with jQuery?

@developeron29
Copy link

Same issue Cannot call method "getRange" of null

@KeithETruesdell
Copy link

Something that I added to help was a "clear" or "reset" function.
After line 31 ( var row = []; ), I added the following snippet.

  // IF there is a url parameter of reset - then it will clear sheet
    if (e.parameter.hasOwnProperty("reset")) {
      // clear everything except the header row until the last row for all headers      
      sheet.getRange(headRow + 1,1, nextRow,headers.length).clear();
      
      // return the clear result
      return ContentService
          .createTextOutput(JSON.stringify({"result": "success", "cleared": nextRow }))
          .setMimeType(ContentService.MimeType.JSON);
    }

Then you can add "reset" to the URL parameters and it will clear everything except for the headers

@ziaongit
Copy link

Hi,
I am providing the same service on Fiverr with very low prize. Please have look on my Gig.
https://www.fiverr.com/share/yvYWrG

Kind Regards
Zia

@ElectricSilk
Copy link

How would I integrate a File Upload field that would store the file in Google Drive and just add the link in the Google Sheet?

@sweetygarg
Copy link

Very helpful tutorial. But I have upload image field in my form. Please help me how I will manage image uploading..

@FergusCoulter
Copy link

Hey, this is a great script. It's odd how it's posting with a GET command but y'know still cool.

One little question though, how secure is this? The script is visible via the inspect source tool so the details can be seen, script ID and what not. I'm not 100% on web security but I would view this as an issue. Can you please confirm?

@hossainahmedkhan
Copy link

Dear,

Once the index.html is created into 'gd' then how should the data go to spreadsheet! How is the script would look Like!
Thank you.

@elson-faal
Copy link

works here, but only post text data, don't work with de input type file, does note upload files.

How can i make a form that upload a file from inputs type file AND set de data of other inputs (or textareas etc) in the spreadsheet AND put a link to the file in the same spreadsheet?

I managed to upload a file by this method: https://issuetracker.google.com/issues/36764534
i also managed to set other kind of data to the google drive's spreadsheet, from other form, by the method of this page.

but i can't do both in the same html form. Is it possible?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment