I decided to create a plugin to retrieve the value of a cryptocurrency pair from Google Spreadsheet. I wanted to learn more about google spreadsheet plugin process and its internal. This was fairly enough and then I challenge myself to do the same for Microsoft Online Excel. This first article will explain the Google plugin creation with a focus on the whole flow and Developer experience. I will details/compare the Microsoft plugin creation in another article with again a focus on developer experience. 

IDE 

To create a plugin for a Google sheet you need to create a google sheet and then use its “script editor” section to write the plugin. It was a little surprising and I had to do some research to be sure I was properly understanding the process.  

Once you click on “script editor” it opens a light online IDE 

This IDE allow you to write your code using Google Script language which is based on JavaScript. There are good resources on the language and its specific functions HERE

Code

I wanted to add a function so that people can simply get the value of a crypto currency in the table. The idea I had in mind was something that look like: 

Luckily it is fairly simple to enhance the list of function in google sheet with the use of a special JSdoc keyword in the function documentation. This is clearly documented by google HERE thus, I will not detail it too much. Then I created several objects that represents various crypto currency exchange so that the user can choose which one he wants to use to retrieve the value. All these “exchange” object exposes various functions to create the URL to call and to decode the response. This design allows a main function to do the URL rest call and then let the “exchange” object parse the response. 

The biggest part of the code which include the “exchange” object and the custom function is done in the file code.gs available HERE

The code is pretty clear and well documented so I will not detail more. Maybe just a note on doing a REST call with Google script that use a specific function “UrlFetchApp.fetch” but once again pretty well documented HERE 

All the code is available on bitbucket HERE and fairly easy to follow.

Tests

Unit tests 

There are some Unit tests in the file Test.gs HERE 

There are fairly rudimentary and just output the result of several call in the logs nevertheless it’s more than enough for the amount of code we had. The interesting info here is that you can run the test in the google IDE online and just check the log on the IDE 

Functional tests 

Google IDE allow you to test the plugin in a sheet by just clicking “Publish->Test as standalone plugin”. This will open a popup where you can select which version you want to test 

Once you click “test” it will open the same google spreadsheet that you used to create the script with your addon automatically loaded inside. It means that we can use your custom function to verify if it works 

Publication 

Once the plugin is ready it can be published on the Google chrome store. This was very confusing for me but it appears that google suite plugins are publish on the google chrome store (but it seems they are only visible when you browse the store from a google document). 

To publish you will need to register as a chrome developer and pay 5$. This was also surprising since I’m already register in the google play store but the 2 stores are completely decorrelated (even the publication flow are different). 

To publish on the store, you just click “publish->publish as sheet addon” 

This will open a popup where you have to fill some information  

As you can see it mentioned the chrome store but no worry it will just be a sheet addon at the end. One important point is the checkbox “Publish in the app marketplace”. I have absolutely no idea what it means…. but I manage to publish my addon without checking the box. The first publication in the store will also ask you to fill another page of information (with some screen capture and other info). It is disturbing because the UI look different and seems to ask some info that you already enter in the popup. My guess is that the popup is only for the sheet add-on and then the other page is for all chrome store applications. It’s a little annoying and not very clear especially when you compare that to the process of publish an android application. There is also a manual process which make the first publication long (took me 3 days) but after that the app will be publish on the store: HERE 

Conclusion 

The process was easy thanks to the integrated IDE nevertheless the publication flow is strange (especially because it is different of what I was used too with the android store). The documentation is good although some part is unclear (the publication part… again). Code is very simple especially thanks to the very easy way to create new “custom functions”. 

The Microsoft plugin will be detailed in another article.