A guide to using Google’s API Client Library for JavaScript with Sheets API

Posted August 12th, 2015 in Javascript, Uncategorized by Florentin

Google allow users to interact with Google Apps (like Calendar, Contacts, Drive) though APIs.
Working with the API Client Library for JavaScript in a browser environment presents it’s challenges as described below.

Setup

I’m making a single page application (one html file) to test the Javascript library capabilities for using it with Google Sheets Api.
Wish to demo the following:
* create a new spreadsheet
* search for the newly created file by name
* read rows and columns
* insert new rows

Working with private data requires setting up a project at https://console.developers.google.com/project
Read the chapter “Get access keys for your application” from https://developers.google.com/api-client-library/javascript/start/start-js
Make sure to add “Drive API” here https://console.developers.google.com/project/PROJECT_KEY/apiui/apis/enabled
After “Create new client id” steps, write down the “Client ID” and make sure that JavaScript origins matches the demo server (in my case it was http://localhost:8888)

I’ll start building the demo with the help of these resources:

https://developers.google.com/api-client-library/javascript/samples/samples

https://github.com/google/google-api-javascript-client/blob/master/samples/authSample.html

The demo

This depends on “client.js” which is a single line js file like this: window.clientId = ‘aaa-bbbcccddd.apps.googleusercontent.com';

Here’s the file on GITHUB: https://github.com/florentin/google-sheets-api

Please read the code comments, they are important.

<!DOCTYPE html>
<html>
	<head>
	  <meta charset='utf-8' />
	</head>
	<body>
		<h1>Hello Google Sheets API</h1>
		<em>Open the console and watch for errors and debug messages.</em>
		<div id="step1">
		    <h2>Step 1: Authorize this app with your Google account</h2>
		    <span id="authorize-status"></span>
		    <button id="authorize-button" style="visibility: hidden">Authorize</button>
		</div>
		
		<div id="step2">
            <h2>Step 2: Create a Spreadsheet document in Google Drive</h2>
            <span id="create-status"></span>
            <button id="create-button">Create file in Drive</button>
            <em>Add a file named "blocky" to Google Drive</em>
        </div>
        
        <div id="step3">
            <h2>Step 3: Search spreadsheet files by the name "blocky"</h2>
            <span id="list-status"></span>
            <button id="list-button">Search files (json)</button>
            
        </div>
        
        <div id="step4">
            <h2>Step 4: Find the first file named "blocky" and retrieve the worksheets</h2>
            <span id="worksheets-status"></span>
            <button id="worksheets-button">Retrieve worksheets</button>
        </div>
        
        <div id="step5">
            <h2>Step 5: Add rows to the first worksheet from the spreadsheet "blocky"</h2>
            <span id="rows-status"></span>
            <button id="rows-button">Add rows</button>
        </div>
		
		<script src="client.js"></script>
        
        <script type="text/javascript">
		var scopes = 'https://spreadsheets.google.com/feeds',
		    headers = {
				accept_json: {'Accept': 'application/json'},
				accept_xml: {'Accept': 'application/atom+xml'},
				send_json: {'Content-Type': 'application/json'},
				send_xml: {'Content-Type': 'application/atom+xml'}
		    },    
		    authorizeButton = document.getElementById('authorize-button'),
		    authorizeStatus = document.getElementById('authorize-status'),
	        createButton = document.getElementById('create-button'),
	        listButton = document.getElementById('list-button'),
	        worksheetsButton = document.getElementById('worksheets-button'),
	        rowsButton = document.getElementById('rows-button');

		function handleClientLoad() {
			checkAuth();
			createButton.onclick = handleCreateClick;
			listButton.onclick = handleListClick;
			worksheetsButton.onclick = handleWorksheetsClick;
			rowsButton.onclick = handleRowClick;
		}
    
		function checkAuth() {
		    gapi.auth.authorize({client_id: clientId, scope: scopes, immediate: true}, handleAuthResult);
		}
    
		function handleAuthResult(authResult) {
			if (authResult && !authResult.error) {
				// this app is authorised
				authorizeButton.style.visibility = 'hidden';
				authorizeStatus.innerHTML = 'Is Authorised';
				//makeApiCall();
			} else {
				authorizeButton.style.visibility = '';
				authorizeButton.onclick = handleAuthClick;
				authorizeStatus.innerHTML = 'Not Authorised';
			}
		}
    
		function handleAuthClick(event) {
			gapi.auth.authorize({client_id: clientId, scope: scopes, immediate: false}, handleAuthResult);
		    return false;
		}
    
		function request(method, url, post_data, sucess_call, headers) {
			var oauthToken = gapi.auth.getToken(),
			    xhr = new XMLHttpRequest();
			
			console.info('request url', url);
			
			xhr.open(method, url);
			xhr.setRequestHeader('Authorization', 'Bearer ' + oauthToken.access_token);
			//xhr.setRequestHeader("GData-Version", "3.0");
			if(headers !== undefined) {
				for(var key in headers) {
					xhr.setRequestHeader(key, headers[key]);
				}
			}
			
			xhr.onload = function() {
			    console.info('response xhr', xhr);
			    if(typeof sucess_call === "function")
			        sucess_call(xhr);
			};
			
			xhr.onerror = function(err) {
			    console.error('Get Woops', err);
			};
			
			xhr.send(post_data);
		}
	    
		function get_request(url, success_call, headers) {
			return request('GET', url, null, success_call, headers);
		}
		
		function post_request(url, data, success_call, headers) {
            return request('POST', url, data, success_call, headers);
        }
		
		function json_url(url) {
		    return url+'?alt=json';
		}
		
		function serialize(obj) {
            return '?'+Object.keys(obj).reduce(function(a,k){a.push(k+'='+encodeURIComponent(obj[k]));return a},[]).join('&')
        }
        
        function xhr_json_response(xhr) {
            return JSON.parse(xhr.responseText);
        }
		
		function handleCreateClick(event) {
			/*
			To upload a file, check the Javascript example
			from https://developers.google.com/drive/v2/reference/files/insert
			*/
			var body = {
				    'mimeType': 'application/vnd.google-apps.spreadsheet',
	                'title': 'blocky'},
	            url = 'https://www.googleapis.com/drive/v2/files';
			post_request(url, JSON.stringify(body), null, send_json_header);
		}
		
		function handleListClick(event, callback) {
			/*
			find the Drive Api reference here

https://developers.google.com/drive/v2/reference/

			*/
			var url = 'https://www.googleapis.com/drive/v2/files'+serialize({'q': 'title="blocky" and trashed=false'});   
			get_request(url, function(xhr) {
				var obj = xhr_json_response(xhr);
				console.info('documents', obj, obj.items);
				if(typeof callback === "function") {
					callback(obj);
				}
			}); 
		}
		
		function handleWorksheetsClick(events) {
			/*
			Google Sheets Api has no "reference" page and i 
			couldn't figure out a way to find a spreadsheet based on it's id.

https://developers.google.com/google-apps/spreadsheets/index

			Make sure that the "blocky" spreadsheet has the following rows:
	        key   content url
	        a1    a2      a3
	        b1    b2      b3
			*/
			
			handleListClick(null, function(obj) {
				// This is the endpoint for getting the worksheets of a spreadsheet identified by "id"
                var url = 'https://spreadsheets.google.com/feeds/worksheets/'+encodeURIComponent(obj.items[0].id)+'/private/full';
				
                /*
				=== TROUBLE AHEAD ===
                Google Sheets Api by default returns and accepts "application/atom+xml" content type - that's XML strings
                */
                
                // Let's get an "ATOM+XML" response of the worksheets for our "blocky" spreadsheet
                get_request(url, function(xhr) {
                	console.info('Request atom+xml', xhr);
                }, headers.accept_xml);
                
                /*
                The request doesn't get thought because of this error:
                No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'http://localhost:8888' is therefore not allowed access.
                
                If you check the Network Headers in Chrome Developer tools, 
                Chrome first does a "OPTIONS" request to the "url" and gets:
                    access-control-allow-headers:authorization
                    access-control-allow-methods:GET,OPTIONS
                    access-control-allow-origin:http://localhost:8888
                
                All seems fine until it makes the actual "GET" request where the "access-control-allow-origin" header dissapears 
                and Google Chrome denies the cross origin request. 
                */
                
                /*
                Let's alter the "url" to request a "JSON" feed as described here: 

https://developers.google.com/gdata/docs/json

                */
                
                get_request(url+'?alt=json', function(xhr) {
                    console.info('Request json', xhr);
                    var obj = xhr_json_response(xhr);
                    console.log('json', obj, obj.feed.entry[0]);
                    
                    /*
                    Using a JSON request (?alt=json) allows getting the data out of the worksheets.
                    Manually add some data to the "blocky" spreadsheet in Google Drive app to see results
                    */
                    var url = obj.feed.entry[0].link[0].href;
                    get_request(url+'?alt=json', function(xhr) {
                    	var obj = xhr_json_response(xhr);
                    	console.info('Spreadsheet data', obj, obj.feed.entry);
                    });
                }, headers.accept_json);
                
			});
		}
		
		function handleRowClick(events) {
			handleListClick(null, function(obj) {
				/*
		        Adding new rows as described in the chapter "Add a list row" from 

https://developers.google.com/google-apps/spreadsheets/data

		        is not working

		        */
				var url = 'https://spreadsheets.google.com/feeds/worksheets/'+encodeURIComponent(obj.items[0].id)+'/private/full';
				get_request(url+'?alt=json', function(xhr) {
                    console.info('Request json', xhr);
                    var obj = xhr_json_response(xhr);
                    //console.log('worksheet', obj)
                    
                    var url = obj.feed.entry[0].link[0].href;
                    var post_data = '<entry xmlns="http://www.w3.org/2005/Atom" xmlns:gsx="http://schemas.google.com/spreadsheets/2006/extended"><gsx:key>x1</gsx:key><gsx:content>x2</gsx:content><gsx:url>x3</gsx:url></entry>';
                    post_request(url, post_data, function(xhr) {
                    	console.log('post data', xhr);
                    	/*
                    	No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'http://localhost:8888' is therefore not allowed access.
                    	*/
                    }, headers.send_xml);
				});
			});
		}
    </script>
    <script src="https://apis.google.com/js/auth.js?onload=handleClientLoad"></script>
  </body>
</html>

Conclusions

  • Google Sheets API is using the old “gdata API”. Read more https://developers.google.com/gdata/docs/directory and here https://developers.google.com/gdata/docs/json
  • Reading operations seem to work fine with CORS (https://developers.google.com/api-client-library/javascript/features/cors) requests as long as you request JSON feeds (add ?alt=json to the requested url)
  • I wasn’t able to “POST” data using CORS requests, using either “atom+xml” or “json” content types.
  • Sending “POST” requests from “curl” or Python worked fine.
  • All these basically mean that you can’t build Single-Page Apps for changing data in Google services (unless you use a back-end request proxy)

Blocky – the Chrome Extension that enables tagging blocks of html

Posted July 30th, 2015 in Javascript by Florentin

This small Chrome Extension i’ve created is good for tagging divs or other blocks of html.

Let’s say you see a web page listing the latest 50 news. Using blocky extension you would be able to tag the news you like with “green”, the ones you don’t like with “red” and the ones you didn’t read with “yellow”. On the following visit you’d see the marked items.

Please check the extension here:

https://github.com/florentin/blocky

GTranslator – In place web page translation tool with Google Translate

Posted October 13th, 2010 in Javascript by Florentin

What is GTranslator

If you have ever spent more time browsing though non English web page with the help of Google Translate you most noticed some downsides, most common being the one where Google Translate don’t work on some pages.
To make translations easy and on the fly, I wrote this GreaseMonkey (a Firefox addon) script called GTranslator. It uses Google Translate to replace the text inside the web pages with the appropriate translated versions.

Installation

In order to use this tool you’ll be needing Firefox with the GreaseMonkey addon installed.
You just have to open a Firefox instance, visit the tool’s page GTranslator and click on the “Install” button.
After installation, you will have to access the GreaseMonkey menu “Manage User Scripts” and include some pages for which the translation tool will be available.
This “included pages” codes enable the tool for all Swedish, Danish or German, pages
*.se/*
*.dk/*
*.de/*

How it works

There are a couple of shortcuts available:
F8 redirects the loaded url to translate.google.com
F9 translates the current page
F10 enables/disables the auto translation. When automatic translations are enabled the tool translates the text without any user intervention.

Make sure you have enabled the tool for the current TLD (e.g. *.de/*) or specific websites by accessing the “Manage User Scripts” of the GreaseMonkey extension.

If you like the script don’t forget to rate and comment on http://userscripts.org/scripts/show/74306 :)

HtmlClipper – save html content from any website

Posted August 26th, 2010 in Javascript by Florentin

What is HtmlClipper

HtmlClipper is a bookmarklet which lets you copy html sections of any web pages together with the attached css styles. After the script is enabled inside a web page, you may select and extract any html element together with all it’s children and computed css styles. What you get is a new html document made up of an inline stylesheet and html code needed to render the element as close as possible to what you’ve seen in the source web page.
The bookmarklet only works in Firefox and Google Chrome.

Download HtmlClipper from GitHub

Firefox Installation

  • Make sure the “Bookmarks Toolbar” is visible. If it is not, go to menu View > Toolbars.
  • Drag this link: Html Clipper up to your Bookmarks Toolbar.

How to use it

  • click the bookmarklet from your Bookmarks Toolbar
  • click inside the html page to select an element
  • press “w” to select the parent element
  • press “q” to undo the selection of parent element
  • press “r” to remove an element
  • press “s” to clip the element
  • press ‘esc’ to exit the ‘clipping’ window
  • press ‘x’ to exit HtmlClipper

Here are some screenshots of webpage clippings created with HtmlClipper: