Google Sheets Integration

// =============================================================================
// Coalition Market Data — Google Sheets App Script
// =============================================================================
//
// Usage (full data):
//   =GetMarketHistory(10000002)                — The Forge, default 7d & 28d
//   =GetMarketHistory(10000002, "7,14,28")     — custom intervals
//   =GetMarketHistory(10000002, "7,28", false) — without header row
//
// Usage (Mokaam compatibility — drop-in replacement):
//   =GetMokaamCompat(10000002)                — returns: type_id | 7d | 28d
//   =GetMokaamCompat(10000002, "7,28")        — same with explicit intervals
//   =GetMokaamCompat(10000002, "7,28", false) — without header row
//
// Configure your API key and base URL below.
// =============================================================================

/** @const {string} Your Coalition Market Data API key (leave empty for public rate limits). */
var CMD_API_KEY = '';

/** @const {string} Base URL of the Coalition Market Data instance. */
var CMD_BASE_URL = 'https://market.coalition.space';

// -----------------------------------------------------------------------------
// Internal helpers
// -----------------------------------------------------------------------------

/**
 * Fetch market history data from the API.
 * @param {number} regionId   EVE region ID.
 * @param {string} intervals  Comma-separated interval days (e.g. "7,28").
 * @returns {Object[]} Parsed JSON array from the API.
 */
function fetchMarketHistory_(regionId, intervals) {
	var url =
		CMD_BASE_URL + '/api/v1/history/' + regionId + '/?intervals=' + encodeURIComponent(intervals);

	var options = {
		method: 'get',
		muteHttpExceptions: true,
		headers: {}
	};

	if (CMD_API_KEY) {
		options.headers['x-api-key'] = CMD_API_KEY;
	}

	var response = UrlFetchApp.fetch(url, options);
	var code = response.getResponseCode();

	if (code !== 200) {
		throw new Error(
			'API returned HTTP ' + code + ': ' + response.getContentText().substring(0, 200)
		);
	}

	return JSON.parse(response.getContentText());
}

/**
 * Parse the intervals string into a deduplicated array preserving order.
 * @param {string} raw  Comma-separated intervals.
 * @returns {number[]}
 */
function parseIntervals_(raw) {
	var parts = raw.split(',');
	var seen = {};
	var result = [];
	for (var i = 0; i < parts.length; i++) {
		var n = parseInt(parts[i].trim(), 10);
		if (isNaN(n) || n < 1 || n > 366) continue;
		if (!seen[n]) {
			seen[n] = true;
			result.push(n);
		}
	}
	return result;
}

// -----------------------------------------------------------------------------
// Public functions
// -----------------------------------------------------------------------------

/**
 * Fetch full market history data for a region.
 *
 * Returns a 2D array with a header row, suitable for use as a custom function.
 * Columns: type_id, then for each interval: {N}d_total_volume, {N}d_daily_mean_volume,
 * {N}d_daily_median_volume, {N}d_min_volume, {N}d_max_volume, {N}d_min_cost, {N}d_mean_cost, {N}d_max_cost.
 *
 * @param {number} regionId        EVE region ID (e.g. 10000002 for The Forge).
 * @param {string} [intervals]     Comma-separated interval days. Default: "7,28".
 * @param {boolean} [includeHeader] Whether to include the header row. Default: true.
 * @returns {Array[]} 2D array.
 * @customfunction
 */
function GetMarketHistory(regionId, intervals, includeHeader) {
	if (!regionId) throw new Error('regionId is required');
	intervals = intervals || '7,28';
	if (includeHeader === undefined || includeHeader === null) includeHeader = true;

	var parsedIntervals = parseIntervals_(intervals);
	if (parsedIntervals.length === 0) throw new Error('No valid intervals provided');

	var data = fetchMarketHistory_(regionId, parsedIntervals.join(','));
	var fields = [
		'total_volume',
		'daily_mean_volume',
		'daily_median_volume',
		'min_volume',
		'max_volume',
		'min_cost',
		'mean_cost',
		'max_cost'
	];

	// Build header row
	var header = ['type_id'];
	for (var i = 0; i < parsedIntervals.length; i++) {
		var prefix = parsedIntervals[i] + 'd_';
		for (var f = 0; f < fields.length; f++) {
			header.push(prefix + fields[f]);
		}
	}

	var result = includeHeader ? [header] : [];

	for (var d = 0; d < data.length; d++) {
		var item = data[d];
		var row = [item.id];
		for (var j = 0; j < parsedIntervals.length; j++) {
			var key = parsedIntervals[j] + 'd';
			var bucket = item[key] || {};
			for (var k = 0; k < fields.length; k++) {
				var val = bucket[fields[k]];
				row.push(val != null ? val : '');
			}
		}
		result.push(row);
	}

	return result;
}

/**
 * Mokaam-compatible drop-in replacement.
 *
 * Returns a 2D array: type_id | {N}d | {N}d | ...
 * Values under each interval column are the daily median volume (daily_median_volume).
 *
 * @param {number} regionId        EVE region ID (e.g. 10000058).
 * @param {string} [intervals]     Comma-separated interval days. Default: "7,28".
 * @param {boolean} [includeHeader] Whether to include the header row. Default: true.
 * @returns {Array[]} 2D array.
 * @customfunction
 */
function GetMokaamCompat(regionId, intervals, includeHeader) {
	if (!regionId) throw new Error('regionId is required');
	intervals = intervals || '7,28';
	if (includeHeader === undefined || includeHeader === null) includeHeader = true;

	var parsedIntervals = parseIntervals_(intervals);
	if (parsedIntervals.length === 0) throw new Error('No valid intervals provided');

	var data = fetchMarketHistory_(regionId, parsedIntervals.join(','));

	// Build header: type_id | 7d | 28d | ...
	var header = ['type_id'];
	for (var i = 0; i < parsedIntervals.length; i++) {
		header.push(parsedIntervals[i] + 'd');
	}

	var result = includeHeader ? [header] : [];

	for (var d = 0; d < data.length; d++) {
		var item = data[d];
		var row = [item.id];
		for (var j = 0; j < parsedIntervals.length; j++) {
			var bucket = item[parsedIntervals[j] + 'd'] || {};
			var vol = bucket.daily_median_volume;
			row.push(vol != null ? vol : 0);
		}
		result.push(row);
	}

	return result;
}