Google Sheets Integration
Blokiments API Google Sheets integration
A Google Docs version of the guide is available here.
Introduction
This document will show you how to automatically fetch and update your spreadsheet crypto data using Google Apps Script and Blokiments API.
Are more requests needed? A subscription can be made via the RapidAPI platform: https://rapidapi.com/blokiments/api/blokiments-data-api
Step 1: Setting Up Google Sheets
Create a new Google Spreadsheet or use an existing one.
You MUST SPECIFY 2 columns:
A1 -> ADDRESS - (contract/pool address) This will determine which token/pool you want to analyze. It can be either a token address or a pool address.
B1 -> CHAIN - This will determine what chain to look for in your token/pool.
Available values:
solana
,ethereum
,bsc
,arbitrum
,base
,tron
,apechain
Next, ADD the TOKENS you want to analyze. Example:

Now, you need to select which metric you want to see. You do this by naming the columns from C onwards with specific names.
EXAMPLE - we want to see the TICKER name (C1), if the token is a SCAM (D1), Beta vs. BTC (E1), and Holders HII (F1) for our tokens
Remember! The column name must exactly match the available metric name. You can select as many as you wish.
For quick copy-paste (ADDRESS and CHAIN must still be the first two columns):
PAIR_ADDRESS TOKEN_ADDRESS TOKEN_DECIMALS TOKEN_NAME TICKER PAIR_CREATION TOKEN_CREATION SCAM LIQUIDITY_USD LOCKED_LIQUIDITY BURNED_LIQUIDITY TOTAL_MARKET_CAP CIRCULATING_MARKETCAP BTC_ALPHA ETH_ALPHA SOL_ALPHA BTC_BETA ETH_BETA SOL_BETA RSI MOVING_AVG PRICE_CHANGE_1H PRICE_CHANGE_4H PRICE_CHANGE_12H PRICE_CHANGE_1D PRICE_CHANGE_7D PRICE_CHANGE_30D HOLDERS HOLDER_CHANGE_1D HOLDER_CHANGE_7D HOLDER_CHANGE_30D HOLDERS_TOP1_SUM HOLDERS_TOP10_SUM HOLDERS_TOP20_SUM HOLDERS_TOP50_SUM HOLDERS_TOP100_SUM HOLDERS_TOP200_SUM HOLDERS_HHI HOLDERS_MEDIAN_RANK DEPLOYER_HOLDS VOLUME_CHANGE_1H VOLUME_CHANGE_1D VOLUME_CHANGE_7D DEX_TRADES_CHANGE_1H DEX_TRADES_CHANGE_1D DEX_TRADES_CHANGE_7D MOM10 BB
For the script to run, you also need an API Key. There are two options to get it:
Via the Blokiments page for a free trial (lower API limits)
A Pro version via the RapidAPI dashboard (more on that later)
Step 2: Obtaining an API Key
Option 1: Registration on Blokiments (free)
A free option gives you 100 monthly requests to test the API's performance.
Register on Blokiments. Go to Blokiments - REFERRAL LINK
Use the referral link to obtain great deals and exclusive features in the future
If you don't have an account, click Register in the upper right corner to create one.
Log in with your credentials and navigate to API Keys in the hamburger menu on the top left:
Click on the orange button with a
+
on it to start creating a new API key. And then name it as you wish. The key name is only for you to separate if you have multiple keys. Once you name it, click the orange buttonCreate new key.

You can now copy and save the new API key. If you lose the saved key at any time, you can revisit the “API key” section of the Blokiments web app and copy the key again.
Option 2: Registration on RapidAPI (PRO verison)
If more API requests are needed than provided by the free plan in the Blokiments app, a subscription can be made via the RapidAPI platform: https://rapidapi.com/blokiments/api/blokiments-data-api
The UI is pretty straightforward. After logging in, on the link above, you can select the plan:
Please input your billing information, and don’t forget to like and rate our API.
Step 3: Scripting with Google Apps Script
Return to the Google Spreadsheet we set up in the first section of this tutorial.
On the top of the spreadsheet, click on Extensions > Apps Script.
Please select the following script and copy it. Then, paste the script into the App Scripts grey window we opened before.
const API_KEY = "API_KEY"
const USE_PRO_API = false
// _______ ______ .__ __. __.___________. .___________. ______ __ __ ______ __ __
// | \ / __ \ | \ | | (_ ) | | | / __ \ | | | | / || | | |
// | .--. | | | | | \| | |/`---| |----` `---| |----`| | | | | | | | | ,----'| |__| |
// | | | | | | | | . ` | | | | | | | | | | | | | | | | __ |
// | '--' | `--' | | |\ | | | | | | `--' | | `--' | | `----.| | | |
// |_______/ \______/ |__| \__| |__| |__| \______/ \______/ \______||__| |__|
//
// .______ _______ __ ______ ____ __ ____
// | _ \ | ____|| | / __ \ \ \ / \ / /
// | |_) | | |__ | | | | | | \ \/ \/ /
// | _ < | __| | | | | | | \ /
// | |_) | | |____ | `----.| `--' | \ /\ /
// |______/ |_______||_______| \______/ \__/ \__/
// Script for automated data fetching using Blokiments API
// Version: 2.1.0
const CONFIG = {
API_KEY: API_KEY,
BASE_URL: USE_PRO_API ? "https://blokiments-data-api.p.rapidapi.com/api/v1/metrics/general" : "https://api.blokiments.com/api/v1/metrics/general",
BATCH_SIZE: 10,
DELAY_BETWEEN_BATCHES_MS: 500,
NULL_VALUES_OVERWRITE_CELL: true,
SPREADSHEET: {
USE_NAME: false,
NAME: "DeFiGodMode_Sheet123",
ADDRESS_COL: 1, // Column A
CHAIN_COL: 2, // Column B
START_ROW: 2
}
};
function main() {
const ss = CONFIG.SPREADSHEET.USE_NAME ?
SpreadsheetApp.getActive().getSheetByName(CONFIG.SPREADSHEET.NAME) :
SpreadsheetApp.getActiveSheet();
if (!ss) {
SpreadsheetApp.getUi().alert("Error: Sheet not found!");
return;
}
const [headers, addresses, chains] = getSheetData(ss);
const requests = buildRequests(addresses, chains);
processRequestsInBatches(ss, requests, headers);
}
function getSheetData(sheet) {
const startRow = CONFIG.SPREADSHEET.START_ROW;
const addressCol = CONFIG.SPREADSHEET.ADDRESS_COL;
const chainCol = CONFIG.SPREADSHEET.CHAIN_COL;
// Get all data in columns A and B from START_ROW downwards
const lastRow = sheet.getLastRow();
const addresses = sheet.getRange(startRow, addressCol, lastRow - startRow + 1, 1).getValues();
const chains = sheet.getRange(startRow, chainCol, lastRow - startRow + 1, 1).getValues();
const validRows = [];
for (let i = 0; i < addresses.length; i++) {
const address = addresses[i][0]?.trim();
const chain = chains[i][0]?.trim();
// Stop at the first empty row in either column A or B
if (!address || !chain) break;
validRows.push({
address: address,
chain: chain,
rowNumber: startRow + i
});
}
const headers = [];
let col = 1;
while (true) {
const header = sheet.getRange(1, col).getValue();
if (!header) break; // Stop at first empty header cell
headers.push(header);
col++;
}
return [headers, validRows];
}
function buildRequests(validRows) {
return validRows.map(({ address, chain, rowNumber }) => {
let headers = {
'Accept': 'application/json',
'X-API-Key': CONFIG.API_KEY
};
if (USE_PRO_API) {
headers = {
'x-rapidapi-host': 'blokiments-data-api.p.rapidapi.com',
'x-rapidapi-key': CONFIG.API_KEY
};
}
return {
url: `${CONFIG.BASE_URL}?network=${encodeURIComponent(chain)}&address=${encodeURIComponent(address)}`,
headers: headers,
method: 'GET',
muteHttpExceptions: true,
rowNumber: rowNumber // Store the actual row number
};
});
}
function processRequestsInBatches(sheet, requests, headers) {
const totalRequests = requests.length;
for (let i = 0; i < totalRequests; i += CONFIG.BATCH_SIZE) {
const batch = requests.slice(i, i + CONFIG.BATCH_SIZE);
const responses = UrlFetchApp.fetchAll(batch);
Logger.log("Batch done!")
updateProgress(i + batch.length, totalRequests);
processResponses(sheet, headers, batch, responses);
Utilities.sleep(CONFIG.DELAY_BETWEEN_BATCHES_MS);
}
}
function processResponses(sheet, headers, batch, responses) {
responses.forEach((response, index) => {
const request = batch[index];
const rowNumber = request.rowNumber; // Use the stored row number
try {
if (response.getResponseCode() !== 200) {
handleError(response, request, rowNumber);
return;
}
const data = parseResponse(JSON.parse(response.getContentText()));
updateRow(sheet, headers, data, rowNumber);
} catch (e) {
Logger.log(`Error processing row ${rowNumber}: ${e}`);
}
});
}
function toPercentage(value) {
return value !== null ? `${(value * 100).toFixed(2)}%` : null;
}
function parseResponse(responseData) {
if (responseData.detail) throw new Error(responseData.detail);
console.log("response data", responseData)
return {
NETWORK: responseData.network,
PAIR_ADDRESS: responseData.pair_address,
TOKEN_ADDRESS: responseData.token_address,
TOKEN_DECIMALS: responseData.token_decimals,
TOKEN_NAME: responseData.token_name,
TICKER: responseData.token_symbol,
PAIR_CREATION: responseData.pair_creation_datetime,
TOKEN_CREATION: responseData.token_creation_datetime,
SCAM: responseData.is_scam,
LIQUIDITY_USD: responseData.current_liquidity_usd,
LOCKED_LIQUIDITY: toPercentage(responseData.current_locked_liquidity_pct),
BURNED_LIQUIDITY: toPercentage(responseData.current_burned_liquidity_pct),
TOTAL_MARKET_CAP: responseData.current_total_marketcap,
CIRCULATING_MARKETCAP: responseData.current_circulating_marketcap,
BTC_ALPHA: responseData.current_alpha_vs_bitcoin,
ETH_ALPHA: responseData.current_alpha_vs_eth,
SOL_ALPHA: responseData.current_alpha_vs_sol,
BTC_BETA: responseData.current_beta_vs_bitcoin,
ETH_BETA: responseData.current_beta_vs_eth,
SOL_BETA: responseData.current_beta_vs_sol,
HOLDERS_HHI: responseData.holders_hhi_market_concentration,
HOLDERS_MEDIAN_RANK: responseData.holders_median_holder_rank,
DEPLOYER_HOLDS: toPercentage(responseData.holders_pct_hold_by_deployer),
RSI: responseData.current_rsi,
MOVING_AVG: responseData.current_moving_average,
...Object.fromEntries(
['1h', '4h', '12h', '1d', '7d', '30d'].map(period => [
`PRICE_CHANGE_${period.toUpperCase()}`,
responseData[`price_change_${period}`]?.toFixed(2) + "%"
])
),
HOLDERS: responseData.total_holders,
...generateHolderChanges(responseData),
...generateTopHolders(responseData),
VOLUME_CHANGE_1H: responseData.volume_change_1h,
VOLUME_CHANGE_1D: responseData.volume_change_1d,
VOLUME_CHANGE_7D: responseData.volume_change_7d,
DEX_TRADES_CHANGE_1H: responseData.dex_trades_change_1h,
DEX_TRADES_CHANGE_1D: responseData.dex_trades_change_1d,
DEX_TRADES_CHANGE_7D: responseData.dex_trades_change_7d,
MOM10: responseData.current_mom_10,
BB: responseData.current_bollinger_band_pct
};
}
function updateRow(sheet, headers, data, rowNumber) {
const columnIndices = {};
headers.forEach((header, index) => {
columnIndices[header] = index + 1; // +1 because spreadsheet columns are 1-based
});
const rowData = [];
headers.forEach(header => {
if (['ADDRESS', 'CHAIN'].includes(header)) return;
const value = data[header] ?? '';
rowData[columnIndices[header] - 3] = // -3 because we start writing at column C (3)
CONFIG.NULL_VALUES_OVERWRITE_CELL ? value : (value || null);
});
// Fill empty spots with null to prevent shifting
for (let i = 0; i < rowData.length; i++) {
if (rowData[i] === undefined) rowData[i] = null;
}
if (rowData.length > 0) {
sheet.getRange(rowNumber, 3, 1, headers.length - 2) // -2 to exclude ADDRESS/CHAIN
.setValues([rowData])
.setNumberFormat("@");
}
}
function handleError(response, request, rowNumber) {
const error = `${response.getResponseCode()} ${response.getContentText().slice(0, 100)}`;
Logger.log(`Error in row ${rowNumber}: ${error}`);
SpreadsheetApp.getActiveSpreadsheet().toast(
`Error row ${rowNumber} (${request.url})`,
"API Error",
10
);
}
function updateProgress(current, total) {
const percentage = ((current / total) * 100).toFixed(1);
SpreadsheetApp.getActiveSpreadsheet().toast(
`${current}/${total} (${percentage}%)`,
"Progress",
5
);
}
function generateHolderChanges(responseData) {
return Object.fromEntries(
['1d', '7d', '30d'].map(period => [
`HOLDER_CHANGE_${period.toUpperCase()}`,
responseData[`holders_change_${period}`]
])
);
}
function generateTopHolders(responseData) {
return Object.fromEntries(
[1, 10, 20, 50, 100, 200].map(n => [
`HOLDERS_TOP${n}_SUM`,
toPercentage(responseData[`holders_top${n}_sum`])
])
);
}
Now, we have a very important step: scroll to the top of the script in the App Scripts. Now, you need to replace the “API_KEY” in the first line of the script with the API key you created in the previous section (Obtaining an API key). Go to Blokiments and copy the key now.
So, now, this part of the script:
It should look something like this:
Three things to consider:
Each user will have a different API key; the key shown in this picture is just for tutorial purposes; your key will be different.
You must NOT remove quote characters (
) surrounding your API key.
Check the following section if you are using a Pro version from Rapid API.
Now, the script in the App Scripts should look something like this:
Save the script by clicking on the
Save
icon at the top of the window:
Now, we want to run the script. In the same tab where you have clicked “Save Button,” click the dropdown menu to ensure the method “main” is selected. Then, Run the script to populate the Google Spreadsheet we created earlier.
The first time you run the script, you need to review permissions since this is an unverified script you just created:
Click on advanced options and then click go to Untitled project (unsafe)
Accept all conditions that will appear and click Allow in the pop-up window. After the script was run, if there was no error, you should get a similar output:
In the lower right corner of the sheet, there will be occasional updates on the progress.
The data can now be found in your spreadsheet.
If you want to change addresses and chains, add more metrics to track, or refresh the data, go to the spreadsheet we created, make the desired changes, and ensure it is saved (usually autosaves).
Go again to extensions > App Script; the script should already be there, and click on Run.
NOW YOU ARE DONE. You can check the rest of this tutorial to get more information.
Advanced settings
Using ActiveSheet vs. Sheet Name
By default, the script will use an active sheet. The active sheet in a spreadsheet is the sheet displayed in the spreadsheet UI.
If you want to specify what sheet the script should use by name:
Go into the code
Change
USE_NAME
in the 28th line to true (replacefalse
withtrue
)Change
NAME
in the 29th line to the name of your sheet.
Empty values don’t overwrite cell values
By default, if the API returns an empty value (missing data or data that can't be computed), it will always overwrite your cell. This is designed to ensure that you always have the most up-to-date data.
But, if you wish for empty values to skip cells (e.g., you have a formula that doesn't support empty values), you can:
Change
NULL_VALUES_OVERWRITE_CELL
in the 26th line tofalse
.
Pro API (RapidAPI key)
If more API requests are needed than provided by the free plan in the Blokiments app, a subscription can be made via the RapidAPI platform: https://rapidapi.com/blokiments/api/blokiments-data-api
Change the API_KEY
in the first line of the script to the new key provided by the RapidAPI, change the USE_PRO_API
to true
in the second line.
Metrics descriptions
TOKEN_ADDRESS, PAIR_ADDRESS
Address of the token and address of the pool.
They are not the same thing!
A token contract address is a unique address that represents a token on the blockchain.
In DEX (decentralized exchange) trading, a pair address refers to the smart contract address of a liquidity pool for a specific token pair. One token can have multiple pools or pair addresses.
TOKEN_DECIMALS
Every token needs to specify how many “decimals” it has. When you buy 1 PEPE on dextools, this “1” is a human-friendly number. The actual number is 1 * 10^(d), where “d” is the decimals of the token.
TOKEN_NAME, TICKER
The name of the token and the token’s symbol.
PAIR_CREATION, TOKEN_CREATION
The creation datetime of the selected pair.
The creation datetime of the selected token.
SCAM
If the selected token was marked as a scam by our security engine. We account for previous rug pulls, honeypots, or scams.
LIQUIDITY_USD
The amount of liquidity (in USD) for the given pool.
LOCKED_LIQUIDITY, BURNED_LIQUIDITY
Percentage amount of tokens used for liquidity that are locked or burned.
For example, if 100% is locked or burned, there is almost no way you can get the rug pulled (of course, there are different types of scams that can still happen).
(!!! Some liqs are scammy. Locker providers still have their backdoors. Also, this doesn’t protect you against hidden mint !!!)
TOTAL_MARKET_CAP, CIRCULATING_MARKETCAP
The total market cap is calculated by multiplying the total token supply by the current price.
The total token supply is the number of coins created minus any coins burned (removed from circulation).
The circulating market cap is calculated by multiplying all the circulating tokens supplied with the current price.
Circulating supply is defined as the amount of coins circulating in the market and tradeable by the public.
Alpha (BTC_ALPHA, ETH_ALPHA, SOL_ALPHA)
Alpha measures the performance of a token relative to its expected return based on a specific benchmark (like BTC). It helps assess whether a token is outperforming or underperforming after accounting for its inherent risk and market conditions.
Here’s what different alpha values mean:
Alpha > 0: The token has outperformed its benchmark
Alpha = 0: The token has performed as expected, matching its benchmark's return.
Alpha < 0: The token has underperformed its benchmark.
Beta (BTC_BETA, ETH_BETA, SOL_BETA)
Beta measures a token's price relative to a specific benchmark, like Bitcoin, Ethereum, or Solana. It helps assess the token's volatility and risk compared to the benchmark.
Here's what different beta values mean:
Beta = 1: The token moves in sync with the market. The token will likely do the same if the market rises or falls by 5%.
Beta > 1: The token is more volatile than the market. For example, if the market rises by 5%, the token might rise by 7% (and fall more sharply in downturns).
Beta < 1: The token is less volatile than the market. If the market rises or falls by 5%, the token's movement may be smaller, like 3%.
Beta = 0: No correlation with the market; the token's price moves independently.
Negative Beta: The token moves in the opposite direction of the market. If the market rises, the token tends to fall, and vice versa.
RSI
RSI is a tool that measures how fast and how much the price of a token changes over some time, in this case, 30 days. It helps determine whether a token might be overbought (too high and could drop) or oversold (too low and could rise). The RSI value ranges from 0 to 100:
RSI > 70 suggests the token might be overbought.
RSI < 30 suggests the token might be oversold.
MOVING_AVG
A moving average is a calculation that shows the average price of a token over a specific period - in the current case, this is the last 7 days. It smooths out short-term price changes and helps identify trends. For example, if the token's price is going up steadily, the moving average will also go up, showing a clear upward trend. It's like looking at the big picture of the price instead of focusing on every small change.
Price Change
It tells us how much a price has changed in a given interval.
Holders (HOLDERS, HOLDERS_CHANGE_1D, HOLDERS_CHANGE_7D, HOLDERS_CHANGE_30D)
HOLDER describes how many unique wallets are holding this token.
Change in holders tells us how many holders have changed in a given interval.
Exp: holder_change_1d = 30, means that 30 new wallets have started holding this token
Holders Top X SUM
It tells us what % of supply the top X holders hold.
Exp: HOLDERS_TOP10_SUM = 20%. The top 20 whale wallets hold 20% of all token supply.
DEX wallets are excluded.
HOLDERS_HHI
The Herfindahl-Hirschman Index of the selected token. The Herfindahl-Hirschman Index (HHI) is a common measure of market concentration used to determine market competitiveness.
HOLDERS_MEDIAN_RANK
Identifies the wallet rank at which half the total token supply is held. For example, if the median rank is 50, the first 50 holders collectively own 50% of the total supply. Indicates whether the token supply is top-heavy (dominated by a few wallets) or more evenly distributed. The higher the value, the better.
DEPLOYER_HOLDS
The percentage of the total token supply held by the token deployer. The deployer is the wallet that created the token contract. Indicates whether the deployer significantly influences the token's market dynamics.
Volume Changes (VOLUME_CHANGE_1H, VOLUME_CHANGE_1D, VOLUME_CHANGE_7D)
The selected pair's trading volume change in the last 1h/1d/7d in USD.
Dex Trades Changes (DEX_TRADES_CHANGE_1H, DEX_TRADES_CHANGE_1D, DEX_TRADES_CHANGE_7D)
The change in the number of DEX trades of the selected pair in the last 1h/1d/7d.
Momentum (MOM10)
(Link)
Momentum monitors the change in prices. It tells you whether prices are increasing at an increasing rate or decreasing at a decreasing rate. Is the market trend about to change? Is the market overbought or oversold? Momentum may help you find those market conditions.
The indicator calculates momentum by computing the continuous difference between prices at fixed intervals. That difference is either a positive or negative value. When momentum is above the zero line and rising, prices are increasing at an increasing rate. If momentum is above the zero line but is declining, prices are still increasing but at a decreasing rate.
The opposite is true when momentum falls below the zero line. If momentum is falling and is below the zero line, prices are decreasing at an increasing rate. With momentum below the zero line and rising, prices are still declining but at a decreasing rate.
The normal trading rule is simple. Buy when the momentum line crosses from below the zero line to above. Sell when the momentum line crosses from above the zero line to below. Another possibility is to establish bands at each extreme of the momentum line. Initiate or change positions when the indicator enters either of those zones. You could modify that rule to enter a position only when the indicator reaches the overbought or oversold zone and then exits that zone.
Bollinger Bands (BB)
(Link)
Bollinger Band Percent (BB %B) quantifies a symbol's price relative to the upper and lower Bollinger Band. There are six basic relationship levels:
%B equals 1 when price is at the upper band
%B equals 0 when price is at the lower band
%B is above 1 when price is above the upper band
%B is below 0 when price is below the lower band
%B is above .50 when price is above the middle band (20-day SMA)
%B is below .50 when price is below the middle band (20-day SMA)
Last updated
Was this helpful?