> For the complete documentation index, see [llms.txt](https://docs.datahungry.dev/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.datahungry.dev/utility/google-sheet.md).

# Google Sheet

## Get Data

### Import data from csv url (read\_csv)

{% code overflow="wrap" %}

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

{% endcode %}

### Import data from another google sheet

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

### Import table data (read\_html)

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

## Analysis

### Select

{% code overflow="wrap" fullWidth="false" %}

```excel-formula
=QUERY(D:D, "SELECT D WHERE D <> ''")
```

{% endcode %}

### Group By

{% code overflow="wrap" fullWidth="false" %}

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

{% endcode %}

#### Skip Header

{% code overflow="wrap" fullWidth="false" %}

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

{% endcode %}

## Average IF Not Error (#VALUE!)

```excel-formula
=AVERAGEIF(K1:K10, ">=0")
```

or

```excel-formula
=AVERAGE(IFERROR(K1:K10))
```

#### Substring

```excel-formula
=IFERROR(IF(SEARCH("subs", D2), 1), 0)
```