Google Sheets App Script to fetch EVM contract and token infos as available from some block explorer API

EVMSheet.md

Template/Example sheet can be found here. Contributions welcome. Fork this gist and edit it then send it back or something I guess. (Why are gists forkable? Has anyone ever really used this?)

////////////////////////////////////////////////////////////////////////////////
// Contract Info Fetching & Updating Script for Google Sheets
// -----------------------------------------------------------
// This script helps fetch contract metadata (Name, ABI, Symbol, Decimals)
// for each row in a Google Sheet that has a contract address.
//
// Main Functions:
//  1) updateContractInfo()       – Only fills in missing Name/ABI
//  2) updateContractInfoForce()  – Overwrites Name/ABI for all rows
//  3) sortSheet()                – Sorts by Project Name (A), then Contract Name (C)
//  4) removeDuplicateRows()      – Removes duplicates based on (A,B)
//  5) onOpen()                   – Adds a custom menu in Google Sheets
//  6) evmCall() / tryStandardEvmInfo() 
//     – Fallback calls to a Sei-compatible EVM RPC if SeiTrace doesn’t have the data
// 
// Usage:
//  1. Open your spreadsheet
//  2. Go to Extensions -> Apps Script
//  3. Paste in this code (or add it to existing code)
//  4. Reload your sheet
//  5. Use the new "Custom Scripts" menu
////////////////////////////////////////////////////////////////////////////////


/**
 * Fetches the ABI from SeiTrace for a given contract address.
 * If it fails, returns "Invalid contract address."
 * 
 * @param {string} contractAddress - The on-chain contract address 
 *                                   (e.g., 0x..., case-insensitive).
 * @return {string} The ABI as a JSON string, or "Invalid contract address" on error.
 */
function fetchAbi(contractAddress) {
  if (!contractAddress) {
    Logger.log("Empty contract address provided to fetchAbi.");
    return "";
  }
  
  var url = "https://seitrace.com/pacific-1/api/v2/smart-contracts/" + contractAddress.toLowerCase();
  try {
    Logger.log("Fetching ABI for contract: " + contractAddress);
    var response = UrlFetchApp.fetch(url); // Standard URL fetch
    var jsonData = JSON.parse(response.getContentText());
    Logger.log("Successfully fetched ABI for contract: " + contractAddress);
    return JSON.stringify(jsonData.abi);
  } catch (e) {
    Logger.log("Error fetching ABI for contract: " + contractAddress + " - " + e.message);
    return "Invalid contract address";
  }
}

/**
 * Fetches the Name from SeiTrace for a given contract address.
 * If not found or the response is empty, attempts an EVM fallback call 
 * with tryStandardEvmInfo().
 * 
 * @param {string} contractAddress - The on-chain contract address (0x...).
 * @return {string} The token or contract name, or "Unknown"/"Invalid contract address".
 */
function fetchName(contractAddress) {
  if (!contractAddress) {
    Logger.log("Empty contract address provided to fetchName.");
    return "";
  }

  var url = "https://seitrace.com/pacific-1/api/v2/smart-contracts/" + contractAddress.toLowerCase();
  try {
    Logger.log("Fetching Name for contract: " + contractAddress);
    var response = UrlFetchApp.fetch(url);
    var jsonData = JSON.parse(response.getContentText());
    Logger.log("Successfully fetched Name for contract: " + contractAddress);

    // If SeiTrace returns a valid non-empty name, use it
    if (jsonData.name && jsonData.name.trim() !== "") {
      return jsonData.name;
    } else {
      // Otherwise, fallback to EVM call
      Logger.log("No name found in SeiTrace for " + contractAddress + ". Trying EVM fallback...");
      var evmInfo = tryStandardEvmInfo(contractAddress);

      if (evmInfo && evmInfo.name) {
        Logger.log("Recovered name from EVM fallback: " + evmInfo.name);
        return evmInfo.name;
      } else {
        return "Unknown";
      }
    }
  } catch (e) {
    Logger.log("Error fetching Name for contract: " + contractAddress + " - " + e.message);
    return "Invalid contract address";
  }
}

/**
 * Attempts to update only missing contract Name (Column C) and ABI (Column D) 
 * for each row that has a contract address in Column B. 
 * Skips rows if Column B is empty or blank.
 * 
 * Also sets "NO DATA AVAILABLE" if neither SeiTrace nor the EVM fallback can 
 * provide any contract info or ABI data.
 */
function updateContractInfo() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var updatedCount = 0;

  for (var i = 2; i <= lastRow; i++) {
    // B = Contract Address
    var contractAddress = sheet.getRange(i, 2).getValue();
    
    // Skip if no address
    if (!contractAddress || contractAddress.toString().trim() === "") {
      continue;
    }

    // Force lowercase
    contractAddress = contractAddress.toLowerCase();
    sheet.getRange(i, 2).setValue(contractAddress);

    var contractNameCell = sheet.getRange(i, 3); // C
    var abiCell = sheet.getRange(i, 4);          // D

    // --- Update Contract Name (if missing) ---
    if (!contractNameCell.getValue() || contractNameCell.getValue().toString().trim() === "") {
      // Attempt to fetch from SeiTrace first
      var fallback = null;
      var name = fetchName(contractAddress);
      var symbol = "";
      var decimals = null;

      // If no luck or "Unknown", try EVM fallback
      if (!name || name === "Unknown") {
        fallback = tryStandardEvmInfo(contractAddress);
        if (fallback && fallback.name) name = fallback.name;
      }

      // If fallback found symbol/decimals, gather them
      if (fallback) {
        if (fallback.symbol) symbol = fallback.symbol;
        if (fallback.decimals !== null && fallback.decimals !== undefined) {
          decimals = fallback.decimals;
        }
      }

      // Prepare display label e.g. "MyToken (SYM, decimals:18)"
      var label = name;
      if (symbol || decimals !== null) {
        var extras = [];
        if (symbol) extras.push(symbol);
        if (decimals !== null) extras.push("decimals:" + decimals);
        label += " (" + extras.join(", ") + ")";
      }

      // If label is still empty or "Unknown", mark NO DATA
      if (!label || label === "Unknown") {
        label = "NO DATA AVAILABLE";
      }
      // Additional check in case name is 'Unknown'
      if (!name || name === "Unknown") {
        label = "NO DATA AVAILABLE";
      }

      contractNameCell.setValue(label);
      updatedCount++;

      // Sleep 300ms to avoid rate-limits
      Utilities.sleep(300);
    }

    // --- Update ABI (if missing) ---
    if (!abiCell.getValue() || abiCell.getValue().toString().trim() === "") {
      var abi = fetchAbi(contractAddress);
      // Mark "NO DATA AVAILABLE" if invalid or unknown
      if (!abi || abi === "Invalid contract address") {
        abi = "NO DATA AVAILABLE";
      }

      // Double check in case it's still invalid 
      if (!abi || abi === "Invalid contract address") {
        abi = "NO DATA AVAILABLE";
      }

      abiCell.setValue(abi);
      updatedCount++;
      Utilities.sleep(300);
    }
  }

  // Show results to the user via a UI alert and console log
  SpreadsheetApp.getUi().alert("Updated " + updatedCount + " missing contract fields.");
  Logger.log("Updated " + updatedCount + " missing contract fields.");
}

/**
 * Force update for every row (2..lastRow), overwriting the Name (C) and ABI (D),
 * ignoring existing data. 
 * 
 * It also clears any "NO DATA AVAILABLE" cells before re-fetching, 
 * so it can attempt to get new data for those addresses.
 */
function updateContractInfoForce() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var updatedCount = 0;

  // Clear old NO DATA AVAILABLE markers before forcing updates
  for (var r = 2; r <= lastRow; r++) {
    var nameCell = sheet.getRange(r, 3); // C
    var abiCell = sheet.getRange(r, 4);  // D

    if (nameCell.getValue() === "NO DATA AVAILABLE") {
      nameCell.setValue("");
    }
    if (abiCell.getValue() === "NO DATA AVAILABLE") {
      abiCell.setValue("");
    }
  }

  // Now process each row, forcing re-fetch
  for (var i = 2; i <= lastRow; i++) {
    var contractAddress = sheet.getRange(i, 2).getValue();
    if (!contractAddress || contractAddress.toString().trim() === "") {
      continue;
    }

    // Lowercase + set in column B
    contractAddress = contractAddress.toLowerCase();
    sheet.getRange(i, 2).setValue(contractAddress);

    // Attempt to fetch from SeiTrace or EVM fallback
    var fallback = null;
    var name = fetchName(contractAddress);
    var symbol = "";
    var decimals = null;

    if (!name || name === "Unknown") {
      fallback = tryStandardEvmInfo(contractAddress);
      if (fallback && fallback.name) name = fallback.name;
    }

    if (fallback) {
      if (fallback.symbol) symbol = fallback.symbol;
      if (fallback.decimals !== null && fallback.decimals !== undefined) {
        decimals = fallback.decimals;
      }
    }

    var label = name;
    if (symbol || decimals !== null) {
      var extras = [];
      if (symbol) extras.push(symbol);
      if (decimals !== null) extras.push("decimals:" + decimals);
      label += " (" + extras.join(", ") + ")";
    }

    if (!label || label === "Unknown") {
      label = "NO DATA AVAILABLE";
    }

    // Overwrite column C (Name)
    sheet.getRange(i, 3).setValue(label);
    updatedCount++;
    Utilities.sleep(300);

    // Overwrite column D (ABI)
    var abi = fetchAbi(contractAddress);
    if (!abi || abi === "Invalid contract address") {
      abi = "NO DATA AVAILABLE";
    }
    sheet.getRange(i, 4).setValue(abi);
    updatedCount++;
    Utilities.sleep(300);
  }

  SpreadsheetApp.getUi().alert("Force updated " + updatedCount + " contract fields.");
  Logger.log("Force updated " + updatedCount + " contract fields.");
}

/**
 * Sorts the active sheet by Column A (Project Name), then by Column C (Contract Name).
 * It only sorts if there's data beyond the first row (header).
 */
function sortSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();

  // Only sort if there's actual data
  if (lastRow > 1) {
    // Sort range from row 2 downward, across all columns
    var range = sheet.getRange(2, 1, lastRow - 1, lastCol);
    range.sort([
      { column: 1, ascending: true },  // Column A: Project Name
      { column: 3, ascending: true }   // Column C: Contract Name
    ]);
  }
}

/**
 * Removes duplicate rows based on columns A (Project Name) and B (Contract Address).
 * For each group of duplicates, keeps only the row(s) with the largest number of 
 * non-empty cells.
 */
function removeDuplicateRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();

  if (lastRow < 2) {
    return; // No data
  }

  // Fetch everything (headers included)
  var data = sheet.getRange(1, 1, lastRow, lastCol).getValues();

  // Helper: count how many non-empty cells are in a row
  function countNonEmpty(rowArr) {
    var count = 0;
    for (var i = 0; i < rowArr.length; i++) {
      if (rowArr[i] !== "" && rowArr[i] != null) {
        count++;
      }
    }
    return count;
  }

  // We'll store the best row(s) for each (ProjectName, ContractAddress)
  var bestRowsByAB = {};

  // data[0] is the header row
  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    var aVal = row[0]; // A = Project Name
    var bVal = row[1]; // B = Contract Address

    var key = aVal + "___" + bVal;
    var filledCount = countNonEmpty(row);
    var rowIndexInSheet = i + 1; // Because i=0 is the header in data[]

    if (!bestRowsByAB[key]) {
      bestRowsByAB[key] = [{ rowIndex: rowIndexInSheet, filledCount: filledCount }];
    } else {
      var currentBest = bestRowsByAB[key];
      // Find the highest 'filledCount' so far
      var maxCount = currentBest[0].filledCount;
      for (var j = 1; j < currentBest.length; j++) {
        if (currentBest[j].filledCount > maxCount) {
          maxCount = currentBest[j].filledCount;
        }
      }

      if (filledCount > maxCount) {
        // This row is strictly better, replace
        bestRowsByAB[key] = [{ rowIndex: rowIndexInSheet, filledCount: filledCount }];
      } else if (filledCount === maxCount) {
        // Tie: keep both
        bestRowsByAB[key].push({ rowIndex: rowIndexInSheet, filledCount: filledCount });
      }
    }
  }

  // Collect all row indices that we want to keep
  var keepers = {};
  for (var k in bestRowsByAB) {
    bestRowsByAB[k].forEach(function(obj) {
      keepers[obj.rowIndex] = true;
    });
  }

  // Remove rows from the bottom up if they're not in keepers
  for (var rowToCheck = lastRow; rowToCheck >= 2; rowToCheck--) {
    if (!keepers[rowToCheck]) {
      sheet.deleteRow(rowToCheck);
    }
  }
}

/**
 * Make a direct 'eth_call' to the contract, using a known methodId (4-byte signature).
 * For instance, 0x06fdde03 is 'name()', 0x95d89b41 is 'symbol()', 0x313ce567 is 'decimals()'.
 * 
 * Appends ?x-apikey=... so we can use the Sei EVM RPC.
 * 
 * @param {string} contractAddress - EVM-compatible contract address
 * @param {string} methodId        - The first 4 bytes of the method signature, e.g. '0x06fdde03'
 * @return {string|null}           - Hex data returned by the call, or null if it fails
 */
function evmCall(contractAddress, methodId) {
  var payload = {
    jsonrpc: "2.0",
    method: "eth_call",
    params: [
      {
        to: contractAddress,
        data: methodId
      },
      "latest"
    ],
    id: 1
  };

  var options = {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(payload)
  };

  try {
    var response = UrlFetchApp.fetch("https://evm-rpc.sei-apis.com", options);
    var result = JSON.parse(response.getContentText()).result;
    // If empty or just '0x', no data returned
    if (!result || result === "0x") return null;
    return result;
  } catch (e) {
    Logger.log("EVM call error for " + contractAddress + ": " + e.message);
    return null;
  }
}

/**
 * Decodes a hex-encoded UTF-8 string. This is useful for contract calls
 * like 'name()' or 'symbol()', which return the string in hex.
 * 
 * @param {string} hex - The hex string (e.g., '0x123abc...') to decode to UTF-8
 * @return {string}    - The decoded string, or '(decode error)' on failure
 */
function hexToUtf8(hex) {
  try {
    // Strip 0x prefix and trailing zeroes
    hex = hex.replace(/^0x/, "").replace(/00+$/, "");
    // Each 2 hex chars -> one byte -> convert to '%xx' for decodeURIComponent
    return decodeURIComponent(hex.match(/.{1,2}/g).map(function(byte) {
      return "%" + byte;
    }).join(""));
  } catch (e) {
    return "(decode error)";
  }
}

/**
 * Attempts calls to 'name()', 'symbol()', and 'decimals()' for standard ERC-20 or ERC-721 tokens.
 * 
 * @param {string} contract - The EVM contract address.
 * @return {Object}         - { name, symbol, decimals } or empty values if unknown
 */
function tryStandardEvmInfo(contract) {
  var nameHex = evmCall(contract, "0x06fdde03");   // name()
  var symbolHex = evmCall(contract, "0x95d89b41"); // symbol()
  var decimalsHex = evmCall(contract, "0x313ce567");// decimals()

  var name = nameHex ? hexToUtf8(nameHex) : null;
  var symbol = symbolHex ? hexToUtf8(symbolHex) : null;
  var decimals = decimalsHex ? parseInt(decimalsHex, 16) : null;

  return { name: name, symbol: symbol, decimals: decimals };
}

/**
 * Runs automatically when the Spreadsheet opens. 
 * Adds our custom menu so users can run these functions from the Sheets UI.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Scripts')
    .addItem('Update Contract Info (Missing Only)', 'updateContractInfo')
    .addItem('Force Update Contract Info', 'updateContractInfoForce')
    .addItem('Sort Sheet (A then C)', 'sortSheet')
    .addItem('Remove Duplicate Rows', 'removeDuplicateRows')
    .addItem('Normalize Project Contracts', 'normalizeFromSourceToAppsContracts')
    .addToUi();
}

Additional Notes

1. Skipping Cells

   • Rows that have "NO DATA AVAILABLE" or any non-empty values in Name (C) or ABI (D) are skipped by updateContractInfo() on future runs. This avoids unnecessary repeated calls.

2. Force Update

   • updateContractInfoForce() clears any "NO DATA AVAILABLE" from Columns C & D before re-fetching data, so it can try new queries in case the issue was temporary.

3. Rate Limiting

   • There is a Utilities.sleep(300) call after each fetch to avoid hitting rate limits. If the script times out (Google Apps Script has a 6-minute limit per execution), simply re-run it. It will skip any rows already filled in.

4. Sorting & De-Duplicating

   • sortSheet() sorts by column A (Project Name), then by column C (Contract Name).
   • removeDuplicateRows() removes extra rows that share the same (A,B) but have fewer filled cells.

5. EVM Fallback

   • If SeiTrace fails to provide a name, the script calls tryStandardEvmInfo() and attemps to retrieve name, symbol, and decimals from the contract’s ERC interface using a standard EVM RPC endpoint.