> For the complete documentation index, see [llms.txt](https://docs.koinju.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.koinju.io/connect-your-tools/google-sheets.md).

# Google Sheets

Query the Koinju market-data database from a Google Sheet using a small Apps Script custom function.

## Before you start

| Setting       | Value                     |
| ------------- | ------------------------- |
| Host          | the provided database URL |
| Port          | 8443                      |
| Protocol      | HTTPS / SSL enabled       |
| Database      | api                       |
| User/Password | provided by Koinju        |

Don't have credentials yet? See [How to connect](/how-to-connect.md).

Google Sheets has **no native ClickHouse connector**. The supported approach is a custom function backed by Apps Script, which calls the ClickHouse HTTPS interface for you.

## Step-by-step

1. In your sheet, open **Extensions ▸ Apps Script**.
2. Delete any boilerplate and paste the script below, then **Save**.
3. In the Apps Script editor, open **Project Settings ▸ Script Properties** and add three properties:
   * `CH_URL` — the provided database **host only** (no `https://`, no port)
   * `CH_USER` — the username provided by Koinju
   * `CH_PASS` — the password provided by Koinju
4. Back in the sheet, call the function from any cell, e.g.:

   ```
   =CHQUERY("SELECT exchange, market, price FROM api.trade WHERE market LIKE 'BTC%' AND timestamp > toStartOfDay(now()) ORDER BY timestamp DESC LIMIT 20")
   ```

```javascript
/**
 * Koinju ClickHouse → Google Sheets.
 * Script Properties: CH_URL (host only), CH_USER, CH_PASS.
 * Cell usage: =CHQUERY("SELECT exchange,market,price FROM api.trade WHERE market LIKE 'BTC%' AND timestamp>toStartOfDay(now()) ORDER BY timestamp DESC LIMIT 20")
 */
function CHQUERY(sql) {
  if (!sql) throw new Error('Pass a SQL string');
  var p = PropertiesService.getScriptProperties();
  var host = p.getProperty('CH_URL'), user = p.getProperty('CH_USER'), pass = p.getProperty('CH_PASS');
  if (!host || !user || !pass) throw new Error('Set CH_URL / CH_USER / CH_PASS in Script Properties');
  var q = /format\s+\w+\s*;?\s*$/i.test(sql) ? sql : sql.replace(/;?\s*$/, '') + ' FORMAT TabSeparatedWithNames';
  var url = 'https://' + host + ':8443/?database=api';
  var res = UrlFetchApp.fetch(url, {
    method: 'post', payload: q,
    headers: { Authorization: 'Basic ' + Utilities.base64Encode(user + ':' + pass) },
    muteHttpExceptions: true
  });
  var code = res.getResponseCode(), body = res.getContentText();
  if (code !== 200) throw new Error('ClickHouse ' + code + ': ' + body);
  return body.replace(/\n$/, '').split('\n').map(function (line) { return line.split('\t'); });
}
```

How it works:

* It calls the ClickHouse **HTTPS interface on port 8443** with **HTTP Basic auth** built from your Script Properties.
* `FORMAT TabSeparatedWithNames` is auto-appended (unless your SQL already ends in a `FORMAT` clause), so the response is parsed into a 2D array that **spills into a sheet range** with a header row.
* Credentials live in **Script Properties, never in cells** — nobody viewing the sheet sees them.
* Apps Script enforces a **\~6-minute execution limit** and **response-size limits**, so always filter by time and add a `LIMIT` to keep result sets small.

## First sanity query

In any cell:

```
=CHQUERY("SELECT exchange, market, timestamp, price FROM api.trade WHERE market LIKE 'BTC%' AND timestamp > toStartOfDay(now()) ORDER BY timestamp DESC LIMIT 20")
```

If \~20 rows spill into the sheet, you're connected. Next, explore what data is available in [Data](/data/coverage.md).