Crypto Funding Rate and Open Interest Logger for Google Sheets

Build me a deterministic ETL workflow that logs crypto derivatives data to Google Sheets on a schedule. This is structured time-series logging only. No summarization, no judgement calls, no branching on content. Just a fixed loop over a watchlist that writes rows.

Trigger: cron, every 8 hours, aligned with the standard perpetual funding cycle (00:00, 08:00, 16:00 UTC). I want it to run on the same cadence that major exchanges settle funding so each snapshot lines up with a real funding event.

Inputs I want exposed as workflow variables:

- coins: a list of ticker symbols to track. Default to ["BTC", "ETH", "SOL"] and let me extend it later.

- spreadsheetId: the Google Sheet to write to.

For each coin in the watchlist, do the following in a loop:

1. Call CoinGlass "Get Funding Rate by Exchange" for the coin. This returns current funding rates across every exchange that lists the perp.

2. Call CoinGlass "Get Open Interest by Exchange" for the coin. This returns current open interest per exchange in both USD and coin units.

3. Flatten and join the two responses by exchange into one row per exchange with these columns: timestamp (ISO 8601 UTC of the run), coin, exchange, funding_rate, open_interest_usd, open_interest_coin. If an exchange shows up in one response but not the other, still write the row and leave the missing field blank.

4. Append those rows to a tab in the Google Sheet named after the coin ("BTC", "ETH", "SOL", etc.) using Google Sheets "Append Values" with valueInputOption USER_ENTERED so numbers are stored as numbers.

First-run setup: before the main loop, check whether each coin's tab exists in the spreadsheet. If not, use Google Sheets "Batch Update Spreadsheet" to add a new tab named after the coin and seed row 1 with the header row [timestamp, coin, exchange, funding_rate, open_interest_usd, open_interest_coin]. After the tab exists, never recreate it; just append.

Error handling: if CoinGlass returns a non-zero code envelope or 429, log the error and skip that coin for this run. Do not block other coins. If Google Sheets returns 429, retry with exponential backoff (1s, 2s, 4s, capped at 16s).

Output: nothing user-facing. The success signal is that the sheet has fresh rows. Optionally include a final summary log line with how many rows were written per coin so I can see it in the run history.

Additional information

What does this prompt do?

Snapshots live funding rates and open interest from CoinGlass on the standard 8-hour perp funding cycle.
Tracks a watchlist you control (defaults to BTC, ETH, and SOL) and adds rows for every exchange that lists the coin.
Appends each snapshot to a Google Sheet, one tab per coin, so your history grows automatically.
Gives you a clean, time-stamped dataset you can pivot, chart, or import into your backtesting and journaling sheets.

What do I need to use this?

A CoinGlass account with an active API plan (Hobbyist or higher).
A Google account with edit access to the spreadsheet you want to log into.
A short list of coin tickers to track, like BTC, ETH, and SOL.

How can I customize it?

Change the cadence. Run it every 4 hours, every hour, or daily at a fixed time instead of every 8 hours.
Edit the watchlist. Add or remove tickers like ARB, AVAX, or DOGE without touching the rest of the workflow.
Point it at a different spreadsheet, or split logs across multiple sheets for paper and live trading.

Frequently asked questions

Why every 8 hours?

Most perpetual futures exchanges settle funding on an 8-hour cycle. Running on the same cadence means each row in your sheet lines up with a real funding payment, which makes the history much easier to read and analyze.

Do I need a paid CoinGlass plan?

Yes. The CoinGlass API requires a paid subscription. Their Hobbyist plan starts at 29 dollars per month and is more than enough for a small watchlist running every 8 hours.

What if I want to track more coins later?

Just add the new ticker to the watchlist. The workflow creates a fresh tab the first time it sees a new coin, then keeps appending rows from then on.

Can I use this with Excel instead of Google Sheets?

This prompt is built for Google Sheets, but you can connect Excel to the same data later by exporting the sheet or by adapting the workflow to write somewhere else.

Does it overwrite past snapshots?

No. Every run appends new rows to the end of the coin tab. Your history is preserved and you can sort or filter by timestamp in Sheets.