# 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).


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.koinju.io/connect-your-tools/google-sheets.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.