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
An easy way to load an RSS feed (and control what you fetch) is with: IMPORTFEED
You can also fetch a CSV data file with: IMPORTDATA function
It’s even got an option to load a table (or list) from a web site with: IMPORTHTML
4. COUNTIF()
If you wish to count certain rows base on a specific ‘key’.
=COUNTIF(A1:A50, “Ratio”)
5. SUMIF()
If you wish to sum certain rows base on a specific ‘key’.
=SUMIF(B2:B10,“Apple”,C2:C10)
6. GOOGLEFINANCE
When you wish to get finance data directly into your sheet.
= GOOGLEFINANCE(ticker, [attribute], [start_date], [num_days|end_date], [interval])
And if you need more data and greater flexibility, check this post I wrote in the past on “Yahoo! finance unofficial API”
Btw, if you need to see what are the biggest spreadsheets (or any other files) in your Google account:
Any other useful hacks I’m missing here?
Ido Green 