Scripts in Google Docs

Forms Service / Scripts in Google Forms

Drive Service

Faster HtmlService

Improved Authorization Flow and API Console Integration

As you can see, we’ve been working hard to improve Apps Script for you. We hope you enjoy the new features!

Saurabh Gupta   profile | twitter | blog As the product manager for Google Apps Script, Saurabh is responsible for Apps Script’s overall vision and direction.

Conclusion

import gdata.apps.emailsettings.client

import gdata.contacts.client

# replace these values with yours



CONSUMER_KEY = mydomain.com

CONSUMER_SECRET = my_consumer_secret

company_name = ACME Inc.

admin_username = admin

# request a 2-legged OAuth token

requestor_id = admin_username + @ + CONSUMER_KEY

two_legged_oauth_token = gdata.gauth.TwoLeggedOAuthHmacToken(

  CONSUMER_KEY, CONSUMER_SECRET, requestor_id)



# Email Settings API client

email_settings_client = gdata.apps.emailsettings.client.EmailSettingsClient(

  domain=CONSUMER_KEY)

email_settings_client.auth_token = two_legged_oauth_token



# User Profiles API client

profiles_client = gdata.contacts.client.ContactsClient(

  domain=CONSUMER_KEY)

profiles_client.auth_token = two_legged_oauth_token

# helper class used to build signatures

class SignatureBuilder(object):

def HtmlSignature(self):

  signature = %s % self.name

  if self.occupation:

    signature += %s % self.occupation

  if self.company:

    signature += %s % self.company

  signature += Email: <a href=mailto:%s>%s</a> - Phone: %s % (

      self.email, self.email, self.phone_number)

  return signature



def __init__(

    self, name, company=, occupation=, email=, phone_number=):

  self.name = name

  self.company = company

  self.occupation = occupation

  self.email = email

  self.phone_number = phone_number

# get all user profiles for the domain

profiles = []

feed_uri = profiles_client.GetFeedUri(profiles)

while feed_uri:

  feed = profiles_client.GetProfilesFeed(uri=feed_uri)

  profiles.extend(feed.entry)

  feed_uri = feed.FindNextLink()

# extract relevant pieces of data for each profile

for entry in profiles:

builder = SignatureBuilder(entry.name.full_name.text)

builder.company = company_name

if entry.occupation:

  builder.occupation = entry.occupation.text

for email in entry.email:

  if email.primary and email.primary == true:

    builder.email = email.address

for number in entry.phone_number:

  if number.primary and number.primary == true:

    builder.phone_number = number.text



# build the signature

signature = builder.HtmlSignature()

# entry.id has the following structure:

# http://www.google.com/m8/feeds/profiles/domain/DOMAIN_NAME/full/USERNAME

# the username is the string that follows the last /

username = entry.id.text[entry.id.text.rfind(/)+1:]

# set the users signature using the Email Settings API

email_settings_client.UpdateSignature(username=username,

  signature=signature)

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.

Editors note: This is a guest post by Benjamin Coe. Benjamin shares tips and best practices on using Ruby on Rails for integrating with Google Apps and launching on the Marketplace. — Arun Nagarajan

Rails.application.config.middleware.use OmniAuth::Builder do
  provider :google_oauth2, ENV["GAM_OAUTH_KEY"], ENV["GAM_OAUTH_SECRET"]
end

# Subclass the GoogleOauth2 Omniauth strategy for
# Google Apps Marketplace V2 SSO.
module OmniAuth
  module Strategies
    class GoogleAppsMarketplace < OmniAuth::Strategies::GoogleOauth2
      option :name, google_apps_marketplace
    end
  end
end

Rails.application.config.middleware.use OmniAuth::Builder do
  provider :google_oauth2,  ENV["OAUTH_KEY"], 
                            ENV["OAUTH_SECRET"], 
                            {:scope => ENV["OAUTH_SCOPE"]}
  provider :google_apps_marketplace,  ENV["GAM_OAUTH_KEY"],  
                                      ENV["GAM_OAUTH_SECRET"], 
                                      { :scope => ENV["GAM_OAUTH_SCOPE"], 
                                        :access_type => online }end

• In the Cloud Console, generate a private key for the OAuth 2.0 Service Account associated with your Google Apps Marketplace project. 
• Download the .p12 private key generated. 
• Place this key somewhere that will be accessible by your production servers, e.g., a certificates folder in your codebase. 

Gmail.connect!(:xoauth, ben@example.com, {
  token: authentication.token,
  secret: authentication.secret,
  consumer_key: google.key,
  consumer_secret: google.secret,
  read_only: true
})

key = Google::APIClient::PKCS12.load_key(
google_apps.service.p12path, # this is a constant value Google uses
# to password protect the key.
notasecret
)service_account = Google::APIClient::JWTAsserter.new(
google_apps.service.email,
https://mail.google.com/,
key
)client = Google::APIClient.new(
:application_name => APPLICATION_NAME,
:version => APPLICATION_VERSION
).tap do |client|
client.authorization = service_account.authorize(ben@example.com)end
Google.connect!(:xoauth2, ben@example.com, {
:oauth2_token => client.authorization.access_token,
})

# Subclass the GoogleOauth2 Omniauth strategy for
# Google Apps Marketplace V2 SSO.
module OmniAuth
  module Strategies
    class GoogleAppsMarketplace < OmniAuth::Strategies::GoogleOauth2

      option :name, google_apps_marketplace

      def request_phase

        # Store the opensocial_viewer_id in the session.
        # this allows us to bind the Google Apps contextual
        # gadget to a user account.
        if request.params[opensocial_viewer_id]
          session[:opensocial_viewer_id] = request.params[opensocial_viewer_id]
        end

        super
      end

    end
  end
end

Now Go Forth

Benjamin Coe profile Benjamin Coe cofounded the email productivity company Attachments.me, which was acquired by Yesware, Inc., in  2013. Before starting his own company, Ben was an engineer at FreshBooks, the  world’s #1 accounting solution. Ben’s in his element when writing scalable cloud-based infrastructure, and loves reflecting on the thought-process that goes into this. A rock-climber, amateur musician, and bagel aficionado, Ben can be found roaming the streets of San Francisco. ben@yesware.com — https://github.com/bcoe— @benjamincoe

Since the public unveiling of the Google Drive SDK in April, companies like Lucidchart or HelloFax have built powerful, slick, useful Google Drive apps, and many more companies are launching compelling integrations every day. During this time, our developer community — especially on Stack Overflow — has grown substantially.

To help support our growing developer community and all the interest in integrating with Google Drive, we’re starting a series of Google Drive developer workshops. For the inaugural event, we are hosting several companies — including Shutterfly, Fedex, Autodesk, Twisted Wave, 1DollarScan and Manilla — to participate in a two-day workshop this week at the Googleplex in Mountain View, California.

During this workshop, Google engineers will be on hand to assist attendees with various parts of their Google Drive integration: things like design and implementation of features, authorization flow, and Android integration. Companies have shown that the Google Drive SDK allows for deep integration in just a couple days and we really hope that attendees of this workshop will enjoy a similar experience. Tune back in later this week to find out more about what we learned and accomplished in our workshop.

If you are interested in attending similar Google Drive workshops near you or if you want to contact the Google Drive team about a potential integration with your product, let us know.

Nicolas Garnier Google+ | Twitter Nicolas Garnier joined Google’s Developer Relations in 2008 and lives in Zurich. He is a Developer Advocate for Google Drive and Google Apps. Nicolas is also the lead engineer for the OAuth 2.0 Playground.

Posted by Wesley Chun, Developer Advocate, Google Apps

Last summer, we launched the new Gmail API, giving developers more flexible, powerful, and higher-level access to programmatic email management, not to mention improved performance. Since then, it has been expanded to replace the Google Apps Admin SDKs Email Migration API (EMAPI v2). Going forward, we recommend developers integrate with the Gmail API.

EMAPI v2 will be turned down on November 1, 2015, so you should switch to the Gmail API soon. To aid you with this effort, weve put together a developer’s guide to help you migrate from EMAPI v2 to the Gmail API. Before you do that, here’s your final reminder to not forget about these deprecations including EMAPI v1, which are coming even sooner (April 20, 2015).

The week before Google I/O, we launched Google Developers Live, a new online channel to connect us with developers from all around the world, all year round.

Google Developers Live features interactive broadcasts about many different products and with many different formats. For Google Drive and Apps Script alone, we have aired app reviews, presentations about Google Drive and Apps Script, question and answer sessions, and a doc feedback session.

We are really interested in knowing from you about your favorite shows. Which types of event would you like to see more of in the future? Are you more interested in introductory material such as getting started tutorials, or more advanced topics?

Please share your feedback with us by adding a comment to this post or by reaching out to us on Google+.

Remember, we go live every Monday and Thursday and our complete schedule can be found at https://developers.google.com/live/drive. See all of you on Google Developers Live!

Claudio Cherubino   profile | twitter | blog Claudio is an engineer in the Google Drive Developer Relations team. Prior to Google, he worked as software developer, technology evangelist, community manager, consultant, technical translator and has contributed to many open-source projects. His current interests include Google APIs, new technologies and coffee.

Posted by Ryan Boyd, Google Apps Team

Want to weigh in on this topic? Discuss on Buzz

import random
import time

def GetResourcesWithExponentialBackoff(client):
  """Gets all of the resources for the authorized user

  Args:
    client: gdata.docs.client.DocsClient authorized for a user.
  Returns:
    gdata.docs.data.ResourceFeed representing Resources found in request.
  """
  for n in range(0, 5):
    try:
      response = client.GetResources()
      return response
    except:
      time.sleep((2 ** n) + (random.randint(0, 1000) / 1000))
  print "There has been an error, the request never succeeded."
  return None

Vic Fryzel profile | twitter | blog Vic is a Google engineer and open source software developer in the United States. His interests are generally in the artificial intelligence domain, specifically regarding neural networks and machine learning. Hes an amateur robotics and embedded systems engineer, and also maintains a significant interest in the security of software systems and engineering processes behind large software projects.

• The first tip was to change the voting so that people vote not only for their top 3 ideas but also their bottom 3. When tallying the results use the net votes (top minus bottom) as your top items to work on for that period. 
• The second tip involves changing the silent writing portion of the retrospective. Instead of writing each individual idea on one post-it, ask each team member to write all their ideas on one paper in a list format. Once everyone has written their list, pass it to the person on your left. Each person then reads their neighbours list and adds to it. Keep passing the lists to the left until everyone has read and contributed to all the lists. Finally, put the individual items on your board and prioritize them as usual.

• Have sinus trouble when flying? A client of mine recommended Sinusalia by Boiron. Take two pills before you get on the plane and then every two hours during your flight - they are magical.
• Need music for your training sessions, workout sessions, relaxing at home, etc? Try the Songza app. It has lots of curated music based on themes and also comes with a pretty neat UX experience that suggests musical themes based on the time of day.

Editor’s note: This is a guest post by Martin Böhringer, Co-Founder and CEO of Hojoki.
-- Steve Bazyl

Honestly, we love Google’s productivity tools. Back in 2010, when I was a computer science researcher at university, I was a heavy user of Google Docs, Google Calendar, Google Contacts and Google Reader. So it was the most natural thing to put these tools to the front for integrations into our startup, Hojoki.

Hojoki integrate productivity cloud apps into one newsfeed and enables sharing and discussions on top of the feed. We’ve integrated 17 apps now and counting, so it’s safe to say that we’re API addicts. Now its Time to share with you what we learned about the Google Apps APIs!

The goal: a newsfeed

Our initial reason for building Hojoki was because of the fragmentation we experience in all of our cloud apps. And all those emails. Still, there was this feeling of “I don’t know what’s going on” in our distributed teamwork. So we decided to build something like a Google+ where streams get automatically filled by activities in the apps you use.

This leads to a comprehensive stream of everything that’s going on in your team combined with comments and microblogging. You can organize your stream into workspaces, which are basically places for discussions and collaboration with your team.

Information needs

To build this, we first need some kind of information on recent events. As we wanted to be able to aggregate similar activities and to provide a search, as well as splitting up the stream in workspaces, we also had to be able to sort events as unique objects like files, calendar entries and contacts.

Further, it’s crucial to not only know what has changed, but who did it. So providing unique identities is important for building federated feeds.

Google APIs

Google’s APIs share some basic architecture and structure, described in their Google Data Protocol. Based on that, application-specific APIs provide access to the application’s data. What we use is the following:

• Google Docs: Google Documents List API version 3.0
• Google Calendar: Google Calendar API v2
  Note: there is a new API v3 for Google Calendar
• Google Contacts: Google Contacts API v2

The basic call for Google Contacts for example looks like this:

https://www.google.com/m8/feeds/contacts/default/full

This responds with a complete list of your contacts. Once we have this list all we have to do is to ask for the delta to our existing knowledge. For such use cases, Google’s APIs support query parameters as well as sorting parameters. So we can set “orderby” to “lastmodified” as well as “updated-min” to the timestamp of our last call. This way we are able to keep the traffic low and get quick results by only asking for things we might have missed.

If you want to develop using those APIs you should definitely have a look at the SDKs for them. We used the Google Docs SDK for an early prototype and loved it. Today, Hojoki uses its own generic connection handler for all our integrated systems so we don’t leverage the SDKs anymore.

Real world problems

If you’re into API development, you’ve probably already realized that our information needs don’t fit into many of the APIs out there. Most of the APIs are object centric. They can tell you what objects are included in a certain folder, but they can’t tell you which object in this folder has been changed recently. They just aren’t built with newsfeeds in mind.

Google Apps APIs support most of our information needs. Complete support of OAuth and very responsive APIs definitely make our lives easier.

However, the APIs are not built with Hojoki-like newsfeeds in mind. For example, ETags may change even if nothing happened to an object because of asynchronous processing on Google’s side (see Google’s comment on this). For us this means that, once we detect an altered ETag, in some cases we still have to check based on our existing data if there really have been relevant activities. Furthermore, we often have trouble with missing actors in our activities. For example, up to now we know when somebody changed a calendar event, but there is no way to find out who this was.

Another issue is the classification of updates. Google’s APIs tell us that something changed with an object. But to build a nice newsfeed you also want to know what exactly has been changed. So you’re looking for a verb like created, updated, shared, commented, moved or deleted. While Hojoki calls itself an aggregator for activities, technically we’re primarily an activity detector.

Final solution

You can think of Hojoki as a multi-layer platform. First of all, we try to get a complete overview on your meta-data of the connected app. In Google Docs, this means files and collections, and we retrieve URI and name as well as some additional information (not the content itself). This information fills a graph-based data storage (we use RDF, read more about it here).

At the moment, we subscribe to events in the integrated apps. If detected, they create a changeset for the existing data graph. This changeset is an activity for our newsfeed and related to the object representation. This allows us to provide a very flexible aggregation and filtering on the client side. See the following screenshot. You can filter the stream for a certain collection (“Analytics”) or only for the file history or for the Hojoki workspace where this file is added (“Hojoki Marketing”).

What’s really important in terms of such heavy API processing is to use asynchronous calls. We use the great Open Source project async-http-client for this task.

A wishlist

When I wrote that “we subscribe to events” this is a very nice euphemism for “we’re polling every 30s to see if something changed”. This is not really optimal and we’d love to change it. If Google Apps APIs would support a feed of user events modelled in a common standard like ActivityStrea.ms, combined with reliable ETags and maybe even a push API (e.g. Webhooks) this would also make life easier for lots of developers syncing their local files with Google and help to reduce traffic on both sides.

Martin Böhringer Martin is Co-Founder and CEO of Hojoki. Hojoki integrates Google Docs, Calendar, Contacts and Reader next to other apps like Dropbox and Evernote into one newsfeed, showing changes by co-workers in one central place. Martin holds a PhD in information systems and likes to perform on stage at Tech Events.

"A document isn’t the only vehicle for expressing or transferring good thinking and ideas."

Image Via ThunderBoxRoad.com

Editor’s Note: Kevin Winter is a guest author from the AdWords API Developer Relations team. He implemented these features in his 20% time. - Jan Kleinert

Google Spreadsheets provides powerful charting functionality to let you analyze your data many different ways. We recently added the ability to programmatically create, modify and delete Spreadsheet charts using Google Apps Script.

What are Embedded Charts?

Charts that a user creates in a Google Spreadsheet are called Embedded Charts. Apps Script can be used to manage these types of Spreadsheet Charts. Each EmbeddedChart belongs to a Sheet. Embedded charts are named this way because they are embedded onto a Sheet within the spreadsheet. Embedded charts align to the upper left hand corner of the specified column and row in the spreadsheet.

An Example of an Embedded Spreadsheet Chart

Let’s say I want to track my gas mileage using Google Spreadsheets. First, I create a Spreadsheet, and set up columns and formulas. Next, I add a form interface to make it easy to add new entries and a chart to visualize my mileage. Now I can start recording data - but each time I add enough entries to go past the ranges the chart uses, I need to manually increase them. How about we use some Apps Script magic to solve this?

The script below iterates through all charts on this sheet and determines if any of the ranges need to be expanded (i.e. if there are more rows with data to display). It then updates the title, builds the new EmbeddedChart object and saves it to the sheet. It could also add a menu interface or a trigger to execute this periodically or when the spreadsheet is edited.

function expandCharts() {
  var sheet = SpreadsheetApp.getActiveSheet()
  // Get a list of all charts on this Sheet.
  var charts = sheet.getCharts();
  for (var i in charts) {
    var chart = charts[i];
    var ranges = chart.getRanges();
    // Returns an EmbeddedChartBuilder with this chart’s settings.
    var builder = chart.modify();
    for (var j in ranges) {
      var range = ranges[j];
      // rangeShouldExpand_ is defined later.
      if (rangeShouldExpand_(range)) {
        // Removes the old range and substitutes the new one.
        builder.removeRange(range);
        var newRange = expandRange_(range);
        builder.addRange(newRange);
      }
    }
    // Update title.
    builder.setOption(title, Last updated  + new Date().toString());
    // Must be called to save changes.
    sheet.updateChart(builder.build());
  }
}

function rangeShouldExpand_(range) {
  var s = range.getSheet();
  var numColumns = range.getNumColumns()
  var values = s.getSheetValues(range.getLastRow(), 
                                range.getColumn(), 
                                2, 
                                numColumns);
  for (var i = 0; i < numColumns; i++) {
    // If the next row has the same pattern of values,
    // it’s probably the same type of data and should be expanded.
    if (!values[0][i] && !values[1][i] 
        || !!values[0][i] && !!values[1][i]) {
      continue;
    } else {
      return false;
    }
  }
  return true;
}

function expandRange_(range) {
  var s = range.getSheet()
  var startRow = range.getRow();
  var startCol = range.getColumn();
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  while (rangeShouldExpand_(range)) {
    numRows++;
    range = s.getRange(startRow, startCol, numRows, numCols);
  }
  return range;
}

Creating New Charts in Spreadsheets

What if you wanted to create a new chart from scratch? You can do that too!

var sheet = SpreadsheetApp.getActiveSheet();
var chart = sheet.newChart()
    .setPosition(5, 6, 5, 5)
    .setChartType(Charts.ChartType.AREA)
    .addRange(sheet.getActiveRange())
    .build();
sheet.insertChart(chart);

In the above code example, we’ve gotten a reference to an EmbeddedChartBuilder, set its position within the sheet, change the chartType, add the currently selected range and insert the new chart.

Modifying Charts in Spreadsheets

The EmbeddedChartBuilder allows you to modify the chart in a couple ways:

• Add/Remove the ranges this chart represents via addRange and removeRange.
• Set options that modify how the chart will be rendered as well as change the chart type with setOption and setChartType.
• Change where the chart will be displayed (the cell and cell offset of the chart container) via setPosition.

Embedded charts use more options than regular Apps Script charts, so options are handled slightly differently. Developers can pass a dotted field path to change options. For example, to change the animation duration, you can do this:

builder.setOption("animation.duration", 1000);

Now that we’ve created a bunch of charts, your spreadsheet is probably pretty cluttered. Want to clear it and start afresh with charts? Just remove them all:

var sheet = SpreadsheetApp.getActiveSheet();
var charts = sheet.getCharts();
for (var i in charts) {
  sheet.removeChart(charts[i]);
}

Take Your Charts with You

Just like standalone charts, you can use embedded charts elsewhere. You can add them to a UIApp or a sites page as well as sending them as an email attachment:

// Attach all charts from the current sheet to an email.  
var charts = SpreadsheetApp.getActiveSheet().getCharts();
MailApp.sendEmail(      
    "recipient@example.com",       
    "Income Charts",  // Subject      
    "Heres the latest income charts", // Content      
    {attachments: charts });

We hope you found this blog post useful. Enjoy editing embedded charts using Google Apps Script!

Kevin Winter   profile Kevin is a Developer Programs Engineer in the AdWords API Team. He maintains the latest version of the Python and Java client libraries for the AdWords API. You can find him in the New York office.

Editor’s note: This is a guest post by Ben Dilts, CTO & Co-founder of Lucidchart.
-- Steve Bazyl

The release of Drive SDK allowing deep integration with Google Drive shows how serious Google is about making Drive a great platform for third parties to develop.

There are a handful of obvious ways to use the SDK, such as allowing your users to open files from Drive in your application, edit them, and save them back. Today, Id like to quickly cover some less-obvious uses of the Drive API that we’re using at Lucidchart.

Automated Backups

Applications have the ability to create new files on Google Drive. This is typically used for content created by applications. For example, an online painting application may save a new PNG or JPG to a users Drive account for later editing.

One feature that Lucidchart has long provided to its users is the ability to download their entire accounts content in a ZIP file, in case they (or we!) later mess up that data in some way. These backups can be restored quickly into a new folder by uploading the ZIP file back to our servers. (Note: we’ve never yet had to restore a user account this way, but we provided it because customers said it was important to them.)

The problem with this arrangement is that users have to remember to do regular backups, since theres no way for us to automatically force them to download a backup frequently and put it in a safe place. With Google Drive, we now have access to a reliable, redundant storage mechanism that we can push data to as often as we would like.

Lucidchart now provides automated backups of these ZIP files to Google Drive on a daily or weekly basis, using the API for creating new files on Drive.

Publishing Content

Another use for the files.create call is to publish finished content. Lucidchart, like most applications, stores its editable files in a custom format. When a user completes a diagram or drawing, they often download it as a vector PDF, image, or Microsoft Visio file to share with others.

Lucidchart is now using the create file API to export content in any supported format directly to a users Google Drive account, making it easy to sync to multiple devices and later share those files.

Indexable Text

Google Drive cant automatically index content created by Lucidchart, or any other application that saves data in a custom format, for full-text search. However, applications now have the ability to explicitly provide HTML content to Google Drive that it can then index.

Indexable text provided to the Drive API is always interpreted as HTML, so it is important to escape HTML entities. And if your text is separated into distinct pieces (like the text in each shape in Lucidchart), you can improve full-text phrase searching by dividing your indexable text into one div or paragraph element per piece. Both the files.create and files.update calls provide the ability to set indexable text.

We hope that this overview helps other developers implement better integrations into the Google Drive environment. Integrating with Drive lets us provide and improve a lot of functionality that users have asked for, and makes accessing and using Lucidchart easier overall. We think this is a great result both for users and web application developers and urge you to check it out.

Ben Dilts Ben is the co-founder and CTO of Lucidchart, an online diagramming application built in HTML5 that features real-time collaboration. Previously, he was CTO of Zane Benefits where he led the development of an online health benefits administration platform which was featured on the front page of The Wall Street Journal. Ben holds a BS in Computer Science from Brigham Young University.

• Math Monday
• Tell Me About Science Tuesday
• Writing Wednesday
• Sight Words Thursday
• Phonics Friday