Automate the boring stuff: Google Apps Script

Google Apps Script is a great tool to create add-ons around data present in the google environment. It can also be utilised to create workflows involving the data.

It is a powerful framework written on top of existing G suite applications like Sheets, Docs, Slides etc, which allows you to tinker with the data available to these applications using widely familiar javascript framework. Google Apps Scripts also provides you ways to modify some parts of Google Application UI eg. Menu Bar, which can host your own features/workflows in the same window as the google suite application, or as an add-on under the Add-ons menu option.

Some applications listed on Google Apps Script samples include

• Translating text present in Google Docs
• Displaying responses in Google Sheets as a form

You could even do stock analysis with Google Apps Script.

I would also recommend using the codelabs tutorial attached at the end, if you would like to tinker with Google Apps Script.

This is what we are going to build today

While working on some feature recently, we felt a need to convert spreadsheet columns into semi structured data such as a JSON to be consumed somewhere as an input.

This could easily be achieved by exporting the spreadsheet data as CSV and then using a scripting language which runs on the CSV input to generate JSON in desired format.

There are two major issues with above approach

• Maintenance of script with changes in requirements
• Not intuitive for people belonging to other verticals as execution of script has its own pre-requisites of environment, dependencies which might deter people from using it, which in turn can lead to manual errors

While trying to figure out a solution, I stumbled upon Google Apps Script and I really liked its quick setup and seamless integration with GSuite Application.

Let’s create a spreadsheet with different data types.

Time to enter the coding realm

Go to Tools > Script Editor

You should now see a code editor in the browser, which can be used for scripting. The script editor is very intuitive and supports a lot of IDE features like method navigation etc on the web.

Replace all the content in default file by the following. All the functions are easy to comprehend. This is really naive script but should be sufficient to understand the entities of Google Apps Script.

We utilise SpreadsheetApp from Spreadsheet Service, which is one of the largest and most popular built-in services in Apps Script, similar to other Gsuite services like Document Service etc. These services allow us to access the application specific data eg. rows/ranges of spreadsheet, slides inside presentation.

Hit the save button

and refresh the spreadsheet page. You should see a new entry at the end of Menu items list in the Menu Bar.

Clicking the menu entry Export Key Values as JSON should trigger an authorisation request, asking for permission to access the spreadsheet data. You can authorise it using the gmail account as you trust the developer ;)

This should also provide you a brief insight into the kind of access an application add-on or extension can have.

As soon as the script is authorise to run, clicking the menu again should result in a dialog on top of spreadsheet window containing our spreadsheet data in JSON format.

That’s All Folks

This Google Codelabs tutorial helps you mark your address on Google Maps in the same spreadsheet window in just 4 lines of code. It utilises Spreadsheet service and Maps service.

If you want the above output to look pretty, you could even add HTML, CSS and JS files to the same project. The working script is attached here.

If you would like to understand the fundamentals of Google Apps Scripts, feel free to attempt this codelab tutorial.

So next time, don’t just stick with the data when you feel something is missing. Tinker with it.