google spread Sheets as DB:
=======================
Google Sheets as a Database – INSERT with Apps Script using POST/GET methods (with ajax eg)
-----------------------------------------------------------------------------------------------------------------------
goto Google.drive.com
Click->New-> google sheets
==> open new untitled spreadsheet
Name on your own ( Presale Email Subscribers)
Enter fields you want to store from Form
==> eg .timestamp, name, Email, phone,etc....
In Menu(sheet) ->Tools->Script Editor click
It open new Gogole app script editor with code.gs fine name
Enter project name ( Form Script)
Mention Sheet name in this script is mandatory. Eg. Sheet1
Enter the below 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());
}
Make to doPost() or doGet()
Click on the Save icon. Set the dropdown in the nav bar to 'setup’ and click on the right-pointing triangle to its left to run this function. It should show 'Running function setup’ and then put up a dialog 'Authorization Required’. Click on Continue. In the next dialog 'Request for permission - Formscript would like to’ click on Accept.
In the menus click on File > Manage Versions… We must save a version of the script for it to be called. In the box labeled 'Describe what has changed’ type 'Initial version’ and click on 'Save New Version’, then on 'OK’.
Back to the menus: click on Resources > Current roject’s triggers. In this dialog click on 'No triggers set up. Click here to add one now’. In the dropdowns select 'doPost’, 'From spreadsheet’, and 'On form submit’, then click on 'Save’.
Back to the menus: click on Publish > Deploy as web app… For 'Who has access to the app:’ select 'Anyone, even anonymous’. Leave 'Execute the app as:’ set to 'Me’ and Project Version to '1’. Click the 'Deploy’ button.
A dialog should appear announcing 'This project is now deployed as a web app’. Copy the Current web app URL from the dialog; it should look something like:
https://script.google.com/macros/s/AKfycbw6RTOxn5OT_BIw9Nl_3KoFSXEQEbiKSZCLyombb1YqkGfRKUSz/exec
Click OK.
Now go back to google-sheet.js and replace 'SCRIPT URL GOES HERE’ with the URL copied from the dialog.
Display or refresh the index.html web page. Enter data into the four fields and click on the 'Send’ button. Within a few seconds that data should appear in your Google sheet. If your browser is Google Chrome, right-click in the web page and click on Inspect Element > Console. It should show:
Hooray, it worked!
- Object
success
- Object
Well done! You can now modify this form and drop it into any web page to collect–at no cost–responses from those who visit your page. With a little research and effort you may be able to get Google Apps to email every time someone submits your form.
I plan to use it to collect names and email addresses of those who visit an MVP page, where the “product” is just a description of the SaaS site I have in mind. I may also use it on a stand-alone blog where I do not want to let readers post comments, but I would rather they send them to me so I can screen, remove spam, edit, and comment on them before putting them up.
full code for index.html and sheet.js
---------------------------------------
<!DOCTYPE html>
<html lang='en'>
<head>
<meta charset='utf-8'>
<meta content='IE=edge' http-equiv='X-UA-Compatible'>
<meta content='width=device-width, initial-scale=1' name='viewport'>
</head>
<body>
<!-- Contact Form - sent to a Google Sheet -->
<form id='foo'>
<p>
<label>Name</label>
<input id='name' name='name' type='text'>
</p><p>
<label>Email Address</label>
<input id='email' name='email' type='email'>
</p><p>
<label>Phone Number</label>
<input id='phone' name='phone' type='tel'>
</p><p>
<label>Message</label>
<textarea id='message' name='message' rows='5'></textarea>
</p>
<div id='success'></div>
<button type='submit'>Send</button>
</form>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
<!-- Custom Theme JavaScript -->
<script>
// Variable to hold request
var request;
// Bind to the submit event of our form
$("#foo").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: "https://script.google.com/macros/s/AKfycbxNDXmWaInWS-aHhBB8XgSbxjR-kk0UI0C05g3pUQ2G8KghHPU/exec",
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);
alert("success");
location.reload();
});
// 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();
});
</script>
</html>
=======================
Google Sheets as a Database – INSERT with Apps Script using POST/GET methods (with ajax eg)
-----------------------------------------------------------------------------------------------------------------------
goto Google.drive.com
Click->New-> google sheets
==> open new untitled spreadsheet
Name on your own ( Presale Email Subscribers)
Enter fields you want to store from Form
==> eg .timestamp, name, Email, phone,etc....
In Menu(sheet) ->Tools->Script Editor click
It open new Gogole app script editor with code.gs fine name
Enter project name ( Form Script)
Mention Sheet name in this script is mandatory. Eg. Sheet1
Enter the below 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());
}
Make to doPost() or doGet()
Click on the Save icon. Set the dropdown in the nav bar to 'setup’ and click on the right-pointing triangle to its left to run this function. It should show 'Running function setup’ and then put up a dialog 'Authorization Required’. Click on Continue. In the next dialog 'Request for permission - Formscript would like to’ click on Accept.
In the menus click on File > Manage Versions… We must save a version of the script for it to be called. In the box labeled 'Describe what has changed’ type 'Initial version’ and click on 'Save New Version’, then on 'OK’.
Back to the menus: click on Resources > Current roject’s triggers. In this dialog click on 'No triggers set up. Click here to add one now’. In the dropdowns select 'doPost’, 'From spreadsheet’, and 'On form submit’, then click on 'Save’.
Back to the menus: click on Publish > Deploy as web app… For 'Who has access to the app:’ select 'Anyone, even anonymous’. Leave 'Execute the app as:’ set to 'Me’ and Project Version to '1’. Click the 'Deploy’ button.
A dialog should appear announcing 'This project is now deployed as a web app’. Copy the Current web app URL from the dialog; it should look something like:
https://script.google.com/macros/s/AKfycbw6RTOxn5OT_BIw9Nl_3KoFSXEQEbiKSZCLyombb1YqkGfRKUSz/exec
Click OK.
Now go back to google-sheet.js and replace 'SCRIPT URL GOES HERE’ with the URL copied from the dialog.
Display or refresh the index.html web page. Enter data into the four fields and click on the 'Send’ button. Within a few seconds that data should appear in your Google sheet. If your browser is Google Chrome, right-click in the web page and click on Inspect Element > Console. It should show:
Hooray, it worked!
- Object
success
- Object
Well done! You can now modify this form and drop it into any web page to collect–at no cost–responses from those who visit your page. With a little research and effort you may be able to get Google Apps to email every time someone submits your form.
I plan to use it to collect names and email addresses of those who visit an MVP page, where the “product” is just a description of the SaaS site I have in mind. I may also use it on a stand-alone blog where I do not want to let readers post comments, but I would rather they send them to me so I can screen, remove spam, edit, and comment on them before putting them up.
full code for index.html and sheet.js
---------------------------------------
<!DOCTYPE html>
<html lang='en'>
<head>
<meta charset='utf-8'>
<meta content='IE=edge' http-equiv='X-UA-Compatible'>
<meta content='width=device-width, initial-scale=1' name='viewport'>
</head>
<body>
<!-- Contact Form - sent to a Google Sheet -->
<form id='foo'>
<p>
<label>Name</label>
<input id='name' name='name' type='text'>
</p><p>
<label>Email Address</label>
<input id='email' name='email' type='email'>
</p><p>
<label>Phone Number</label>
<input id='phone' name='phone' type='tel'>
</p><p>
<label>Message</label>
<textarea id='message' name='message' rows='5'></textarea>
</p>
<div id='success'></div>
<button type='submit'>Send</button>
</form>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script>
<!-- Custom Theme JavaScript -->
<script>
// Variable to hold request
var request;
// Bind to the submit event of our form
$("#foo").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: "https://script.google.com/macros/s/AKfycbxNDXmWaInWS-aHhBB8XgSbxjR-kk0UI0C05g3pUQ2G8KghHPU/exec",
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);
alert("success");
location.reload();
});
// 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();
});
</script>
</html>
