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.

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")

/**
 * 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.