Tutorial: Leveraging Google App Engine services from scripts

Hugo Fierro, Google Apps Script Team
Ikai Lan, Google Apps Script Team
Originally published June 2009, updated December 2012

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 UrlFetchApp and Browser Classes.
  • 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: Creating and deploying an App Engine service

  1. Make sure you have downloaded the App Engine SDK as described at here.
  2. Create a directory for the Google App Engine application called google-apps-script
  3. Create a file called backend.py and 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&params={"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&params={"value":11}&key=mySecretKey
    # Returns: {"value": 121}
    # To store a key value pair:
    #   http://hugo-test.appspot.com/rpc?action=Store&params={"key":"a","value":"xxx"}&key=mySecretKey
    # Returns: {"retCode": "Ok"}
    # To lookup value by key:
    #   http://hugo-test.appspot.com/rpc?action=Lookup&params={"key":"a"}&key=mySecretKey
    # Returns: {"retCode": "Ok", "value": "xxx"}
    
    import json
    import webapp2
    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(webapp2.RequestHandler):
      """ Allows the functions defined in the RPCMethods class to be RPCed."""
    
      def __init__(self, request=None, response=None):
         webapp2.RequestHandler.__init__(self, request, response)
         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(json.loads(params))
        self.response.out.write(json.dumps(result))
    
    class MyData(db.Model):
      keyString = db.StringProperty()
      valueString = db.StringProperty()
    
    
    app = webapp2.WSGIApplication([('/rpc', RPCHandler)],
                                  debug=True)
    
    
    
  4. Create a file called app.yaml and copy and paste this:
    application: google-apps-script-tutorial
    version: 1
    runtime: python27
    api_version: 1
    threadsafe: true
    
    handlers:
    - url: /.*
      script: backend.app
    
  5. Start a local web server with the following command:
    google_appengine/dev_appserver.py google-apps-script/
    
  6. Test this URL in your browser: http://localhost:8080/rpc?action=Echo&params={"example":"blah"}&key=mySecretKey. It should just return the params CGI argument.
  7. 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.
  8. Update the first line of the file app.yaml to the application id defined in the previous step
  9. Upload the application to Google App Engine by running the following command:
    appcfg.py update google-apps-script/
    
  10. Test this URL in your browser: http://<APPLICATION_ID>.appspot.com/rpc?action=Echo&params={"example":"blah"}&key=mySecretKey. It should just return the params CGI argument.
  11. 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

  1. Create a new Spreadsheet.
  2. Open the Script Editor ('Tools' menu, then 'Script editor...')
  3. 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 + "&params=" + 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());
    }
    
    
  4. 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).
  5. 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 the Echo 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.
  1. From the Script Editor, run the function square and 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.

  2. Now run the function lookupData and enter the key test. 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.

  3. Now run the function storeData. Enter the the key test.

    Enter the value "Hello World!".

    You should get a confirmation that the data was stored successfully in App Engine.

  4. Finally, let's run lookupData a second time and retrieve the data we stored in the previous step. Enter the key test.

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

Summary

Congratulations, you've completed this tutorial and have created a script that calls a Google App Engine service.

Send feedback about...

Apps Script
Apps Script