http://code.google.com/intl/lv/googleapps/appsscript/articles/appengine.html
Hugo Fierro, Google Apps Script TeamJune 2009
Goal
This tutorial guides you through the steps of creating your first Google Apps Script that communicates with a web service built on Google App Engine. We will implement a simple storage service based on the Google App Engine Data Store and use it from a Google Apps Script.Time to Complete
Approximately one hour
Prerequisites
Before you begin this tutorial, you should already be familiar with:
- basic JavaScript and JSON knowledge.
- the content of some of the basic Google Apps Script tutorials. We will only use the
UrlFetchAppandBrowserClasses. - Google App Engine: we provide Python code for the tutorial, but you should be able to easily write the equivalent code in Java. If you have never used Google App Engine before, start by creating a simple hello world application as described here (the relevant sections are 'Introduction', 'The Development Environment', 'Hello World!' and 'Uploading Your Application')
Index
This tutorial is divided into the following sections:
- Section 1: Using the Script Editor
- Section 2: Writing a Google Apps Script that communicates with a Google App Engine service
- Section 3: Using Google App Engine Services from Scripts
- Section 4: Use cases
- Summary
Section 1: Creating and deploying an App Engine service
- Make sure you have downloaded the App Engine SDK as described at here.
- Create a directory for the Google App Engine application called google-apps-script
- Create a file called
backend.pyand copy and paste the code below:# App Engine API (works with both HTTP GET and POST requests):
# To echo request parameters:
# http://hugo-test.appspot.com/rpc?action=Echo¶ms={"key":"a","value":"xxx"}&key=mySecretKey
# Returns: {"value": "xxx", "key": "a"}
# To compute the square of a number:
# http://hugo-test.appspot.com/rpc?action=Square¶ms={"value":11}&key=mySecretKey
# Returns: {"value": 121}
# To store a key value pair:
# http://hugo-test.appspot.com/rpc?action=Store¶ms={"key":"a","value":"xxx"}&key=mySecretKey
# Returns: {"retCode": "Ok"}
# To lookup value by key:
# http://hugo-test.appspot.com/rpc?action=Lookup¶ms={"key":"a"}&key=mySecretKey
# Returns: {"retCode": "Ok", "value": "xxx"}
#
import cgi
from google.appengine.ext import webapp
from google.appengine.ext.webapp.util import run_wsgi_app
from django.utils import simplejson
from google.appengine.ext import db
class RPCMethods:
""" Defines the methods that can be RPCed.
NOTE: Do not allow remote callers access to private/protected "_*" methods.
"""
def Echo(self, params):
return params
def Square(self, params):
return {'value': params['value'] * params['value']}
def Lookup(self, params):
ret = {}
values = db.GqlQuery("SELECT * FROM MyData WHERE keyString = '%s'" % params['key'])
if values.count() > 0:
ret['value'] = values[0].valueString
ret['retCode'] = "Ok"
else:
ret['retCode'] = "NotFound"
return ret
def Store(self, params):
ret = {}
values = db.GqlQuery("SELECT * FROM MyData WHERE keyString = '%s'" % params['key'])
if values.count() > 0:
data = values[0]
else:
data = MyData()
data.valueString = params['value']
data.keyString = params['key']
data.put()
ret['retCode'] = "Ok"
return ret
class RPCHandler(webapp.RequestHandler):
""" Allows the functions defined in the RPCMethods class to be RPCed."""
def __init__(self):
webapp.RequestHandler.__init__(self)
self.methods = RPCMethods()
def get(self):
self.post() # For debugging purposes, you may want this disabled
def post(self):
action = self.request.params['action']
params = self.request.params['params']
key = self.request.params['key']
if not key or key != 'mySecretKey':
self.error(404) # file not found
return
if not action:
self.error(404) # file not found
if action[0] == '_':
self.error(403) # access denied
return
func = getattr(self.methods, action, None)
if not func:
self.error(404) # file not found
return
result = func(simplejson.loads(params))
self.response.out.write(simplejson.dumps(result))
class MyData(db.Model):
keyString = db.StringProperty()
valueString = db.StringProperty()
def main():
app = webapp.WSGIApplication([('/rpc', RPCHandler)], debug=True)
run_wsgi_app(app)
if __name__ == "__main__":
main() - Create a file called
app.yamland copy and paste this:application: google-apps-script-tutorial
version: 1
runtime: python
api_version: 1
handlers:
- url: /rpc
script: backend.py - Start a local web server with the following command:
google_appengine/dev_appserver.py google-apps-script/
- Test this URL in your browser:
http://localhost:8080/rpc?action=Echo¶ms={"example":"blah"}&key=mySecretKey. It should just return theparamsCGI argument. - Create a new App Engine web application at
http://appengine.google.com/a/<DOMAIN.COM>(where <DOMAIN.COM> is the domain that your account is associated with). Click the "Create an Application" button and follow the instructions. You will need to chose a unique application id and we will reference it later in the Google Apps Script code. If you have never used Google App Engine before, you may need to activate your account first with an SMS. - Update the first line of the file
app.yamlto the application id defined in the previous step - Upload the application to Google App Engine by running the following command:
appcfg.py update google-apps-script/
- Test this URL in your browser:
http://<APPLICATION_ID>.appspot.com/rpc?action=Echo¶ms={"example":"blah"}&key=mySecretKey. It should just return theparamsCGI argument. - Congratulations, you now have a server running. Let's now write a Google Apps Script that communicates with it!
Section 2: Writing a Google Apps Script that communicates with a Google App Engine service
- Create a new Spreadsheet.
- Open the Script Editor ('Tools' menu, 'Scripts', then 'Script editor...')
- Copy and Paste the code below and save the script.
var SERVER_BASE_URL = "http://<YOUR_APPLICATION_ID>.appspot.com/"
var SECRET_KEY = "mySecretKey";
function runAppEngineRpc_(action, params) {
var paramsText = JSON.stringify(params);
var url = SERVER_BASE_URL + "rpc";
var payload = "action=" + action + "¶ms=" + paramsText + "&key=" + SECRET_KEY;
var response = UrlFetchApp.fetch(url, {method:'post', payload:payload, contentType:"application/x-www-form-urlencoded"});
if (response.getResponseCode() == 200) {
return JSON.parse(response.getContentText());
}
}
function echoRpc() {
var params = {data: "hello world: this string is echoed by the Google App Engine server"};
var object = runAppEngineRpc_("Echo", params);
Browser.msgBox(object.toSource());
}
function square() {
var params = {};
params.value = Number(Browser.inputBox("Compute the square of this number:"));
var result = runAppEngineRpc_("Square", params);
Browser.msgBox(result.toSource());
}
function storeData() {
var params = {};
params.key = Browser.inputBox("Enter string key");
params.value = Browser.inputBox("Enter string value");
var result = runAppEngineRpc_("Store", params);
Browser.msgBox(result.toSource());
}
function lookupData() {
var params = {};
params.key = Browser.inputBox("Enter string key");
var result = runAppEngineRpc_("Lookup", params);
Browser.msgBox(result.toSource());
} - Edit the value of the constant
SERVER_BASE_URL. You should put you own Google App Engine Application id (it was chosen by you in the previous section). - Copy and Paste the following Open Source library at the bottom of the script. It will let our scripts manipulate JSON objects. The Google App Engine services in this tutorial uses JSON objects for its inputs and outputs.
/*
http://www.JSON.org/json2.js
2009-04-16
Public Domain.
NO WARRANTY EXPRESSED OR IMPLIED. USE AT YOUR OWN RISK.
See http://www.JSON.org/js.html
This file creates a global JSON object containing two methods: stringify
and parse.
JSON.stringify(value, replacer, space)
value any JavaScript value, usually an object or array.
replacer an optional parameter that determines how object
values are stringified for objects. It can be a
function or an array of strings.
space an optional parameter that specifies the indentation
of nested structures. If it is omitted, the text will
be packed without extra whitespace. If it is a number,
it will specify the number of spaces to indent at each
level. If it is a string (such as '\t' or ' '),
it contains the characters used to indent at each level.
This method produces a JSON text from a JavaScript value.
When an object value is found, if the object contains a toJSON
method, its toJSON method will be called and the result will be
stringified. A toJSON method does not serialize: it returns the
value represented by the name/value pair that should be serialized,
or undefined if nothing should be serialized. The toJSON method
will be passed the key associated with the value, and this will be
bound to the object holding the key.
For example, this would serialize Dates as ISO strings.
Date.prototype.toJSON = function (key) {
function f(n) {
// Format integers to have at least two digits.
return n < 10 ? '0' + n : n;
}
return this.getUTCFullYear() + '-' +
f(this.getUTCMonth() + 1) + '-' +
f(this.getUTCDate()) + 'T' +
f(this.getUTCHours()) + ':' +
f(this.getUTCMinutes()) + ':' +
f(this.getUTCSeconds()) + 'Z';
};
You can provide an optional replacer method. It will be passed the
key and value of each member, with this bound to the containing
object. The value that is returned from your method will be
serialized. If your method returns undefined, then the member will
be excluded from the serialization.
If the replacer parameter is an array of strings, then it will be
used to select the members to be serialized. It filters the results
such that only members with keys listed in the replacer array are
stringified.
Values that do not have JSON representations, such as undefined or
functions, will not be serialized. Such values in objects will be
dropped; in arrays they will be replaced with null. You can use
a replacer function to replace those with JSON values.
JSON.stringify(undefined) returns undefined.
The optional space parameter produces a stringification of the
value that is filled with line breaks and indentation to make it
easier to read.
If the space parameter is a non-empty string, then that string will
be used for indentation. If the space parameter is a number, then
the indentation will be that many spaces.
Example:
text = JSON.stringify(['e', {pluribus: 'unum'}]);
// text is '["e",{"pluribus":"unum"}]'
text = JSON.stringify(['e', {pluribus: 'unum'}], null, '\t');
// text is '[\n\t"e",\n\t{\n\t\t"pluribus": "unum"\n\t}\n]'
text = JSON.stringify([new Date()], function (key, value) {
return this[key] instanceof Date ?
'Date(' + this[key] + ')' : value;
});
// text is '["Date(---current time---)"]'
JSON.parse(text, reviver)
This method parses a JSON text to produce an object or array.
It can throw a SyntaxError exception.
The optional reviver parameter is a function that can filter and
transform the results. It receives each of the keys and values,
and its return value is used instead of the original value.
If it returns what it received, then the structure is not modified.
If it returns undefined then the member is deleted.
Example:
// Parse the text. Values that look like ISO date strings will
// be converted to Date objects.
myData = JSON.parse(text, function (key, value) {
var a;
if (typeof value === 'string') {
a =
/^(\d{4})-(\d{2})-(\d{2})T(\d{2}):(\d{2}):(\d{2}(?:\.\d*)?)Z$/.exec(value);
if (a) {
return new Date(Date.UTC(+a[1], +a[2] - 1, +a[3], +a[4],
+a[5], +a[6]));
}
}
return value;
});
myData = JSON.parse('["Date(09/09/2001)"]', function (key, value) {
var d;
if (typeof value === 'string' &&
value.slice(0, 5) === 'Date(' &&
value.slice(-1) === ')') {
d = new Date(value.slice(5, -1));
if (d) {
return d;
}
}
return value;
});
This is a reference implementation. You are free to copy, modify, or
redistribute.
This code should be minified before deployment.
See http://javascript.crockford.com/jsmin.html
USE YOUR OWN COPY. IT IS EXTREMELY UNWISE TO LOAD CODE FROM SERVERS YOU DO
NOT CONTROL.
*/
/*jslint evil: true */
/*global JSON */
/*members "", "\b", "\t", "\n", "\f", "\r", "\"", JSON, "\\", apply,
call, charCodeAt, getUTCDate, getUTCFullYear, getUTCHours,
getUTCMinutes, getUTCMonth, getUTCSeconds, hasOwnProperty, join,
lastIndex, length, parse, prototype, push, replace, slice, stringify,
test, toJSON, toString, valueOf
*/
// Create a JSON object only if one does not already exist. We create the
// methods in a closure to avoid creating global variables.
var JSON = JSON || {};
(function () {
function f(n) {
// Format integers to have at least two digits.
return n < 10 ? '0' + n : n;
}
if (typeof Date.prototype.toJSON !== 'function') {
Date.prototype.toJSON = function (key) {
return this.getUTCFullYear() + '-' +
f(this.getUTCMonth() + 1) + '-' +
f(this.getUTCDate()) + 'T' +
f(this.getUTCHours()) + ':' +
f(this.getUTCMinutes()) + ':' +
f(this.getUTCSeconds()) + 'Z';
};
String.prototype.toJSON =
Number.prototype.toJSON =
Boolean.prototype.toJSON = function (key) {
return this.valueOf();
};
}
var cx = /[\u0000\u00ad\u0600-\u0604\u070f\u17b4\u17b5\u200c-\u200f\u2028-\u202f\u2060-\u206f\ufeff\ufff0-\uffff]/g,
escapable = /[\\\"\x00-\x1f\x7f-\x9f\u00ad\u0600-\u0604\u070f\u17b4\u17b5\u200c-\u200f\u2028-\u202f\u2060-\u206f\ufeff\ufff0-\uffff]/g,
gap,
indent,
meta = { // table of character substitutions
'\b': '\\b',
'\t': '\\t',
'\n': '\\n',
'\f': '\\f',
'\r': '\\r',
'"' : '\\"',
'\\': '\\\\'
},
rep;
function quote(string) {
// If the string contains no control characters, no quote characters, and no
// backslash characters, then we can safely slap some quotes around it.
// Otherwise we must also replace the offending characters with safe escape
// sequences.
escapable.lastIndex = 0;
return escapable.test(string) ?
'"' + string.replace(escapable, function (a) {
var c = meta[a];
return typeof c === 'string' ? c :
'\\u' + ('0000' + a.charCodeAt(0).toString(16)).slice(-4);
}) + '"' :
'"' + string + '"';
}
function str(key, holder) {
// Produce a string from holder[key].
var i, // The loop counter.
k, // The member key.
v, // The member value.
length,
mind = gap,
partial,
value = holder[key];
// If the value has a toJSON method, call it to obtain a replacement value.
if (value && typeof value === 'object' &&
typeof value.toJSON === 'function') {
value = value.toJSON(key);
}
// If we were called with a replacer function, then call the replacer to
// obtain a replacement value.
if (typeof rep === 'function') {
value = rep.call(holder, key, value);
}
// What happens next depends on the value's type.
switch (typeof value) {
case 'string':
return quote(value);
case 'number':
// JSON numbers must be finite. Encode non-finite numbers as null.
return isFinite(value) ? String(value) : 'null';
case 'boolean':
case 'null':
// If the value is a boolean or null, convert it to a string. Note:
// typeof null does not produce 'null'. The case is included here in
// the remote chance that this gets fixed someday.
return String(value);
// If the type is 'object', we might be dealing with an object or an array or
// null.
case 'object':
// Due to a specification blunder in ECMAScript, typeof null is 'object',
// so watch out for that case.
if (!value) {
return 'null';
}
// Make an array to hold the partial results of stringifying this object value.
gap += indent;
partial = [];
// Is the value an array?
if (Object.prototype.toString.apply(value) === '[object Array]') {
// The value is an array. Stringify every element. Use null as a placeholder
// for non-JSON values.
length = value.length;
for (i = 0; i < length; i += 1) {
partial[i] = str(i, value) || 'null';
}
// Join all of the elements together, separated with commas, and wrap them in
// brackets.
v = partial.length === 0 ? '[]' :
gap ? '[\n' + gap +
partial.join(',\n' + gap) + '\n' +
mind + ']' :
'[' + partial.join(',') + ']';
gap = mind;
return v;
}
// If the replacer is an array, use it to select the members to be stringified.
if (rep && typeof rep === 'object') {
length = rep.length;
for (i = 0; i < length; i += 1) {
k = rep[i];
if (typeof k === 'string') {
v = str(k, value);
if (v) {
partial.push(quote(k) + (gap ? ': ' : ':') + v);
}
}
}
} else {
// Otherwise, iterate through all of the keys in the object.
for (k in value) {
if (Object.hasOwnProperty.call(value, k)) {
v = str(k, value);
if (v) {
partial.push(quote(k) + (gap ? ': ' : ':') + v);
}
}
}
}
// Join all of the member texts together, separated with commas,
// and wrap them in braces.
v = partial.length === 0 ? '{}' :
gap ? '{\n' + gap + partial.join(',\n' + gap) + '\n' +
mind + '}' : '{' + partial.join(',') + '}';
gap = mind;
return v;
}
}
// If the JSON object does not yet have a stringify method, give it one.
if (typeof JSON.stringify !== 'function') {
JSON.stringify = function (value, replacer, space) {
// The stringify method takes a value and an optional replacer, and an optional
// space parameter, and returns a JSON text. The replacer can be a function
// that can replace values, or an array of strings that will select the keys.
// A default replacer method can be provided. Use of the space parameter can
// produce text that is more easily readable.
var i;
gap = '';
indent = '';
// If the space parameter is a number, make an indent string containing that
// many spaces.
if (typeof space === 'number') {
for (i = 0; i < space; i += 1) {
indent += ' ';
}
// If the space parameter is a string, it will be used as the indent string.
} else if (typeof space === 'string') {
indent = space;
}
// If there is a replacer, it must be a function or an array.
// Otherwise, throw an error.
rep = replacer;
if (replacer && typeof replacer !== 'function' &&
(typeof replacer !== 'object' ||
typeof replacer.length !== 'number')) {
throw new Error('JSON.stringify');
}
// Make a fake root object containing our value under the key of ''.
// Return the result of stringifying the value.
return str('', {'': value});
};
}
// If the JSON object does not yet have a parse method, give it one.
if (typeof JSON.parse !== 'function') {
JSON.parse = function (text, reviver) {
// The parse method takes a text and an optional reviver function, and returns
// a JavaScript value if the text is a valid JSON text.
var j;
function walk(holder, key) {
// The walk method is used to recursively walk the resulting structure so
// that modifications can be made.
var k, v, value = holder[key];
if (value && typeof value === 'object') {
for (k in value) {
if (Object.hasOwnProperty.call(value, k)) {
v = walk(value, k);
if (v !== undefined) {
value[k] = v;
} else {
delete value[k];
}
}
}
}
return reviver.call(holder, key, value);
}
// Parsing happens in four stages. In the first stage, we replace certain
// Unicode characters with escape sequences. JavaScript handles many characters
// incorrectly, either silently deleting them, or treating them as line endings.
cx.lastIndex = 0;
if (cx.test(text)) {
text = text.replace(cx, function (a) {
return '\\u' +
('0000' + a.charCodeAt(0).toString(16)).slice(-4);
});
}
// In the second stage, we run the text against regular expressions that look
// for non-JSON patterns. We are especially concerned with '()' and 'new'
// because they can cause invocation, and '=' because it can cause mutation.
// But just to be safe, we want to reject all unexpected forms.
// We split the second stage into 4 regexp operations in order to work around
// crippling inefficiencies in IE's and Safari's regexp engines. First we
// replace the JSON backslash pairs with '@' (a non-JSON character). Second, we
// replace all simple value tokens with ']' characters. Third, we delete all
// open brackets that follow a colon or comma or that begin the text. Finally,
// we look to see that the remaining characters are only whitespace or ']' or
// ',' or ':' or '{' or '}'. If that is so, then the text is safe for eval.
if (/^[\],:{}\s]*$/.
test(text.replace(/\\(?:["\\\/bfnrt]|u[0-9a-fA-F]{4})/g, '@').
replace(/"[^"\\\n\r]*"|true|false|null|-?\d+(?:\.\d*)?(?:[eE][+\-]?\d+)?/g, ']').
replace(/(?:^|:|,)(?:\s*\[)+/g, ''))) {
// In the third stage we use the eval function to compile the text into a
// JavaScript structure. The '{' operator is subject to a syntactic ambiguity
// in JavaScript: it can begin a block or an object literal. We wrap the text
// in parens to eliminate the ambiguity.
j = eval('(' + text + ')');
// In the optional fourth stage, we recursively walk the new structure, passing
// each name/value pair to a reviver function for possible transformation.
return typeof reviver === 'function' ?
walk({'': j}, '') : j;
}
// If the text is not JSON parseable, then a SyntaxError is thrown.
throw new SyntaxError('JSON.parse');
};
}
}()); - From the Script Editor, run the function
echoRpc. You should get a message box in the spreadsheet tab similar to this:
Section 3: Using Google App Engine Services from Scripts
In the last step of the previous section we already used theEcho service, which simply returns the arguments that are passed in as inputs. Let's play with some of the other services. They're also pretty simple but will hopefully inspire much more complex use cases. - From the Script Editor, run the function
squareand enter the number 11.
You should get a message box with the value 121. This has been computed in your App Engine application, not in the script.
- Now run the function
lookupDataand enter the keytest. It will send a request to App Engine to fetch data stored in a Datastore
You should get a mesage box with the message "NotFound", as we have stored no data yet.
- Now run the function
storeData. Enter the the keytest.
Enter the value "Hello World!".
You should get a confirmation that the data was stored successfully in App Engine.
- Finally, let's run
lookupDataa second time and retrieve the data we stored in the previous step. Enter the keytest. You should get the following response, including the string "Hello World!"
Section 4: Use cases
The UrlFetchApp class provides a powerful way of extending scripts, leverage existing web services, easily importing and exporting data and overcoming some of the limitations of Google Apps Script.
You may want to use a similar framework to do heavy computations more efficiently in App Engine or your own servers (e.g. sophisticated/proprietary financial computations, image processing, etc...).
Also, you may be interested in learning about Google Secure Data Connector for accessing corporate services behing firewalls and the Google Feed Server for accessing databases.
Комментариев нет:
Отправить комментарий