Recipes

Practical, copy-paste patterns for common integrations. Each one builds on the basics in Getting started and Querying with SoloQL.

Pull consolidated sales and shipping every morning

Run one query on a schedule to capture yesterday-and-back-30-days sales, shipping, and orders per store, and save the CSV with the date in its name.

Save this as morning-pull.sh and store your key in an environment variable:

#!/usr/bin/env bash
set -euo pipefail

: "${ECOMSOLO_API_KEY:?set ECOMSOLO_API_KEY}"
OUT="sales-$(date +%F).csv"

curl -sS https://api.ecomsolo.com/v1/query \
  -H "Authorization: Bearer ${ECOMSOLO_API_KEY}" \
  -H "Content-Type: application/json" \
  -H "Accept: text/csv" \
  -d '{"query": "FROM sales SHOW total_sales, shipping, orders GROUP BY store SINCE -30d"}' \
  -o "${OUT}"

echo "wrote ${OUT}"

Schedule it with cron to run at 6am every day:

0 6 * * * ECOMSOLO_API_KEY=esk_live_... /path/to/morning-pull.sh >> /var/log/ecomsolo-pull.log 2>&1

tip

Store the key in a secrets manager or a locked-down environment file, not inline in the crontab, and use a key dedicated to this job so you can revoke it without touching your other integrations.

Load results into Google Sheets

Pull JSON in Apps Script and write it straight into a sheet. In the Google Sheets editor, open Extensions → Apps Script and paste:

function refreshSales() {
  const key = PropertiesService.getScriptProperties().getProperty("ECOMSOLO_API_KEY");
  const res = UrlFetchApp.fetch("https://api.ecomsolo.com/v1/query", {
    method: "post",
    contentType: "application/json",
    headers: { Authorization: "Bearer " + key },
    payload: JSON.stringify({
      query: "FROM sales SHOW total_sales, shipping, orders GROUP BY store SINCE -30d",
    }),
  });

  const data = JSON.parse(res.getContentText());
  const header = data.columns.map((c) => c.title);
  const rows = data.rows.map((r) => data.columns.map((c) => r[c.name]));

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sales");
  sheet.clearContents();
  sheet.getRange(1, 1, 1, header.length).setValues([header]);
  if (rows.length) {
    sheet.getRange(2, 1, rows.length, header.length).setValues(rows);
  }
}

Store the key under Project Settings → Script Properties as ECOMSOLO_API_KEY, then add a time-driven trigger to run refreshSales on a schedule.

Feed a BI tool or data warehouse

For a warehouse or BI load, request CSV and pipe it into your loader. Because the same query can group by store and normalise currency, you get warehouse-ready, comparable rows in one call.

  • cURL
  • Python

info

Adding a TIMESERIES day interval and WITH CURRENCY 'USD' gives you one currency-normalised row per store per day — ideal for an incremental warehouse load and downstream BI modelling.

More