Chrome Extension Writing to Google Spreadsheet

Question

I've been doing some research and for some reason can't find a good example showing this anywhere, and I am starting to wonder if it's even possible.

What's I'm looking to do is to have my extension write data within a Google Spreadsheet, so that the sheet is being used as a database.

Does anyone have any documentation that I could follow through? Considering that the Spreadsheet API doesn't seem to allow JavaScript, is that even possible?

THanks.

Answer 1

Yes, it is definitely possible. I have used the Spreadsheet API extensively using Javascript. You'll need to use the Protocol version of the API as documented here: https://developers.google.com/google-apps/spreadsheets/

This requires sending signed requests using OAuth2 (the older auth protocols aren't really reliable anymore.) so I suggest using an OAuth2 library like JSO. https://github.com/andreassolberg/jso

When writing your javascript you'll need to write functions that create an XML string to interface with the Protocol API. Parsing the responses is pretty straight forward. I've included a snippet of the code I've used. You can also see my answer to a related question using JQuery here. JQuery .ajax POST to Spreadsheets API?

function appendSpreadsheet(){

 //Constructs the XML string to interface with the Spreadsheet API.
 //This function adds the value of the param foo to the cell in the first empty row in the column called 'columnTitle'. 
 //The Spreadsheet API will return an error if there isn't a column with that title.
 function constructAtomXML(foo){
  var atom = ["<?xml version='1.0' encoding='UTF-8'?>",
          '<entry xmlns="http://www.w3.org/2005/Atom" xmlns:gsx="http://schemas.google.com/spreadsheets/2006/extended">',//'--END_OF_PART\r\n',
          '<gsx:columnTitle>',foo,'</gsx:columnTitle>',//'--END_OF_PART\r\n',
          '</entry>'].join('');
  return atom;
 };

 var params = {
 'method': 'POST',
 'headers': {
   'GData-Version': '3.0',
   'Content-Type': 'application/atom+xml'
 },
 'body': constructAtomXML(foo)
 };

 var docId //Get this from the spreadsheet URL or from the Google Drive API.
 var worksheetId = 'od6'; //The worksheet Id for the first sheet is 'od6' by default.

 url = 'https://spreadsheets.google.com/feeds/list/'+docId+'/'+worksheetId+'/private/full';

 sendSignedRequest(url, handleSuccess, params); //Use your OAuth2 lib
}

Answer 2

I think you are having same question which I had some months ago. I was looking for some library to do same but couldn't found any so I end up with creating one called gsloader. I am using this library in this jiraProgressTracker chrome extension. Chrome extension is under development but gsloader library is ready to use.

Here is what you need to do.

1. Create a google cloud project under this, https://cloud.google.com/console#/project. Be patient, it will take some time.
2. Under "Registered Apps", do not delete the "Service Account - Project".
3. Under "Registered Apps", register a new app, choose platform web application.
4. Under "APIs", select "Drive API".
5. In newly created app, paste your chrome application url(like chrome-extension://) for "web origin"
6. Copy "client id" from OAuth 2.0 Client ID from app created in step 3
7. Add gsloader library, into you html page. It needs require.js and js-logger and jQuery. If you can't use requirejs, please let me know I will try to create library by removing requirejs dependency, though it may take more time for me to do it.

8. Following is some code snippet to go with.
   // Do Authorization
   var clientId = "<your client id>";
GSLoader.setClientId(clientId);

   // Load existing spreadsheet
   GSLoader.loadSpreadsheet("spreadsheet id");

   // Create spreadsheet
   GSLoader.createSpreadsheet("spreadsheet id")

   There are enough methods and objects available to work with, rather than mentioning all here I will try to make documentation available.

Please let me know, how does it works with you overall.