webdev

Useful Google Sheets Functions

water falls

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

import rss in google sheets

 

 

 

You can also fetch a CSV data file with: IMPORTDATA function

Import CSV data to google sheets

 

 

 

 

 

 

It’s even got an option to load a table (or list) from a web site with: IMPORTHTML

import html in google sheets

 

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?

 

 

 

 

 

 

 

 

Advertisement
Standard

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s