Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google Sheets as a Database – Authenticated INSERT with Apps Script using Execution API - Working Example
<html>
<head>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<title>Google Sheets as a Database – Authenticated INSERT with Apps Script using Execution API - Working Example</title>
<script type="text/javascript">
// Your Client ID can be retrieved from your project in the Google
// Developer Console, https://console.developers.google.com
var CLIENT_ID = '<INSERT_YOUR_CLIENT_ID>';
var SCRIPT_ID = '<INSERT_YOUR_SCRIPT_ID>';
var SCOPES = ['https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/userinfo.email'];
/**
* Check if current user has authorized this application.
*/
function checkAuth() {
gapi.auth.authorize({
'client_id': CLIENT_ID,
'scope': SCOPES,
'immediate': true
}, handleAuthResult);
}
/**
* Handle response from authorization server.
*
* @param {Object} authResult Authorization result.
*/
function handleAuthResult(authResult) {
var authorizeDiv = document.getElementById('authorize-div');
var loadDiv = document.getElementById('loading');
if (authResult && !authResult.error) {
// Hide auth UI, then load client library.
authorizeDiv.style.display = 'none';
loadDiv.style.display = 'block';
callScriptFunction();
} else {
// Show auth UI, allowing the user to initiate authorization by
// clicking authorize button.
authorizeDiv.style.display = 'inline';
}
}
/**
* Initiate auth flow in response to user clicking authorize button.
*
* @param {Event} event Button click event.
*/
function handleAuthClick(event) {
gapi.auth.authorize({
client_id: CLIENT_ID,
scope: SCOPES,
immediate: false
},
handleAuthResult);
return false;
}
/**
* Calls an Apps Script function to get the data from the Google Sheet
*/
function callScriptFunction() {
// Create an execution request object.
var request = {
'function': 'getData'
};
// Make the API request.
var op = gapi.client.request({
'root': 'https://script.googleapis.com',
'path': 'v1/scripts/' + SCRIPT_ID + ':run',
'method': 'POST',
'body': request
});
op.execute(function(resp){
handleGetDataResponse(resp);
});
}
function handleGetDataResponse(resp) {
if (resp.error && resp.error.status) {
// The API encountered a problem before the script
// started executing.
appendPre('Error calling API:');
appendPre(JSON.stringify(resp, null, 2));
} else if (resp.error) {
// The API executed, but the script returned an error.
// Extract the first (and only) set of error details.
// The values of this object are the script's 'errorMessage' and
// 'errorType', and an array of stack trace elements.
var error = resp.error.details[0];
appendPre('Script error message: ' + error.errorMessage);
if (error.scriptStackTraceElements) {
// There may not be a stacktrace if the script didn't start
// executing.
appendPre('Script error stacktrace:');
for (var i = 0; i < error.scriptStackTraceElements.length; i++) {
var trace = error.scriptStackTraceElements[i];
appendPre('\t' + trace.function+':' + trace.lineNumber);
}
}
} else {
// The structure of the result will depend upon what the Apps
// Script function returns.
console.log(resp.response.result);
var comments = resp.response.result.comments;
if (Object.keys(comments).length == 0) {
appendPre('No comments returned!');
} else {
var ul = document.getElementById('commentlist')
ul.innerHTML = '';
Object.keys(comments).forEach(function(id) {
appendComment(comments[id]);
});
var loadDiv = document.getElementById('loading');
loadDiv.style.display = 'none';
}
}
}
jQuery(document).ready(function($) {
$('#foo').find("input, select, button, textarea").prop("disabled", false);
$("#foo").submit(function(event) {
$form = $('#foo');
var $inputs = $form.find("input, select, button, textarea");
// serialize the data in the form
var serializedData = $form.serializeObject();
// Create an execution request object.
// Create execution request.
var request = {
'function': 'setData',
'parameters': serializedData,
'devMode': true // Optional.
};
// Make the API request.
var op = gapi.client.request({
'root': 'https://script.googleapis.com',
'path': 'v1/scripts/' + SCRIPT_ID + ':run',
'method': 'POST',
'body': request
});
op.execute(function(resp){
handleGetDataResponse(resp);
});
// prevent default posting of form
event.preventDefault();
});
});
/**
* Append a pre element to the body containing the given message
* as its text node.
*
* @param {string} message Text to be placed in pre element.
*/
function appendPre(message) {
var pre = document.getElementById('output');
var textContent = document.createTextNode(message + '\n');
pre.appendChild(textContent);
}
function appendComment(comment) {
var comment_list = document.getElementById('commentlist');
var commentNode = document.createElement("li");
commentNode.innerHTML = "<article><header><cite><p class='comment-author-name'>" + comment.name + " - " + comment.Email + "</p></cite><time>" + comment.Timestamp + "</time></header><section class='comment-content comment'><p>" + comment.comment + "</p> </section></article>";
comment_list.appendChild(commentNode);
}
$.fn.serializeObject = function() {
var o = {};
var a = this.serializeArray();
$.each(a, function() {
if (o[this.name] !== undefined) {
if (!o[this.name].push) {
o[this.name] = [o[this.name]];
}
o[this.name].push(this.value || '');
} else {
o[this.name] = this.value || '';
}
});
return o;
};
</script>
<style>
.comments-area article header cite, .comments-area article header time {
margin-left:0px;
}
.site {
padding-top:15px;
}
</style>
</head>
<body>
<div id="page" class="hfeed site">
<div id="authorize-div" style="display: none"> <span>Authorize access to Google Apps Script Execution API</span>
<!--Button for the user to click to initiate auth sequence -->
<button id="authorize-button" onclick="handleAuthClick(event)"> Authorize </button>
</div>
<pre id="output"></pre>
<div id="respond" class="comment-respond">
<form id="foo">
<p>
<label for="name">Name:</label>
<input id="name" name="name" type="text" value="" disabled/>
</p>
<p>
<label for="comment">Comment:</label>
<br/>
<textarea id="comment" name="comment" cols="40" disabled></textarea>
</p>
<p align="center"><strong>Please note:</strong> By submitting this form by clicking 'Send' you permit your email address (displayed in an obscured format), name and comment will be recorded and displayed below. Your email address will not be used in any other way (e.g. direct marketing)</p>
<input type="submit" value="Send" />
</form>
</div>
<div id="comments" class="comments-area">
<div id="loading" style="display:none">
<article>Loading...</article>
</div>
<ol class="commentlist" id="commentlist">
</ol>
</div>
</div>
</body>
<script src="https://apis.google.com/js/client.js?onload=checkAuth"></script>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.