Using Scripts in Google Spreadsheet

Scripts can give your spreadsheets super powers


You can do a lot of things with Google Drive via scripts, but I will focus here on the most simple scripting scenario. You all have used functions in Excel or similar spreadsheet application to make the value of one cell depend on that of other cells. For example, we usually use the sum() to put the summation of a whole row or column in one cell and we call it total. Nevertheless, in Google Spreadsheets, you can also write your own functions, and in those functions, you can even call external sources or applications.

In my case, I have spreadsheet with one column the represents peoples’ addresses, and I wanted to add another column that contains the latitude and longitude extracted from that address. Easy peasy! From the ‘Tools’ menu select ‘Script Manager’, then click on the ‘New’ button. Then, you are given a page where you can write some JavaScript code.I created a function and called it latlng() which takes one parameter, address. In that function I called Maps.newGeocoder().geocode(address), this returns the results in a json format. I then parsed the results and returned the resulting latitude and longitude. Voila! That’s it. All you have to do now is to use that new latlng() function in your spreadsheet, the same way you used to use any other built-in functions, for example, sum(), or max().




Share on Facebook Share on twitter