Google Sheet

Get Data

Import data from csv url (read_csv)

=IMPORTDATA("https://raw.githubusercontent.com/sonarsushant/California-House-Price-Prediction/master/housing.csv")

Import data from another google sheet

=IMPORTRANGE("https://docs.google.com/spreadsheets/...", "Sheet1!A1:C20")

Import table data (read_html)

=IMPORTHTML("https://www.goldtraders.or.th/", "table", 5)

Analysis

Select

=QUERY(D:D, "SELECT D WHERE D <> ''")

Group By

=QUERY(D, "SELECT D, COUNT(D) WHERE D <> '' GROUP BY D ORDER BY COUNT(D) DESC")

Skip Header

=QUERY(OFFSET(D:D, 1, 0), "SELECT D, COUNT(D) WHERE D <> '' GROUP BY D ORDER BY COUNT(D) DESC")

Average IF Not Error (#VALUE!)

=AVERAGEIF(K1:K10, ">=0")

or

=AVERAGE(IFERROR(K1:K10))

Substring

=IFERROR(IF(SEARCH("subs", D2), 1), 0)

Last updated