Trick and Tips

Editor’s Note: Guest author Bruce McPherson is a contributor to the Excel Liberation website and blog. -- Eric Koleda

If you are new to Google Apps Script and the JavaScript programming language, migrating legacy automation code written in Microsofts Visual Basic for Applications (VBA) can be a daunting task. This blog post describes a Google Apps Script library which mimics the behavior and calling structure of common VBA functions, allowing you to more easily convert your existing applications.

Retaining compatibility

If you are planning to continue to use VBA, you can minimize both the work involved in maintaining the same capability in both platforms, and in porting from one to the other, by preserving backwards compatibility with VBA. This means breaking a few JavaScript conventions, but the result is worth it.

For example, JavaScript variables are normally written in lowerCamelCase, with classes being in UpperCamelCase. VBA is not case sensitive, and uses hungarian notation by convention, except for the built-in functions, which have a capitalized first letter. Since the objective here is to minimize change, I have decided to retain this capitalization for VBA functions replacements (for example CStr(), Mid() etc. ).

In VBA, indices (normally) start at 1, while in JavaScript they start at 0. Since these functions are to minimize change in application written in VBA, they also start at 1. For example Mid(x, 1, 2) means the first 2 characters of
string x in both VBA and JavaScript versions.

Enumeration of collections

JavaScript does not have a collection object. The vEquivalents library provides an implementation of a collection class so that continuity for migrated code that relies on the collection can be maintained. But how to enumerate through that collection? There are a number of ways, but the forEach() method of the collection most closely resembles the For Each member in collection approach VBA developers are familiar with. The syntax may seem a little fiddly at first, since it passes the code you want executed against each member of the collection as an anonymous function.

var coll = new collection();
// by index
for (var i=1; i <= coll.count() ;i++) {
  DebugPrint (coll.item(i));
}
// by key
for (k in coll.keys()) {
    DebugPrint (coll.item(k));
}
// forEach 
coll.forEach(
  function (item, index) {
    DebugPrint (item, index);
  }
);

Including vEquivalents in your Google Apps Script Project

With the great new libraries functionality, you can now include these scripts in your project by using the project key "MEQ3tE5y5_cTOAgUbUKSIAiz3TLx7pV4j", or you can make a copy of the scripts directly to include in your own project. You will find a selection of other scripts in the library, but the VBA equivalents are all in the module vEquivalents. Note that as you include external libraries in your project (see here for how), you need to prefix the functions with the library identifier (for example mcpher.CStr())

Examples

You can access the documentation here, and you will see that most of the common VBA functions are included. Some examples are

var x = Trim(s);
var x = Len(s);
var a = Split(s);
var x = Instr(1, s1, s2);
var d = DateSerial(y, m, d);
MsgBox(s);
var x = InputBox(s);
DebugAssert (b, s);
var w = ActiveSheet();

Going beyond the built-in VBA functions

Using the same approach, I have converted many other VBA utility classes and procedures built over time and added them to this library. This means that the implementation of something on either platform not only looks the same, but can be accomplished in hours or even minutes. For example, on my blog I publish a daily API, implemented in both VBA and Apps Script (both versions even use ScriptDB for the same parameter data). Heres a recent one.

function testUkPostcodes() {
  mcpher.generalDataSetQuery ("uk postcodes", "uk postcodes", "postcode");
}

Public Sub testUkPostcodes()
  generalDataSetQuery "uk postcodes", "uk postcodes", "postcode"
End Sub

You can find more projects that have been migrated this way here.

Bruce McPherson   profile | twitter Bruce McPherson is a contributor to Excel Liberation website and blog, and advocate for open data.

Editors note: this announcement is cross-posted from the Google Ads Developer Blog, which caters to AdWords, AdSense, DoubleClick and AdMob developers. We hope you enjoy this latest addition to Google Apps Script — Ryan Boyd

Starting today, the AdSense Management API is available as part of AdSense Services in Google Apps Script. This means that you’ll be able to do things like:

• Create AdSense performance reports for your AdSense accounts in a Google spreadsheet
• Create a chart based on your AdSense reporting data and display it in a Google Spreadsheet
• Embed your scripts in a Google Sites page, for instance to import a chart
• Use triggers to schedule the execution of your scripts, for instance to periodically update the chart imported in the Google Sites page

Accessing the API from Google Apps Scripts is very easy. The following snippet of code shows how to generate a report and populate columns of a spreadsheet with the data retrieved:

function generateReport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(Reports);
  var startDate = Browser.inputBox(
      "Enter a start date (format: yyyy-mm-dd)");
  var endDate = Browser.inputBox(
      "Enter an end date (format: yyyy-mm-dd)");
  var args = {
    metric: [PAGE_VIEWS, AD_REQUESTS, MATCHED_AD_REQUESTS,
               INDIVIDUAL_AD_IMPRESSIONS],
    dimension: [MONTH]};
  var report = AdSense.Reports.generate(startDate, endDate, args).getRows();
  for (var i=0; i<report.length; i++) {
    var row = report[i];
    sheet.getRange(A + String(i+2)).setValue(row[0]);
    sheet.getRange(B + String(i+2)).setValue(row[1]);
    sheet.getRange(C + String(i+2)).setValue(row[2]);
    sheet.getRange(D + String(i+2)).setValue(row[3]);
    sheet.getRange(E + String(i+2)).setValue(row[4]);
  }    
}

function generateLineChart() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var startDate = Browser.inputBox(
      "Enter a start date (format: yyyy-mm-dd)");
  var endDate = Browser.inputBox(
      "Enter an end date (format: yyyy-mm-dd)");
  var adClientId = Browser.inputBox("Enter an ad client id");
  var args = {
    filter: [AD_CLIENT_ID== + adClientId],
    metric: [PAGE_VIEWS, AD_REQUESTS, MATCHED_AD_REQUESTS,
               INDIVIDUAL_AD_IMPRESSIONS],
    dimension: [MONTH]};
  var report = AdSense.Reports.generate(startDate, endDate, args).getRows();
  var data = Charts.newDataTable()
      .addColumn(Charts.ColumnType.STRING, "Month")
      .addColumn(Charts.ColumnType.NUMBER, "Page views")
      .addColumn(Charts.ColumnType.NUMBER, "Ad requests")
      .addColumn(Charts.ColumnType.NUMBER, "Matched ad requests")
      .addColumn(Charts.ColumnType.NUMBER, "Individual ad impressions");
  
  // Convert the metrics to numeric values.
  for (var i=0; i<report.length; i++) {
    var row = report[i];
    data.addRow([row[0],parseInt(row[1]),parseInt(row[2]),
        parseInt(row[3]),parseInt(row[4])]);  
  }
  data.build();
  
  var chart = Charts.newLineChart()
      .setDataTable(data)
      .setTitle("Performances per Month")
      .build();
  
  var app = UiApp.createApplication().setTitle("Performances");
  var panel = app.createVerticalPanel()
      .setHeight(350)
      .setWidth(700);
  panel.add(chart);
  app.add(panel);
  doc.show(app); 
}

Silvano Luciani   profile Silvano Luciani joined Googles London office in 2011 to make the AdSense API developers happier people. Before that, he has worked in Finland, Italy, Spain and the UK, writing web based configuration management tools for ISPs, social networks, web based training materials, e-commerce apps and more. He has recently discovered that he loves charts, and has finally started to play the drums in the London’s office music room. If you can call what he does "playing the drums".

Posted by Laurent Tu, Google Apps Team

Want to weigh in on this topic? Discuss on Buzz

We have released version 1.9 of the .NET Library for Google Data APIs and it is available for download.

This version adds the following new features:

• support for 3-legged OAuth
• two new sample applications: BookThemAll (mashup of Calendar and Provisioning APIs) and UnshareProfiles (showcasing a new feature of the Google Apps Profiles API)
• updates to the Content for Shopping API to implement 20+ new item attributes
• support for new yt:rating system and Access Control settings to the YouTube API

This new version also removes the client library for the deprecated Google Base API and fixes 20 bugs.

For more details, please check the Release Notes and remember to file feature requests or bugs in the project issue tracker.

Claudio Cherubino profile | twitter | blog Claudio is a Developer Programs Engineer working on Google Apps APIs and the Google Apps Marketplace. Prior to Google, he worked as software developer, technology evangelist, community manager, consultant, technical translator and has contributed to many open-source projects, including MySQL, PHP, Wordpress, Songbird and Project Voldemort.

1. Experience starting a company
2. Domain experience and an understanding of the market
3. Balance and different experience than your own
4. Passion about the company vision

Posted by Don Dodge

Want to weigh in on this topic? Discuss on Buzz

Don Dodge profile | twitter | blog | events Don Dodge is a Developer Advocate at Google helping developers build new applications on Google platforms and technologies. Prior to joining Google Don was a startup evangelist at Microsoft. He is also a veteran of five start-ups including Forte Software, AltaVista, Napster, Bowstreet, and Groove Networks.

"This sentence is false" 

"There are contradictions in the laws of physics – We have Einstein’s relativity that explains planetary motion, gravity, all these big things. And we have quantum mechanics that explains subatomic energy and matter, all those tiny things. Unfortunately, they contradict each other in the middle. Because the one requires physical reality to be absolutely smooth and continuous, while the other requires quantum bumps, which are profoundly non-smooth and discontinuous. Right now the laws of physics are both true and false (and by false, I mean true.)"

"A newborn is young" = truth value of 100%.
"A 20 year old is young" = truth value of 80%

Individuals and interactions over processes and tools
Working software over comprehensive documentation
Customer collaboration over contract negotiation
Responding to change over following a plan

The Drive SDK allows apps to store all kinds of files and file-like items in user-managed cloud storage. Files can be standard document formats like PDF, images, video & audio clips, or even your proprietary application data files. Storing files in Drive makes it easy for users to organize, search, and securely share them with their coworkers, friends, or family.

However, some applications work better with document or application data stored in a database. For example, let’s imagine a modern, web-based project management tool that provides lots of awesome features via data objects that are assembled dynamically at runtime for presentation to the user. In such cases, there is no single file to store all the data that comprises the project -- though there is of course a named “file” item that users will want to save and list in their Drive. Drive applications like this can create file-like entries called shortcuts that allow users to organize, access, and share items as if they were files stored in Drive.

Creating Shortcuts

Creating a shortcut is not much different than creating a regular file. Just set the MIME type to application/vnd.google-apps.drive-sdk, and make sure you don’t upload any actual content in the call to files.insert. Here’s an example of creating a shortcut using Python:

shortcut = {
    title: My project plan,
    mimetype: application/vnd.google-apps.drive-sdk,
    description: Project plan for the launch of our new product!
  }
  file = service.files().insert(body=shortcut).execute()
  key = file[id] # Key to use when re-opening shortcuts

For examples in other supported languages, see the Drive SDK documentation.

Opening shortcuts in Drive always launches the application that created them. Shortcuts can even be synchronized to the desktop. Opening a shortcut from the desktop will launch the application that created it in a new browser tab.

Sharing and Security

Shortcuts require special consideration when it comes to sharing and security. Since the actual content is not stored in Drive, applications are responsible for enforcing permissions and ensuring that only authorized users are allowed to read or update content. Follow these best practices when working with shortcuts:

• Always call files.get with the current user’s access token to verify the user has access to the content.
• Restrict user actions based on the userPermission property of the file and disable saves if the user only has reader or commenter roles.

Honoring permissions not only ensures the protection of user data, but also provides a consistent user experience and added value to Drive applications. Users should be able to safely share an item in Drive without worrying about the particular implementation details of the application that created it.

If you have any questions about shortcuts, don’t hesitate to ask us on our Stack Overflow tag, google-drive-sdk

Steven Bazyl   profile | twitter | events Steve is a Developer Advocate for Google Drive, Google Apps, and the Google Apps Marketplace. He enjoys helping developers find ways to integrate their apps and bring added value to users.

Since we released version 2 of the Google Drive SDK at Google I/O, we’ve been quietly updating the DrEdit sample application to use the new API. As part of the update, the UI for DrEdit has been rewritten to use AngularJS, a modern web application toolset developed by Google and used in apps at DoubleClick. You might be wondering -- why go through the trouble of rewriting the UI for a basic sample app just to show off some new API features? Turns out it was more of a happy coincidence, but a valuable one and great learning experience!

Practice what you preach

I had the pleasure of co-presenting a session on building great apps for Google Drive, and a big focus of the talk was on all the little things that go into making an app intuitive and user-friendly. This is particularly important for Google Drive, where many users are already familiar with the built-in apps like Docs, Presentations, and Spreadsheets.

The first version of DrEdit was a good demo app, but didn’t follow all of our recommendations. I didn’t want to tell developers all the things they should be doing without having tried them myself. I decided to write a separate sample for the talk and needed a solid base to build on. It was the perfect opportunity to learn a new tool!

HTML & Javascript, only smarter

Angular doesn’t aim to abstract away HTML, Javascript & CSS. Rather, it enhances HTML to make building dynamic apps easier. One benefit, besides a nice short learning curve, is the positive interaction with other tools. To give the app some structure, I used Bootstrap. For example, the HTML for displaying the authenticated user’s info and a small dropdown to link to their profile in the navigation bar only required a few minor changes from typical Bootstrap usage (shown in bold) to wire up to a controller.

<ul class="nav pull-right" ng-controller="UserCtrl">
  <li class="dropdown">
    <a class="dropdown-toggle" data-toggle="dropdown" href="#">
    {{user.email}}
    </a>
    <ul class="dropdown-menu">
      <li><a href="{{user.link}}" target="_blank">Profile</a></li>
    </ul>
  </li>
</ul>

Even models are plain javascript objects. Anything reachable through a scope (the binding between a view and controller) is considered part of the model. These can be primitives, hashes, or objects. No need to extend a base class or access properties through special properties. Rather than use change listeners that require special instrumentation, Angular uses dirty checking to detect model changes and update views.

The one catch with this approach is it requires any changes to the model to be made inside the scope of a scope.$apply(fn) call. In most cases, this is done automatically. When working with external libraries or raw XMLHttpRequests that can fire asynchronous callbacks, calling $apply yourself is necessary to make sure mutations are tracked correctly.

Speaking of asynchronous tasks…

Promises, Promises

No, I’m not talking about the hit song by 80’s band Naked Eyes, rather Angular’s $q service based on one of the proposed CommonJS Promises APIs. If you’re already familiar with JQuery’s deferred object or any of the other related implementations, this is familiar territory. If not, time to learn. Working with deferred objects can be a lot easier than the traditional callback approach. You can compose async tasks either serially or in parallel, chain callbacks, and return deferred objects from functions like normal results.

Where this mostly comes into play is Angular’s $http service. If you’ve used jQuery, you’ll find it similar to jQuery.ajax() & the jqXHR result. It is based on the deferred/promises API and also ensures callbacks are executed correctly inside $apply for safe & efficient model mutations. This combination makes it easy to work with remote services in Angular.

Room for improvement

Trying to learn some new frameworks while preparing for Google I/O and helping developers to launch apps on our updated API all within a few weeks was a lot to take on. A few corners were cut and there are a few things I’d like to revisit when time permits:

• Tests! AngularJS boasts testability as one of its key features and leverages dependency injection throughout to help keep things simple and testable. Since this was originally intended as a live demo instead of a reference app, I cut a few corners here. Yeah, I know better than that...
• Rethink how ACE is used. In most cases it’s easy to figure out if something should be a controller, directive, filter, or service. But trying to pigeonhole libraries like ACE into one of those is daunting. Out of expediency, I chose to hide ACE behind a service, but it feels like it belongs in a directive. It would be nice to be able to declare in HTML:
  

  <editor content=”myModel.text”/>

  and have that sync with the model just like any other input in Angular. I started down that route, but correctly wiring up ACE to do that was more effort than it seemed worth at the time. This resulted in some other warts with how the app’s routes & views are structured.
• Talking to the backend servers. Not so much an issue with Angular, but rather with a late decision to replace the DrEdit UI. The original goal was a separate app. Once we decided to build on the previous sample, I didn’t want to make unnecessary changes to the server side code that was already written. This led to implementing some of the new features in less than ideal ways. For example, the editor can not independently save metadata from the document content when the file is renamed or starred but the content left untouched. A minor inefficiency, but something that could have been done better.

I know I’ve only scratched the surface and have a lot more to learn. Even so, it was incredibly fun diving head first into AngularJS, and I highly recommend considering it if you’re dissatisfied with your current framework or just want to learn something new!

Steven Bazyl   profile | twitter | events Steve is a Developer Advocate for Google Drive, Google Apps, and the Google Apps Marketplace. He enjoys helping developers find ways to integrate their apps and bring added value to users.

ACL (Access Control List) entries control who can access Google Docs resources. This allows more specific control over resource privacy or permissions.

Many types of applications need to grant document access for several users at once. As an example: when a new user is added to a project in the Manymoon project management application, every user on the project needs to be granted access to all attached Google docs. If there are 10 users on the project and 10 shared documents, this means the app would typically need to perform 100 HTTP requests -- a lot of overhead. With batching of ACL requests, the application can reduce the number of requests to one per document, resulting in a 10x savings.

Before Batching

A typical ACL entry for a single user is created by making an HTTP POST to the ACL link provided with each resource entry. The POST body looks something like this:

<entry xmlns="http://www.w3.org/2005/Atom"
       xmlns:gAcl=http://schemas.google.com/acl/2007>
    <category scheme=http://schemas.google.com/g/2005#kind
              term=http://schemas.google.com/acl/2007#accessRule/>
    <gAcl:role value=writer/>
    <gAcl:scope type=user value=new_writer@example.com/>
</entry>

To achieve the same thing using the Python client library, use the following code:

from gdata.acl.data import AclScope, AclRole
from gdata.docs.data import AclEntry

acl = AclEntry(
    scope = AclScope(value=user@example.com, type=user),
    role = AclRole(value=writer)
)

With Batching

Instead of submitting the requests separately, multiple ACL operations for a resource can be combined into a single batch request. This is done by POSTing a feed of ACL entries. Each ACL entry in the feed must have a special batch:operation element, describing the type of operation to perform on the ACL entry. Valid operations are query, insert, update, and delete.

<feed xmlns="http://www.w3.org/2005/Atom"
      xmlns:gAcl=http://schemas.google.com/acl/2007
      xmlns:batch=http://schemas.google.com/gdata/batch>
  <category scheme=http://schemas.google.com/g/2005#kind
            term=http://schemas.google.com/acl/2007#accessRule/>
    <entry>
        <category scheme=http://schemas.google.com/g/2005#kind
                  term=http://schemas.google.com/acl/2007#accessRule/>
        <gAcl:role value=reader/>
        <gAcl:scope type=domain value=example.com/>
        <batch:operation type=insert/>
    </entry>
    <entry>
        <category scheme=http://schemas.google.com/g/2005#kind
                  term=http://schemas.google.com/acl/2007#accessRule/>         
        <id>https://docs.google.com/feeds/default/private/full/document%3Adocument_id/acl/user%3Aold_writer%40example.com</id>
        <gAcl:role value=writer/>
        <gAcl:scope type=user value=new_writer@example.com/>
        <batch:operation type=update/>
    </entry>
</feed>

The following code represents the same operation in the Python client library:

from gdata.data import BatchOperation
from gdata.acl.data import AclScope, AclRole
from gdata.docs.data import AclEntry

acl1 = AclEntry(
    scope=AclScope(value=example.com, type=domain),
    role=AclRole(value=reader),
    batch_operation=BatchOperation(type=insert)
)

acl2 = client.get_acl_entry_by_self_link(
    (https://docs.google.com/feeds/default/private/full/
     document%3Adocument_id/acl/user%3Aold_writer%40example.com))
acl2.scope = AclScope(value=new_writer@example.com, type=user)
acl2.role = AclRole(value=writer)
acl2.batch_operation = BatchOperation(type=update)

entries = [acl1, acl2]

The feed of these entries can now be submitted together to apply to a resource:

results = client.batch_process_acl_entries(resource, entries)

The return value is an AclFeed, with a list of AclEntry elements for each operation, the status of which can be checked individually:

for result in results.entry:
    print entry.title.text, entry.batch_status.code

The examples shown here are using the raw protocol or the Python client library. The Java client library also supports batch operations on ACL entries.

For more information on how to use batch operations when managing ACLs, see the Google Documents List API documentation, and the Google Data APIs batch protocol reference guide. You can also find assistance in the Google Documents List API forum.

Ali Afshar profile | twitter Ali is a Developer Programs engineer at Google, working on Google Docs and the Shopping APIs which help shopping-based applications upload and search shopping content. As an eternal open source advocate, he contributes to a number of open source applications, and is the author of the PIDA Python IDE. Once an intensive care physician, he has a special interest in all aspects of technology for healthcare.

From nations choosing presidents to offices selecting which coffee to brew, we often find ourselves involved in election systems designed to choose the best option. This spring my alma maters solar vehicle team, CalSol, needed to elect new leaders. Our previous system was painfully slow, involved "raising hands" in a room, and excluded any team members who could not attend a specific meeting. I set out to solve these problems and the result was an easy method for running fair elections in a matter of minutes.

I was able to build the system completely on Google products and technologies:

• Google Forms: Allows members to submit their votes from anywhere.
• Google Spreadsheets: Makes it easy to audit the votes and configure the system.
• Google Apps Script: Simple way to access the results and determine the winner.

I used a lesser known voting system called instant-runoff voting (IRV), or the alternative vote, which asks voters to rank candidates rather than cast a single vote. These votes, along with a secret voting key which I provided to each member, are recorded with a Google Form that automatically populates a spreadsheet. The code in Apps Script looks through the spreadsheet to count the votes while ensuring that each voting key is only used once. The secret keys not only prevent voters from casting multiple votes, but they also allow voters to change their vote by submitting the form again.

Below is a simplified snippet of code that shows the general process used to calculate the winner.

/* Some code omitted for clarity */

/* candidates is a list of names (strings) */
var candidates = get_all_candidates(results_range);

/* votes is an object mapping candidate names -> number of votes */
var votes = get_votes(results_range, candidates, keys_range, valid_keys);

/* winner is candidate name (string) or null */
var winner = get_winner(votes, candidates);

while (winner == null) {
  /* Modify candidates to only include remaining candidates */
  get_remaining_candidates(votes, candidates);
  if (candidates.length == 0) {
    Browser.msgBox("Tie");
    return;
  }
  votes = get_votes(results_range, candidates, keys_range, valid_keys);
  winner = get_winner(votes, candidates);
}
Browser.msgBox("Winner: " + winner);

I learned that putting a little effort into Apps Script can make people happy and save a lot of time. The team feedback was outstanding. One CalSol member said the process was an "Excellent, clean, and professional voting process. This should become a standard [for the team]." I was elated when I was able to close the polls during a meeting and announce the winners of twelve independent elections in just a matter of minutes.

If you like, you can watch a video demonstrating how to create and run your own election using this script:

Try the script yourself to make sure your coffee preferences are heard!

Chris Cartland profile | GitHub Chris is a Developer Programs Engineer based in Mountain View on the Google+ team. He previously worked on solar vehicles at UC Berkeley and wants developers to write software that makes our lives better. In his spare time he likes to play soccer and throw the ball in after doing a front handspring.

With the arrival of the new Google Drive API v2, we are deprecating the Google Documents List API v3. We are confident that the Google Drive API covers all the functionality of the Documents List API, in addition to adding many improvements, including Drive UI Integration, a finer grained security model, and a better client library experience.

What does this mean for your app?

The Documents List API v3 will remain in action for more than a year, as per our deprecation policy, so there’s no rush, but we encourage you to migrate your code to the new platform. Documentation is available with samples in multiple languages and a migration guide outlining some of the major transition points.

If you have any questions or issues, please ask them on StackOverflow.com, where our team is waiting to hear from you.

Ali Afshar profile | twitter Tech Lead, Google Drive Developer Relations. As an eternal open source advocate, he contributes to a number of open source applications, and is the author of the PIDA Python IDE. Once an intensive care physician, he has a special interest in all aspects of technology for healthcare

Sessions

Google Apps Marketplace:

• Launch and Grow your Business App on the Google Apps Marketplace provided an intro to the Apps Marketplace, but most of the session was third-party developers telling the story of their businesses, demoing their integrations and providing guidance for other developers looking for success on the Marketplace. Teaser: 30% free->paid conversion rates from GQueues on the Google Apps Marketplace.
• Apps Marketplace: Best Practices and Integrations covered a wealth of best practices for business app development and Google Apps integrations based on experience working with hundreds of developers building applications for the Google Apps Marketplace.

Google Apps Script:

• Enterprise Workflow with Apps Script showed how Google Apps Script can be used to build complex workflows using simple server-side JavaScript code. The speakers built on several examples for document publishing approval, showing lots of code for how it’s done.
• Developing Apps, Add-Ins and More stepped through building Add-Ins with deep integration into the Google Apps UI and full applications. The team announced the Apps Script GUI Builder to drag and drop UI components and full Apps Script APIs for Gmail and Google Docs.

Application APIs:

• Google Tasks API announced the brand-new API to interact with a user’s Google Tasks. Several third-party developers demonstrated how they integrated tasks with their project management and CRM apps.
• Using the Google Docs APIs to Store All your Information in the Cloud gave a very brief overview of the Documents List API followed by a deep-dive into gcategorically, an App Engine + Python sample app for uploading documents to Google Docs. Best practices for developing integrations with Google Doc were also covered. This session is very useful for Apps Marketplace developers, especially because of the new ability to upload all file types to all types of Google accounts.

Solutions and Administration:

• Developing Innovative Custom Business Solutions with Google Apps covered how web solution providers are driving us towards the goal of 100% web. Included many real-world examples from a variety of companies who are extending Google Apps using Apps Script, Google Sites, gadgets, Data APIs, App Engine, GWT and more.
• Compliance and Security in the Cloud talked about the suite of APIs and tools available for Google Apps customers to handle policy compliance, audit, incident response and more. Very helpful session for IT administrators, CTOs and CIOs using Google Apps, with much of the session diving into several examples using real-world use cases.

Developer Sandbox

Parties and Fun

Ryan Boyd profile | twitter | events Ryan is a Developer Advocate on the Google Apps Marketplace team, helping businesses build applications integrated into Google Apps. Wearing both engineering and business development hats, youll find Ryan writing code and helping businesses get to market with integrated features.

• Gmail Snooze with Apps Script
• Content Approval Workflow
• Vacation calendar management
• Helpdesk Workflow
• Time Booking

Saurabh Gupta profile | twitter | blog Saurabh is a Developer Programs Engineer at Google. He works closely with Google Apps Script developers to help them extend Google Apps. Over the last 10 years, he has worked in the financial services industry in different roles. His current mission is to bring automation and collaboration to Google Apps users.

Posted by Vic Fryzel, Google Spreadsheets API Team

Want to weigh in on this topic? Discuss on Buzz

Way back in the dawn of time, before I joined Google — OK, fine, two months ago — I was a video-game designer. Occasionally, I had to come up with names for people or places. And Im no good at naming things.

So once, instead of manually naming hundreds of towns in a (fictitious) foreign country, I weaseled my way out of creativity by writing a ridiculous set of custom spreadsheet functions. My spreadsheet analyzed a list of real placenames from a similar country, then spit out plausible fake names with the same lexical structure.

It worked — but I was pushing spreadsheet functions so far that the “code” (if you can call it that) became difficult to maintain. At the same time, the reason I used a spreadsheet in the first place was so I could lean on the analytical power of pivot tables.

That’s what made Google Apps Script perfect for revamping the project. With Apps Script, I can still use pivot tables, then do the heavy lifting in JavaScript and package everything up as a tidy web app. I call it Name Generator … because I’m terrible at naming software, too.

Now, before you say, “There’s no way I’d call my daughter Harliance,” remember that the goal wasn’t to produce real names. The goal was to produce names that were good enough for a video game. Perhaps Harliance is a cyborg woman of negotiable virtue in dystopian future-America? You should probably pick your daughter’s name the old-fashioned way.

So let’s look at a few of the techniques that NameGen uses.

1. We start out in Google Sheets, first dropping a list of real names into column A, then slicing it into overlapping three-letter segments using the formula =MID($A2,COLUMN(B2)-1,3) (that’s the version of the formula you’d use in cell B2; from there, just copy and paste the formula across the rest of the sheet and the cell references will update accordingly). Here’s a sample of one of the spreadsheets so you can see how the data is set up.

2. We then create a pivot table for each column in that first sheet, just summarizing the column by COUNTA (the number of times each segment occurs). For example, since Lakisha, Nakia, and Nakisha (from our list of real names) share “aki” as letters 2 through 4, the pivot table for Segment 2 shows “aki: 3.”

The plan is that NameGen will randomly pick one of the starting three-letter segments, then look at the last two letters of its selection so that it can find an overlapping segment from the next column. The script then uses the pivot-table statistics to weight its selections toward more common segments. It continues until a segment ends in a blank. This diagram shows how it might build the name Calina:

3. This is where Apps Script takes over. Just once per source list of names, we run the utility function below (shown slightly simplified) to convert the spreadsheet data to a more useful format and store it in ScriptDb. The script can then pull the data from ScriptDb in about 0.5s, versus about 5s to read directly from the spreadsheet. Note that we’re using Script Properties to store the spreadsheet ID rather than cluttering up the code with extra variables.

function updateDb() {
  var language = americanFemale;

  // Look up the spreadsheet ID in Script Properties, then grab its sheets. 
  var ssId = ScriptProperties.getProperty(language);
  var sheets = SpreadsheetApp.openById(ssId).getSheets();
  var dictSize = sheets[0].getLastRow() - 1;
  var segment = {};

  // Transform each sheet into the segment object we want later.
  for (var i = 0; i < sheets.length; i++) {    

    // Retrieve the list of real names (first loop) or a pivot table.
    if (i === 0) {
      segment.data = sheets[0].getRange(A:A).getValues();
    } else {
      segment.data = sheets[i].getDataRange().getValues();
    }
    
    // Store other properties so we can retrieve the right record later.
    segment.index = i;
    segment.language = language;
    segment.size = dictSize;

    // Save the object as a ScriptDb record, then start the loop again.
    ScriptDb.getMyDb().save(segment);
  }
}

4. Now, every time the app runs, it jumps straight to the generateNames() function shown below (again slightly simplified). After it queries the database, it’s straight JavaScript — but one Apps Script–specific trick you’ll notice is that we assemble the data into an array using segments[current.index] = current.data rather than segments.push(current.data). Because ScriptDb returns the records in an unpredictable order, we gave our objects an index property to store the correct order.

function generateNames(language, numNames) {
  
  // Query the database to find all results for this language.
  var results = ScriptDb.getMyDb().query({language: language});
  var current = {};
  var segments = [];
  
  // Assemble the DB records into an array so we can pass it around.
  while (results.hasNext()) {
    current = results.next();
    segments[current.index] = current.data;
  }
  
  var names = [];
  var segment = ;
  
  for (var i = 0; i < numNames; i++) {
    var name = ;
    
    // For each requested name, pick one segment, making
    // sure it overlaps with the previous two letters.    
    for (var j = 1; j < segments.length; j++) {
      segment = randomSegment(segments[j], name);
      name = name.slice(0, name.length - 2);
      name += segment;
      
      // If the segment wasnt full length (end of a name), done!
      if (segment.length < 3) {
        break;
      }
    }
    
    names.push(name);
  }
  
  return names;
}

I haven’t explained the randomSegment() function, but you can probably guess at how it works based on the description above. Still, if you want to dig in further, you can view the full source code here.

5. The only remaining step is to expose the results to the world through a web app. Apps Script provides several ways of doing this; I used Html Service, but didn’t require any of the advanced features. Here’s how little HTML we need to turn NameGen into a functional app:

<html>
  <body>
    <script>
      function sendRequest() {
        var language = document.getElementById(language).value;
        var numNames = document.getElementById(numNames).value;
        google.script.run.withSuccessHandler(updateField).
            generateNames(language, numNames);
      }
    
      function updateField(names) {
        var output = "";
        for (var i = 0; i < names.length; i++) {
          output += (names[i] + <br/>);
        }
        document.getElementById(resultsBox).innerHTML = output;
      }
    </script>
    <select id="language">
      <option value="americanFemale">American Females</option>
      <option value="americanMale">American Males</option>
      <option value="american">American Towns</option>
      <option value="british">British Towns</option>
      <option value="french">French Towns</option>
      <option value="irish">Irish Towns</option>
      <option value="italian">Italian Towns</option>
      <option value="spanish">Spanish Towns</option>
    </select>
    <select id="numNames">
      <option value=1>1</option>
      <option value=10 selected>10</option>
      <option value=100>100</option>
      <option value=1000>1000</option>
    </select>
    <input id="generateButton" type="button"
     value="Generate" onclick="sendRequest()">
    <div id="resultsBox">
    </div>
  </body>
</html>

And presto chango, you have a web app that harnesses the tremendous power of Google’s infrastructure … to produce names that could only ever exist in a parallel universe. It’s like Adriano Celentano’s "Prisencolinensinainciusol" — a convincing rendition of an American pop song, unless you actually speak English, in which case it’s total gibberish.

Dan Lazin   profile | twitter Dan is a technical writer on the Developer Relations team for Google Apps Script. Before joining Google, he worked as video-game designer and newspaper reporter. He has bicycled through 17 countries.