Runtah

JavaScript has long been the de facto choice for client-side web development, but lately its been catching on server-side as well. While we like to think that Apps Script has contributed to the trend, projects such as Mozillas Rhino and Node.js have also done a great deal to popularize the concept. As a result, developers have created a wealth of new open-source JavaScript libraries, and in this post well talk about how you can leverage them in your Apps Script projects.

Underscore
One library I wanted to use in my scripts was Underscore, which describes itself as "a utility-belt library for JavaScript." It provides a wealth of helper functions that make coding in JavaScript cleaner and more enjoyable. Take, for example, the simple situation where you want to log each value in a range.

// Using plain JavaScript.
for (var i = 0; i < values.length; i++) {
  for (var j = 0; j < values[i].length; j++) {
    Logger.log(values[i][j]);
  }
}

Although writing for loops like this is a common pattern, its a fair amount of typing and you need to keep track of counter variables that serve little purpose. Underscore provides an each() method that makes the process much simpler.

// Using Underscore.
_.each(values, function(row) {
  _.each(row, function(cell) {
    Logger.log(cell);
  });
});

Passing anonymous functions as parameters takes a little getting used to, but if youve worked with jQuery, the pattern feels familiar.

Underscore also has some great extensions, and Underscore.string provides some useful string manipulation features. My favorite is the ability to use sprintf() notation in JavaScript, which can simplify the process of building complex strings.

// Using plain JavaScript.
var message = "Hello, " + firstName + " " + lastName + ". Your wait time is " + wait + " minutes.";

// Using Underscore.string.
var message = _.sprintf("Hello, %s %s. Your wait time is %d minutes.", firstName, lastName, wait);

Integrating with Apps Script
The simplest way to include the Underscore library in a project would be to paste its source code directly into your script, but this would lead to a lot of duplication if you end up using it in multiple projects. Earlier this year, we released a feature in Apps Script called libraries that allows you to share scripts and include them in other projects. Packaging a JavaScript library like Underscore as an Apps Script library is possible, but requires some helper functions to work correctly.

When Underscore loads, it creates a global variable named "_" that you use to access its functionality. Apps Script specifically prevents the global scope of a library from interfering with the global scope of the script that includes it, so I built a helper function into the library to pass the variable around.

// In the library.
function load() {
  return _;
}

In my script that includes the library, I simply make a call to that function and use the result to set up my own "_" variable.

// In the script that includes the library.
var _ = Underscore.load();

To try my copy of the Underscore library in your own project, use the project key "MGwgKN2Th03tJ5OdmlzB8KPxhMjh3Sh48" and the code snippet above. You can browse the full source code here.

Using it with the HtmlService
Using the code above, I could easily include the library in my server-side Apps Script code, but I also wanted to use these functions client-side in my web app served by the HtmlService. To accomplish this, I created a copy of the Underscore source code, wrapped it in <script> tags, and stored them in Html files (instead of Script files). These snippet files could then be included in my web apps HtmlTemplates using the helper function below.

// In the library.
var FILES = [Underscore.js, Underscore.string.js, Init.js];
function include(output) {
  for (var i = 0; i < FILES.length; i++) {
    var file = FILES[i];
    output.append(
        HtmlService.createHtmlOutputFromFile(file).getContent());
  }
}

This function was called in the web apps HtmlTemplate using the simple code below.

<!-- In the web app that includes the library. -->
<html>
  <head>
    <? Underscore.include(output) ?>
  </head>
  ...

Other libraries
Integrating with Underscore was fairly easy, but trying the same approach with other open-source libraries may be a bit more complicated. Some libraries wont run correctly in the Apps Script environment if they rely on certain capabilities within the browser or Node.js runtime. Additionally, to be served by the HtmlService, the code must pass the Caja engines strict validation, which many popular libraries dont meet. In some cases, you may be able to manually patch the library to work around these issue, but this usually requires a deep understanding of how the library works.

We hope youre inspired to use Underscore and other open-source libraries in your own work. If you find a library that works great with Apps Script, share it with me on Google+ and Ill help get the word out.

Eric Koleda profile Eric is a Developer Programs Engineer based in NYC on the Google Apps Script team. Hes previously worked with the AdWords API and enterprise content management software.

Editor’s Note: This post written by Ferris Argyle. Ferris is a Sales Engineer with the Enterprise team at Google, and had written fewer than 200 lines of JavaScript before beginning this application. --Ryan Boyd

I started with Apps Script in the same way many of you probably did: writing extensions to spreadsheets. When it was made available in Sites, I wondered whether it could meet our needs for gathering roadmap input from our sales engineering and enterprise deployment teams.

Gathering Roadmap Data

At Google, teams like Enterprise Sales Engineering and Apps Deployment interact with customers and need to share product roadmap ideas to Product Managers. Product Managers use this input to iterate and make sound roadmap decisions. We needed to build a tool to support this requirement. Specifically, this application would be a tool used to gather roadmap input from enterprise sales engineering and deployment teams, providing a unified way of prioritizing customer requirements and supporting product management roadmap decisions. We also needed a way to share actual customer use cases from which these requirements originated.

The Solution

This required bringing together the capabilities of Google Forms, Spreadsheets and Moderator in a single application: form-based user input, dynamically generated structured lists, and ranking.

This sounds like a fairly typical online transaction processing (OLTP) application, and Apps Script provides rich and evolving UI services, including the ability to create grids, event handlers, and now a WYSIWYG GUI Builder; all we needed was a secure, scalable SQL database backend.

One of my geospatial colleagues had done some great work on a demo using a Fusion Tables backend, so I did a little digging and found this example of how to use the APIs in Apps Script (thank you, Fusion Tables Developer Relations).

Using the CRUD Wrappers

Full sample code for this app is available and includes a test harness, required global variables, additional CRUD wrappers, and authorization and Fusion REST calls. It has been published to the Script Gallery under the title "Using Fusion Tables with Apps Script."

The CRUD Wrappers:

/**
 * Read records
 * @param {string} tableId The Id of the Fusion Table in which the record will be created
 * @param {string} selectColumn The Fusion table columns which will returned by the read
 * @param {string} whereColumn The Fusion table column which will be searched to determine whether the record already exists
 * @param {string} whereValue The value to search for in the Fusion Table selectColumn; can be *
 * @return {string} An array containing the read records if no error; the bubbled return code from the Fusion query API if error
 */
function readRecords_(tableId, selectColumn, whereColumn, whereValue) {
  
  var query = ;
  var foundRecords = [];
  var returnVal = false;
  var tableList = [];
  var row = [];
  var columns = [];
  var rowObj = new Object();
  
  if (whereValue == *) {
    var query = SELECT +selectColumn+ FROM +tableId;
  } else {
    var query = SELECT +selectColumn+ FROM +tableId+
                 WHERE +whereColumn+ = +whereValue+;
  }
  
  var foundRecords = fusion_(get,query);

  if (typeof foundRecords == string && foundRecords.search(>> Error)>-1) 
  {
    returnVal = foundRecords.search;
  } else if (foundRecords.length > 1 ) {
    //first row is header, so use this to define columns array
    row = foundRecords[0];
    columns = [];
    for (var k = 0; k < row.length; k++) {
      columns[k] = row[k];
    }
    
    for (var i = 1; i < foundRecords.length; i++) {
      row = foundRecords[i];
      if( row.length > 0 ) {    
        //construct object with the row fields
        rowObj = {};
        for (var k = 0; k < row.length; k++) {
          rowObj[columns[k]] = row[k];
        }
        //start new array at zero to conform with javascript conventions
        tableList[i-1] = rowObj; 
      }
    }
    returnVal = tableList;
  }

  return returnVal;
}

Now all I needed were CRUD-type (Create, Read, Update, Delete) Apps Script wrappers for the Fusion Tables APIs, and I’d be in business. I started with wrappers which were specific to my application, and then generalized them to make them more re-usable. I’ve provided examples above so you can get a sense of how simple they are to implement.

The result is a dynamically scalable base layer for OLTP applications with the added benefit of powerful web-based visualization, particularly for geospatial data, and without the traditional overhead of managing tablespaces.

I’m a Fusion tables beginner, so I can’t wait to see what you can build with Apps Script and Fusion Tables. You can get started here: Importing data into Fusion Tables, and Writing a Fusion Tables API Application.

Tips:

• Fusion Tables is protected by OAuth.This means that you need to authorize your script to access your tables. The authorization code uses “anonymous” keys and secrets: this does NOT mean that your tables are available anonymously.
• Some assumptions were made in the wrappers which you may wish to change to better match your use case:
  • key values are unique in a table
  • update automatically adds a record if it’s not already there, and automatically removes duplicates
• Characters such as apostrophes in the data fields will be interpreted as quotation marks and cause SQL errors: you’ll need to escape these to avoid issues.
About”) and column names to construct your queries

Ferris Argyle Ferris is a Sales Engineer with the Enterprise team at Google.

Ive heard about the hand signals that teachers all over use, and I absolutely loved the idea, but I wanted to do something a bit different with my class.  I thought back to when I was in elementary school and one of my teachers taught us the pledge in sign language.  I have a second cousin who is deaf and speaks sign language, and I thought it was the coolest thing to learn something that I could use to "communicate" with him. 

So... it hit me!  I decided to teach my class sign language!

We learned so many different words, and my students constantly asked if we could go to the website Signing Savvy to learn more!  It totally revolutionized my classroom.  Suddenly, everything was sign language.  No longer did students call out "Is it time to erase the board yet?" Instead, they used sign language!

Click the pictures below to see an animated video to learn how to sign each of the words, then download the poster at the bottom of the page to post in your classroom as a reminder to your students!









For the next two words, we combine two different signs.





Heres a free accompanying poster to hang in your classroom!

To save the file, just click the image to get to the higher quality picture, then right click (or Mac: Ctrl + Click) to save the image onto your computer!