Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Post to google spreadsheet from html form

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

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
// 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());
}
<!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>
<!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>
$(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);
}
});
});
});
@amjadm123

This comment has been minimized.

Show comment
Hide comment
@amjadm123

amjadm123 Nov 20, 2015

Can you please tell me how to get/create a google app script and what should I write inside the script for a google spreadsheet (like this: https://script.google.com/macros/s/AKfycbzy3UKiaESk9y2ccnBkdSgCo1zxJ0Kx0qE0_eKro7QYE5yFKJVH/exec)?

Thanks in advance..

Can you please tell me how to get/create a google app script and what should I write inside the script for a google spreadsheet (like this: https://script.google.com/macros/s/AKfycbzy3UKiaESk9y2ccnBkdSgCo1zxJ0Kx0qE0_eKro7QYE5yFKJVH/exec)?

Thanks in advance..

@ewh1021

This comment has been minimized.

Show comment
Hide comment
@ewh1021

ewh1021 Dec 11, 2015

I am also stuck trying to edit the script editor in order to complete this. Any advice on how to build this: https://script.google.com/macros/s/AKfycbzy3UKiaESk9y2ccnBkdSgCo1zxJ0Kx0qE0_eKro7QYE5yFKJVH/exec)
would be very helpful.

ewh1021 commented Dec 11, 2015

I am also stuck trying to edit the script editor in order to complete this. Any advice on how to build this: https://script.google.com/macros/s/AKfycbzy3UKiaESk9y2ccnBkdSgCo1zxJ0Kx0qE0_eKro7QYE5yFKJVH/exec)
would be very helpful.

@Skokan44

This comment has been minimized.

Show comment
Hide comment
@Skokan44

Skokan44 Feb 2, 2016

Hi,

you actually have to make your own script for posting to the correct google Document. How to achive that is described in the example by Martin Hawksey. It is a bit tricky, but basicly go to the document, which you want to fill the data in, Insert -> Script editor and copy-paste the code in the Martins example. Then just follow the instructions.

Hope it helps.

Skokan44 commented Feb 2, 2016

Hi,

you actually have to make your own script for posting to the correct google Document. How to achive that is described in the example by Martin Hawksey. It is a bit tricky, but basicly go to the document, which you want to fill the data in, Insert -> Script editor and copy-paste the code in the Martins example. Then just follow the instructions.

Hope it helps.

@imnotberg

This comment has been minimized.

Show comment
Hide comment
@imnotberg

imnotberg Aug 1, 2016

Great!

Can the validation be linked to a column(s) in the same or different Googlesheet?

Great!

Can the validation be linked to a column(s) in the same or different Googlesheet?

@marqmarti

This comment has been minimized.

Show comment
Hide comment

marqmarti commented Oct 3, 2016

I'm getting this error here https://script.google.com/macros/s/AKfycbzFmu9ty0ZUQ6TYH2AA1QksiTi3vjaUWeZnFQVlb6NXbiKFP3s/exec. Does anyone know what's going on? image

@willpatera

This comment has been minimized.

Show comment
Hide comment
@AVEmedia

This comment has been minimized.

Show comment
Hide comment
@AVEmedia

AVEmedia Oct 26, 2016

im also receiving an error after testing web app URL {"result":"error","error":{"message":"Cannot call method "getRange" of null.","name":"TypeError","fileName":"Code","lineNumber":45,"stack":"\tat Code:45 (handleResponse)\n\tat Code:4 (doGet)\n"}}

im also receiving an error after testing web app URL {"result":"error","error":{"message":"Cannot call method "getRange" of null.","name":"TypeError","fileName":"Code","lineNumber":45,"stack":"\tat Code:45 (handleResponse)\n\tat Code:4 (doGet)\n"}}

@chipmanaged

This comment has been minimized.

Show comment
Hide comment
@chipmanaged

chipmanaged Jan 16, 2017

Solution to TypeError: Cannot call method "getId" of null problem can be found here:
https://gist.github.com/coreyphillips/f27025c86b838cc5372c/revisions?diff=unified
(and then comment out the setup function)

chipmanaged commented Jan 16, 2017

Solution to TypeError: Cannot call method "getId" of null problem can be found here:
https://gist.github.com/coreyphillips/f27025c86b838cc5372c/revisions?diff=unified
(and then comment out the setup function)

@rodcoiler

This comment has been minimized.

Show comment
Hide comment
@rodcoiler

rodcoiler Feb 5, 2017

Hello, is it possible that users can send data only once? a warning like that email already exists.. or something

Hello, is it possible that users can send data only once? a warning like that email already exists.. or something

@architchandra

This comment has been minimized.

Show comment
Hide comment
@architchandra

architchandra Feb 11, 2017

It seems that the .getPublicLock() method has now been deprecated. Any suggestions on how to still make this work?

It seems that the .getPublicLock() method has now been deprecated. Any suggestions on how to still make this work?

@agatstone

This comment has been minimized.

Show comment
Hide comment
@agatstone

agatstone Apr 13, 2017

Still have a problem with : the SETUP function.

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

-> Impossible to call "getId" method.

Any ideas? Thanks a lot!

And when I want to execute the script :
{"result":"error","error":{"message":"Argument non valide : id","name":"Exception","fileName":"Code (InscEDD)","lineNumber":35,"stack":"\tat Code (InscEDD):35 (handleResponse)\n\tat Code (InscEDD):18 (doGet)\n"}}

agatstone commented Apr 13, 2017

Still have a problem with : the SETUP function.

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

-> Impossible to call "getId" method.

Any ideas? Thanks a lot!

And when I want to execute the script :
{"result":"error","error":{"message":"Argument non valide : id","name":"Exception","fileName":"Code (InscEDD)","lineNumber":35,"stack":"\tat Code (InscEDD):35 (handleResponse)\n\tat Code (InscEDD):18 (doGet)\n"}}

@agatstone

This comment has been minimized.

Show comment
Hide comment
@agatstone

agatstone Apr 14, 2017

Problem solved thanks to Serge insas (stackoverflow).

I replaced getActiveSpreadsheet with SpreadsheetApp.openById('ID')
*line73 : var doc = SpreadsheetApp.openById('yours_spreadsheet_url_id');

And now it works perfectly!

Problem solved thanks to Serge insas (stackoverflow).

I replaced getActiveSpreadsheet with SpreadsheetApp.openById('ID')
*line73 : var doc = SpreadsheetApp.openById('yours_spreadsheet_url_id');

And now it works perfectly!

@AthiraRaju

This comment has been minimized.

Show comment
Hide comment
@AthiraRaju

AthiraRaju May 5, 2017

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

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

@foutik

This comment has been minimized.

Show comment
Hide comment
@foutik

foutik Jun 12, 2017

Hi,

I have tried the code and it worked perfectly. Thank you all for your very informative comments, they really helped.
However, when I changed the security access from "anyone, even anonymous" to "anyone" it wouldn't work anymore. I am currently doing on my internship as a data scientist and I really need to use the google spreadsheet to publish my reports but those reports must not be for anonymous users.
Does anyone have a clue on how to use the same method but with the 'anyone' security level ?

Thanks in advance.

foutik commented Jun 12, 2017

Hi,

I have tried the code and it worked perfectly. Thank you all for your very informative comments, they really helped.
However, when I changed the security access from "anyone, even anonymous" to "anyone" it wouldn't work anymore. I am currently doing on my internship as a data scientist and I really need to use the google spreadsheet to publish my reports but those reports must not be for anonymous users.
Does anyone have a clue on how to use the same method but with the 'anyone' security level ?

Thanks in advance.

@karnailBhutto

This comment has been minimized.

Show comment
Hide comment
@karnailBhutto

karnailBhutto Jun 28, 2017

Hello , I am getting a error ,when pass parameter.Script file url https://script.google.com/macros/s/AKfycbwFpFrznzH2lW5Mtu_FiMi9-9jB6nhCetfhScGX-J_PWfrWxQ/exec

{"result":"error","error":{"message":"Invalid argument: id","name":"Exception","fileName":"Code (Test
)","lineNumber":40,"stack":"\tat Code (Test):40 (handleResponse)\n\tat Code (Test):27 (doPost)\n"}}

Thanx in Advance...

Hello , I am getting a error ,when pass parameter.Script file url https://script.google.com/macros/s/AKfycbwFpFrznzH2lW5Mtu_FiMi9-9jB6nhCetfhScGX-J_PWfrWxQ/exec

{"result":"error","error":{"message":"Invalid argument: id","name":"Exception","fileName":"Code (Test
)","lineNumber":40,"stack":"\tat Code (Test):40 (handleResponse)\n\tat Code (Test):27 (doPost)\n"}}

Thanx in Advance...

@dimcoderx

This comment has been minimized.

Show comment
Hide comment
@dimcoderx

dimcoderx Sep 11, 2017

it works ))

it works ))

@bocha95

This comment has been minimized.

Show comment
Hide comment
@bocha95

bocha95 Oct 2, 2017

No errors appeared but not posting anything in the sheet.

Any suggestions??

bocha95 commented Oct 2, 2017

No errors appeared but not posting anything in the sheet.

Any suggestions??

@mrcomptech

This comment has been minimized.

Show comment
Hide comment
@mrcomptech

mrcomptech Oct 9, 2017

When I attempt to use the files above setup on shared hosting and under a subdomain, such as testing.mysite.com the page/form appears as expected but when the form is filled in and the submit button clicked the submit button gets a 'refresh' icon on it and stays that way and no information is posted to the spreadsheet. When I check the Inspect Elements in Firefox 56.0, an error shows up in the security log as follows:

Cross-Origin Request Blocked: The Same Origin Policy disallows reading the remote resource at https://script.google.com/macros/s/{google script}/exec. (Reason: CORS header ‘Access-Control-Allow-Origin’ missing).

Immediately after submitting the form if I visit the URL for the Google Sheets script in a different browser tab the data "undefined" is added to each labeled column on the sheet.

Does anyone know how to alter the java script above so that Cross-Origin Resource Sharing (CORS) will be allowed?

EDIT: I can also enter the following URL in Firefox and the data gets posted correctly and I receive a response back of "success" along with the row number where the data was added to the sheet:

https://script.google.com/macros/s/{id for your google web apps script goes here}/exec?firstName=Joe&lastName=Smith&email=somebody@somewhere.com&address=87555+Main+St.&notes=This+is+a+comment.

NOTE: If you are unable to copy and past the URL above into your web browser address bar (and cusomtize it for your Google Sheet url) and have it add to your Google sheet then you have not setup your Google Sheet and Script correctly. You must be able to do this and receive the success message back "{"result":"success","row":3}" before you bother trying to get any of the HTML or JS code on this post to work.

NOTE: After you have published your script as a web app and need the url for your web app you can get it from the Google Script page by clicking on "Publish" then "Deploy as web app ..." in the text box labeled "Current web app URL:"

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:
{"result":"error","error":{"message":"Invalid argument: id","name":"Exception","fileName":"Code (Web form to Google Sheet)","lineNumber":40,"stack":"\tat Code (Web form to Google Sheet):40 (handleResponse)\n\tat Code (Web form to Google Sheet):4 (doGet)\n"}}
and you my beat yourself in the head until you re-read the instructions realize that you forgot to do this!

EDIT: my remedy for the "Cross-Origin Request Blocked: The Same Origin Policy disallows reading the remote resource at https://script.google.com/macros/s/{google script}/exec. (Reason: CORS header ‘Access-Control-Allow-Origin’ missing)." error was to make a change in the validation-function.js script:
var jqxhr = $.post(url, $form.serialize(), function(data) {
added this >>> jqxhr.crossDomain = true;
console.log("Success! Data: " + data.statusText);
$(location).attr('href',redirectUrl);
})

When I first added the jqhxr.crossDomain line above I made two successful entries to my Google Sheet. But more research revealed that this may not only be needed but is actually doing nothing. I removed this line and the scripts are still working.

mrcomptech commented Oct 9, 2017

When I attempt to use the files above setup on shared hosting and under a subdomain, such as testing.mysite.com the page/form appears as expected but when the form is filled in and the submit button clicked the submit button gets a 'refresh' icon on it and stays that way and no information is posted to the spreadsheet. When I check the Inspect Elements in Firefox 56.0, an error shows up in the security log as follows:

Cross-Origin Request Blocked: The Same Origin Policy disallows reading the remote resource at https://script.google.com/macros/s/{google script}/exec. (Reason: CORS header ‘Access-Control-Allow-Origin’ missing).

Immediately after submitting the form if I visit the URL for the Google Sheets script in a different browser tab the data "undefined" is added to each labeled column on the sheet.

Does anyone know how to alter the java script above so that Cross-Origin Resource Sharing (CORS) will be allowed?

EDIT: I can also enter the following URL in Firefox and the data gets posted correctly and I receive a response back of "success" along with the row number where the data was added to the sheet:

https://script.google.com/macros/s/{id for your google web apps script goes here}/exec?firstName=Joe&lastName=Smith&email=somebody@somewhere.com&address=87555+Main+St.&notes=This+is+a+comment.

NOTE: If you are unable to copy and past the URL above into your web browser address bar (and cusomtize it for your Google Sheet url) and have it add to your Google sheet then you have not setup your Google Sheet and Script correctly. You must be able to do this and receive the success message back "{"result":"success","row":3}" before you bother trying to get any of the HTML or JS code on this post to work.

NOTE: After you have published your script as a web app and need the url for your web app you can get it from the Google Script page by clicking on "Publish" then "Deploy as web app ..." in the text box labeled "Current web app URL:"

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:
{"result":"error","error":{"message":"Invalid argument: id","name":"Exception","fileName":"Code (Web form to Google Sheet)","lineNumber":40,"stack":"\tat Code (Web form to Google Sheet):40 (handleResponse)\n\tat Code (Web form to Google Sheet):4 (doGet)\n"}}
and you my beat yourself in the head until you re-read the instructions realize that you forgot to do this!

EDIT: my remedy for the "Cross-Origin Request Blocked: The Same Origin Policy disallows reading the remote resource at https://script.google.com/macros/s/{google script}/exec. (Reason: CORS header ‘Access-Control-Allow-Origin’ missing)." error was to make a change in the validation-function.js script:
var jqxhr = $.post(url, $form.serialize(), function(data) {
added this >>> jqxhr.crossDomain = true;
console.log("Success! Data: " + data.statusText);
$(location).attr('href',redirectUrl);
})

When I first added the jqhxr.crossDomain line above I made two successful entries to my Google Sheet. But more research revealed that this may not only be needed but is actually doing nothing. I removed this line and the scripts are still working.

@Md-Kutubuddin-Sardar

This comment has been minimized.

Show comment
Hide comment
@Md-Kutubuddin-Sardar

Md-Kutubuddin-Sardar Oct 21, 2017

This works fine. But when I am trying to make the spreadsheet like : https://docs.google.com/spreadsheets/d/1R0fuUdKrh2gT4hrsm0i4lGLf3ataPnZB-QTtaxtg6Jk/edit?usp=sharing , with the files http://s000.tinyupload.com/?file_id=40788916596688879874. How can I make my desired googleSpreadsheet

This works fine. But when I am trying to make the spreadsheet like : https://docs.google.com/spreadsheets/d/1R0fuUdKrh2gT4hrsm0i4lGLf3ataPnZB-QTtaxtg6Jk/edit?usp=sharing , with the files http://s000.tinyupload.com/?file_id=40788916596688879874. How can I make my desired googleSpreadsheet

@MillerApps

This comment has been minimized.

Show comment
Hide comment
@MillerApps

MillerApps Oct 22, 2017

Is there any way to do this using node?

Is there any way to do this using node?

@sheldonkennedy

This comment has been minimized.

Show comment
Hide comment
@sheldonkennedy

sheldonkennedy Dec 27, 2017

Hi there -- thanks for posting this. where do I edit the link so that the script posts to my own spreadsheet, instead of the demo?

Hi there -- thanks for posting this. where do I edit the link so that the script posts to my own spreadsheet, instead of the demo?

@FutoRicky

This comment has been minimized.

Show comment
Hide comment
@FutoRicky

FutoRicky Dec 29, 2017

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

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

@Doctatur

This comment has been minimized.

Show comment
Hide comment
@Doctatur

Doctatur Jan 21, 2018

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

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

This comment has been minimized.

Show comment
Hide comment
@Alex-Broughton

Alex-Broughton Jan 23, 2018

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?

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

This comment has been minimized.

Show comment
Hide comment
@tee-enn

tee-enn Feb 1, 2018

This has saved me! Thank you.

tee-enn commented Feb 1, 2018

This has saved me! Thank you.

@ziaongit

This comment has been minimized.

Show comment
Hide comment
@run-cmw

This comment has been minimized.

Show comment
Hide comment
@run-cmw

run-cmw 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:

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

This comment has been minimized.

Show comment
Hide comment
@JohanssonMartins

JohanssonMartins Feb 18, 2018

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

JohanssonMartins commented Feb 18, 2018

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

@teljotom

This comment has been minimized.

Show comment
Hide comment
@teljotom

teljotom Feb 20, 2018

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"}}

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

This comment has been minimized.

Show comment
Hide comment
@teljotom

teljotom Feb 20, 2018

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

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

@lkdnvc

This comment has been minimized.

Show comment
Hide comment
@lkdnvc

lkdnvc Feb 21, 2018

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

lkdnvc commented Feb 21, 2018

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

@ashutoshmalve

This comment has been minimized.

Show comment
Hide comment

thanks man

@mkarczewski85

This comment has been minimized.

Show comment
Hide comment
@mkarczewski85

mkarczewski85 Mar 23, 2018

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

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

@omer8799

This comment has been minimized.

Show comment
Hide comment
@omer8799

omer8799 Apr 5, 2018

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

omer8799 commented Apr 5, 2018

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

@akrutibagade

This comment has been minimized.

Show comment
Hide comment
@akrutibagade

akrutibagade May 19, 2018

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

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

@Redolance

This comment has been minimized.

Show comment
Hide comment
@Redolance

Redolance Jun 2, 2018

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

Redolance commented Jun 2, 2018

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

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