My brother, a teacher and renowned idea man, set himself about solving a simple problem they were having at the school. He needed a quick and easy way to poll a spreadsheet while on a Google Form. Specifically, he wanted to do a quick check to see how many times a certain name showed up before hitting Submit. Google Forms does not allow this sort of background lookup while the Form is active, so we had to find another way. The solution we found is to self-host the Google Form HTML, and add a little JavaScript to do the background search through the spreadsheet. This solution has two tricky parts: authenticating to the Google Sheet (since we don’t want to make it public), and then actually doing the query. Turns out neither is difficult. This post will talk about the authentication method we used, and another time I’ll write about polling the spreadsheet.

Hello.js is the solution we used. This JavaScript library takes all of the difficulty out of doing authentication. It required one small tweak, but otherwise was a breeze. If you find yourself needing to do quick authentication using OAuth2.0 through Google or a large number of other social sites, I greatly recommend this library.

The documentation on the site is very good, but left out a few small details required to get it running. Here are the full steps of what I found it required to get authentication to Google SpreadSheets:

1. Register your application as a web app at https://console.developers.google.com/project
   Note: this will require that you know where you’re going to put your site. Google uses the redirect_uri as an authentication, so make sure you know you keep this consistent with the redirect URI you include on the init call (see step 3). The redirect page also must include the hello.js in order for the authentication to work.
2. Procure and include hello.js in your main page and on your redirect page. The redirect doesn’t need to do much except include hello.js.

   <script src="js/hello.js"></script>

3. Initialize Hello.js by calling init and passing some relevant parms:

   hello.init( 
     {google : GOOGLE_CLIENT_ID },
     {redirect_uri:'redirect.html', 
       scope: 'sheets'}
   );

   Note the “scope: sheets”.. We’ll return to this later

4. Call hello.login() from within your code. You can attach it to a button, or just call it from within your code.
5. You can also generate a function to run when authentication returns. I used the example from their site.
6. Once authenticated, store the token which you can get from the object returned from a call to hello(). Here is the code I used both to log in and capture the token:

   hello( 'google' ).login( function() {
     token = hello( 'google' ).getAuthResponse().access_token;
   });

7. Now just include that token on your calls to Google!

That’s it! It’s quick and simple and takes a lot of the work out of doing OAuth2 authentication.

Now about the scope: hello.js is designed primarily for accessing profile data, such as name and photo. It doesn’t have a built-in scope for Google Spreadsheets. To allow me access to that, I had to update hello.js with a new scope type: sheets. This change is made inside hello.js during a call to itself. Do a quick search on “scope” and you’ll find the section. At the end, add “sheets: ‘https://spreadsheets.google.com/feeds’ “. You can then include “sheets” as a scope parameter on your init() call.

// Authorization scopes
scope : {
  //,
  basic : "https://www.googleapis.com/auth/plus.me https://www.googleapis.com/auth/userinfo.email https://www.googleapis.com/auth/userinfo.profile",
  email : '',
  birthday : '',
  events : '',
  photos : 'https://picasaweb.google.com/data/',
  videos : 'http://gdata.youtube.com',
  friends : 'https://www.google.com/m8/feeds, https://www.googleapis.com/auth/plus.login',
  files : 'https://www.googleapis.com/auth/drive.readonly',
  publish : '',
  publish_files : 'https://www.googleapis.com/auth/drive',
  create_event : '',
  offline_access : '',
  sheets : 'https://spreadsheets.google.com/feeds'
}

And there you have it. Just a quick edit, and a few JavaScript calls and you can easily OAuth your way into your private spreadsheets.