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

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

• 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

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

1. For the script to run, you also need an API Key. There are two options to get it:

   1. Via the Blokiments page for a free trial (lower API limits)

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

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.

Option 2: Registration on RapidAPI (PRO verison)

You can skip this step for now. Return here when more API calls are needed.

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

1. The UI is pretty straightforward. After logging in, on the link above, you can select the plan:

Select a plan that suits you.

1. Please input your billing information, and don’t forget to like and rate our API.

2. Change your API key and USE_PRO_API Pro API (Rapidapi key).

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.

const API_KEY = "API_KEY"
const USE_PRO_API = false


//  _______   ______   .__   __.  __.___________.   .___________.  ______    __    __    ______  __    __  
//  |       \ /  __  \  |  \ |  | (_ )           |   |           | /  __  \  |  |  |  |  /      ||  |  |  |
//  |  .--.  |  |  |  | |   \|  |  |/`---|  |----`   `---|  |----`|  |  |  | |  |  |  | |  ,----'|  |__|  |
//  |  |  |  |  |  |  | |  . `  |        |  |            |  |     |  |  |  | |  |  |  | |  |     |   __   |
//  |  '--'  |  `--'  | |  |\   |        |  |            |  |     |  `--'  | |  `--'  | |  `----.|  |  |  |
//  |_______/ \______/  |__| \__|        |__|            |__|      \______/   \______/   \______||__|  |__|
//                                                                                                        
//  .______    _______  __        ______   ____    __    ____
//  |   _  \  |   ____||  |      /  __  \  \   \  /  \  /   /
//  |  |_)  | |  |__   |  |     |  |  |  |  \   \/    \/   /  
//  |   _  <  |   __|  |  |     |  |  |  |   \            /  
//  |  |_)  | |  |____ |  `----.|  `--'  |    \    /\    /    
//  |______/  |_______||_______| \______/      \__/  \__/    
                                                         
// Script for automated data fetching using Blokiments API
// Version: 2.0.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_Sheet",
    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);
 
 
  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,
  };
}


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`])
    ])
  );
}

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:

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:

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. In the lower right corner of the sheet, there will be occasional updates on the progress.

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.

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:

1. Go into the code

2. Change USE_NAME in the 28th line to true (replace false with true )

3. Change NAME in the 29th line to the name of your sheet.

Original settings

New settings

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 to false.

Old values.

New values.

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.

New settings.

Metrics descriptions

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

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.