Useful Google Sheets Functions

From time to time you want to get more from your data in Google Sheet. It might be some information you collected from a form or any other spreadsheet with data you wish to work on. Here are some useful functions for common tasks.

1. Return the last row

There is no specific function to return the “last row” – but this hack is working perfectly.

=INDEX(A:A,COUNTA(A:A),1)

2. Joining text

Useful for all the cases where you have first name and last name in 2 columns and you wish to join them.

="this name " & A1 & “ plus this extra name ” & B1


or if you have cells you wish to merge into one with a certain delimiter:
=join(“;”,A1:A5)



3. Import any RSS, HTML or CSV data

Advertisement

2014 World Cup Matches And Results

The world cup is the biggest sporting event on the planet. During the last world cup in 2010, 3.2 billion people watched at least one of the games. As I know, many groups are going to ‘make it interesting’ by placing bets on the results, I thought it would be good to have all the data on a spreadsheet. Google sheets are ideal for that as they offer a great ability to share, comment and ‘play’ with the data. You can see the demo spreadsheet: http://goo.gl/ZjdfqE and fork the code from: https://github.com/greenido/WorldCup

For now, we are fetching the info from these end points:

What’s New In Google Developers World

Here are my slides from a talk I gave last week on what’s new and available for you as a developer in Google’s world. This talk gives a high level overview on the main APIs. If you wish to dive deeper on some of them, you will find many links in the slides.

Big Query And Google Spreadsheet Integration

There are many options to extend the powerful spreadsheets that Google offers. One of the cool, new ways to leverage its power is by using a spreadsheet as your ‘front-end’ to a big data processing power (=Big Query). In our world, there is a need to get results as fast as possible and since our data sources grow fast. It’s nice to have a tool that let us ‘see’ (and share) results quickly and easily.

What is BigQuery?

Google BigQuery is a web service that lets you do interactive analysis of massive datasets. When we saying massive we are talking here on billions of rows (or more). It is a scalable and easy to use tool that gives developers and businesses an easy way into powerful data analytics on demand.

As for Google Docs and their powerful sharing capabilities – I guess we don’t need to elaborate here. So, let’s see what are the steps that will let us get data from BigQuery into our Google spreadsheet.

Integrate BigQuery To Google Spreadsheet

Mobile Web App – Real Life Example

This week in Google Developers Live Israel, we showed a live example of a mobile web app that is built in two ways. One we used jQueryMobile as our UI framework and for cases where we need to support IE we used bootstrap. All the code can be found on github. The nice aspect in this real demo is that we used google sheets are our ‘server side’ and since its got a cool way to publish their content to the web as RSS, ATOM, XML, JSON feeds we could work with them from the web/mobile apps. The three main elements we used are:

Yahoo Finance API With NodeJS

In the past I’ve wrote this post on the different options you can use with Yahoo Finance API. It is time (4 years later!) to a followup post on how to gain more data but this time with NodeJS.
The first idea was to be able to gain information by using different parameters and downloading csv files from Yahoo finance. Something like this GET request will do the magic:

http://finance.yahoo.com/d/quotes.csv?s=NFLX&f=snd1l1yr and from there you can work with the data.

However, there are cases, where you wish to have information that is not part of this set of arguments. Here are two quick examples for such cases:

GDL Israel On Yeoman.io, AngularJS And Github Pages

In this week episode of GDL-IL we talked with Ran Tavory on his new conference and how he built its website. Here are the main topics we covered:

• Yeoman as the main built tool. We found out that is easy to start working with it. This powerful tool gives you a set of tools that any modern web developer should use. Yeoman is a robust and opinionated set of tools, libraries, and a workflow that can help you be more productive and use the best practices in modern web development. Give it a ride…
• AngularJS – To add the dynamic part for our site. It’s very interesting to see how Ran used google spreadsheets as his database in the cloud and with the add angular he fetch the data and update the content automatically.
• Github pages – As a free (and scalable) hosting service.
• Twitter Bootstrap – Because it is saving you a lot of ‘leg work’ by using this powerful css framework. Ran didn’t start with it… but once he saw that the site doesn’t look good on mobile, he jump on this wagon and luckily for him it worked.
• Google spreadsheet as a database and a simple database for our CMS in the cloud.