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.

Step 1: Setting Up Google Sheets

1. Create a new Google Spreadsheet or use an existing one.

2. You MUST SPECIFY 2 columns:

   1. 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.

   2. B1 -> CHAIN - This will determine what chain to look for in your token/pool.

      1. Available values: solana, ethereum, bsc, arbitrum, base, tron, apechain

3. Next, ADD the TOKENS you want to analyze. Example:

A Google Sheet example with five tokens.

1. Now, you need to select which metric you want to see. You do this by naming the columns from C onwards with specific names.

   1. 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.

Available metrics names

• NETWORK

• PAIR_ADDRESS

• TOKEN_ADDRESS

• TOKEN_DECIMALS

• TOKEN_NAME

• TICKER

• 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

For quick copy-paste:

NETWORK	PAIR_ADDRESS	TOKEN_ADDRESS	TOKEN_DECIMALS	TOKEN_NAME	TICKER	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

1. For the script to run, you also need an API Key.

Step 2: Obtaining an API Key

1. Register on Blokiments. Go to Blokiments - REFERRAL LINK

   1. Use the referral link to obtain great deals and exclusive features in the future

   2. NOT referral link

2. If you don't have an account, click Register in the upper right corner to create one.

1. Log in with your credentials and navigate to API Keys in the hamburger menu on the top left:

1. 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 button Create 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.

Step 3: Scripting with Google Apps Script

1. Return to the Google Spreadsheet we set up in the first section of this tutorial.

2. On the top of the spreadsheet, click on Extensions > Apps Script.

   
3. Please select the following script and copy it. Then, paste the script into the App Scripts grey window we opened before.

var BLOKIMENTS_API_KEY = "API_KEY"
var BLOKIMENTS_BASE_URL = "https://api.blokiments.com/api/v1/metrics/general"


function main() {
 var alphabet = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",
   "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ"];
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var columnHeaders = sheet.getRange("A1:AZ1").getValues()[0]
 var addresses = sheet.getRange("A2:A").getValues()
 var chains = sheet.getRange("B2:B").getValues()
 for (var addrIndex = 0; addrIndex < addresses.length; addrIndex++) {
   Utilities.sleep(200);
   var address = addresses[addrIndex][0];
   if (address !== '') {
     try {
       Logger.log("Fetching address " + address)
       var resp = fetchTokenDataBlokiments(address, chains[addrIndex]);


       var finalObj = getFinalObject(resp);
       for (var headIndex = 0; headIndex < columnHeaders.length; headIndex++) {
         var header = columnHeaders[headIndex];
         if (header !== "" && header !== "ADDRESS" && header !== "CHAIN") {
           let value = ""
           try {
             value = finalObj[getDisplayValue(header)]
           } catch (error) {
             Logger.log("Error parsing value", header, finalObj)
           }
           writeToCell(sheet, alphabet[headIndex], addrIndex + 2, value);
         }
       }
     } catch (error) {
       Logger.log("Error processing address " + address + ": " + error.message);
       Logger.log("Response Data: " + error.response);
       //break;
     }
   } else {
     // Stop the loop if address is empty
     break;
   }
 }
}

function fetchTokenDataBlokiments(address, network) {
 var url = BLOKIMENTS_BASE_URL + "?network=" + network + "&address=" + address;
 var options = {
   'method': 'get',
   'headers': {
     'Accept': 'application/json',
     'X-API-Key': BLOKIMENTS_API_KEY
   },
   'muteHttpExceptions': true
 };
 // Get Blokiments data
 var response = UrlFetchApp.fetch(url, options);
 if (response.getResponseCode() !== 200) {
   throw new Error("Response from " + url + " is not 200. Response: " + response.getContentText())
 }
 // If response is valid, return parsed JSON
 return JSON.parse(response.getContentText())
}


function getFinalObject(blokiObj) {
 // Mapping from raw values the API returns, to what we want to display in spreadsheet


 return {
   // Basic Info
   network: blokiObj.network,
   pairAddress: blokiObj.pair_address,
   tokenAddress: blokiObj.token_address,
   tokenDecimals: blokiObj.token_decimals,
   tokenName: blokiObj.token_name,
   tokenSymbol: blokiObj.token_symbol,


   // Security
   isScam: blokiObj.is_scam,


   // Liquidity
   currentLiqudity: blokiObj.current_liquidity_usd,
   currentLockedLiqudityPers: blokiObj.current_locked_liquidity_pct !== null ? (blokiObj.current_locked_liquidity_pct * 100 + "%") : "",
   currentBurnedLiqudityPers: blokiObj.current_burned_liquidity_pct !== null ? (blokiObj.current_burned_liquidity_pct * 100 + "%") : "",


   // Market Cap
   currentCircMarketcap: blokiObj.current_circulating_marketcap,
   currentTotalMarketcap: blokiObj.current_total_marketcap,


   // Alpha
   currentAlphaVsBitcoin: blokiObj.current_alpha_vs_bitcoin,
   currentAlphaVsEth: blokiObj.current_alpha_vs_eth,
   currentAlphaVsSol: blokiObj.current_alpha_vs_sol,


   // Beta
   currentBetaVsBitcoin: blokiObj.current_beta_vs_bitcoin,
   currentBetaVsEth: blokiObj.current_beta_vs_eth,
   currentBetaVsSol: blokiObj.current_beta_vs_sol,


   // RSI
   currentRsi: blokiObj.current_rsi,


   // Moving Average
   currentMovigAverage: blokiObj.current_moving_average,


   // Price Change
   priceChange1h: blokiObj.price_change_1h !== null ? (blokiObj.price_change_1h + "%") : "",
   priceChange4h: blokiObj.price_change_4h !== null ? (blokiObj.price_change_4h + "%") : "",
   priceChange12h: blokiObj.price_change_12h !== null ? (blokiObj.price_change_12h + "%") : "",
   priceChange1d: blokiObj.price_change_1d !== null ? (blokiObj.price_change_1d + "%") : "",
   priceChange7d: blokiObj.price_change_7d !== null ? (blokiObj.price_change_7d + "%") : "",
   priceChange30d: blokiObj.price_change_30d !== null ? (blokiObj.price_change_30d + "%") : "",


   // Holders
   totalHolders: blokiObj.total_holders,
   holdersChange1d: blokiObj.holders_change_1d !== null ? (blokiObj.holders_change_1d + "%") : "",
   holdersChange7d: blokiObj.holders_change_7d !== null ? (blokiObj.holders_change_7d + "%") : "",
   holdersChange30d: blokiObj.holders_change_30d !== null ? (blokiObj.holders_change_30d + "%") : "",
   holdersTopSum1: blokiObj.holders_top1_sum * 100,
   holdersTopSum10: blokiObj.holders_top10_sum * 100,
   holdersTopSum20: blokiObj.holders_top20_sum * 100,
   holdersTopSum50: blokiObj.holders_top50_sum * 100,
   holdersTopSum100: blokiObj.holders_top100_sum * 100,
   holdersTopSum200: blokiObj.holders_top200_sum * 100,
   holdersHHI: blokiObj.holders_hhi_market_concentration,
   holdersMedianHolderRank: blokiObj.holders_median_holder_rank,
   holdersPctHeldByDeployer: blokiObj.holders_pct_hold_by_deployer !== null ? (blokiObj.holders_pct_hold_by_deployer * 100 + "%") : "",


   //topPercentageGraph: `\=SPARKLINE\(\{${holdersObj.topPercentage}\}; \{\"charttype\"\\\"bar\";\"max\"\\100\}\)`,
 }
}


function getDisplayValue(field) {
 // Mapping between our final object and the columns user sets
 var values = {
   // Basic Info
   NETWORK: "network",
   PAIR_ADDRESS: "pairAddress",
   TOKEN_ADDRESS: "tokenAddress",
   TOKEN_DECIMALS: "tokenDecimals",
   TOKEN_NAME: "tokenName",
   TICKER: "tokenSymbol",


   // Security
   SCAM: "isScam",


   // Liquidity
   LIQUIDITY_USD: "currentLiqudity",
   LOCKED_LIQUIDITY: "currentLockedLiqudityPers",
   BURNED_LIQUIDITY: "currentBurnedLiqudityPers",


   // Market Cap
   TOTAL_MARKET_CAP: "currentTotalMarketcap",
   CIRCULATING_MARKETCAP: "currentCircMarketcap",


   // Alpha
   BTC_ALPHA: "currentAlphaVsBitcoin",
   ETH_ALPHA: "currentAlphaVsEth",
   SOL_ALPHA: "currentAlphaVsSol",


   // Beta
   BTC_BETA: "currentBetaVsBitcoin",
   ETH_BETA: "currentBetaVsEth",
   SOL_BETA: "currentBetaVsSol",


   // RSI
   RSI: "currentRsi",


   // Moving Average
   MOVING_AVG: "currentMovigAverage",


   // Price Change
   PRICE_CHANGE_1H: "priceChange1h",
   PRICE_CHANGE_4H: "priceChange4h",
   PRICE_CHANGE_12H: "priceChange12h",
   PRICE_CHANGE_1D: "priceChange1d",
   PRICE_CHANGE_7D: "priceChange7d",
   PRICE_CHANGE_30D: "priceChange30d",


   // Holders
   HOLDERS: "totalHolders",
   HOLDER_CHANGE_1D: "holdersChange1d",
   HOLDER_CHANGE_7D: "holdersChange7d",
   HOLDER_CHANGE_30D: "holdersChange30d",
   HOLDERS_TOP1_SUM: "holdersTopSum1",
   HOLDERS_TOP10_SUM: "holdersTopSum10",
   HOLDERS_TOP20_SUM: "holdersTopSum20",
   HOLDERS_TOP50_SUM: "holdersTopSum50",
   HOLDERS_TOP100_SUM: "holdersTopSum100",
   HOLDERS_TOP200_SUM: "holdersTopSum200",
   HOLDERS_HHI: "holdersHHI",
   HOLDERS_MEDIAN_RANK: "holdersMedianHolderRank",
   DEPLOYER_HOLDS: "holdersPctHeldByDeployer",
 }
 var retVal = values[field]
 // Map the header values to the values used in the code
 if (retVal !== undefined) return retVal
 // Throw if header value is unknown - might be a typo
 throw new Error("Undefined display value field: " + field + ".")
}


function writeToCell(sheet, column, row, value) {
 sheet.getRange(column + "" + row).setValue(value)
}

Script Overview

The script will:

• Fetch Blokiments data for each address and network combination listed in the spreadsheet’s columns A and B using the Blokiments API

• Parse the JSON response from the response and extract the data

• Write the data to your active spreadsheet

1. 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:

Two things to consider:

• Each user will have a different API key; the key shown in this picture, “Pq1lfppsp10oapppeo141oeqro23reg0f_lags” is just for tutorial purposes; your key will be different.

• You must NOT remove quote characters ( ) surrounding your API key.

Now, the script in the App Scripts should look something like this:

1. Save the script by clicking on the Save icon at the top of the window:

1. 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.

1. The first time you run the script, you need to review permissions since this is an unverified script you just created:

1. Click on advanced options and then click go to Untitled project (unsafe)

1. 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:

1. 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.

Metrics descriptions

Full API docs -> https://api.blokiments.com/documentation

NETWORK

The blockchain on which this token and pool are found.

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.

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.