Google Sheets ERC-20 Token Balance: A Custom Function with Apps Script

Somebody in finance or ops asks what the treasury wallet holds today, and the answer lives in a block explorer tab that nobody exports. This tutorial puts the number in a spreadsheet cell: type =ERC20_BALANCE(A2, B2, C2) and get 39.192124 for the USDC in a wallet, on Ethereum, Base, BNB Chain or Polygon.

Prerequisites

• A Google account and a spreadsheet you can edit.
• An evmquery API key. The free key is enough.
• A token contract address and a wallet address. This guide uses USDC on Ethereum (0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48) and vitalik.eth’s address (0xd8dA6BF26964aF9D7eEd9e03E53415D37aA96045).

No programming experience is needed beyond copying text into a box. The code is short, and I explain the lines that are not obvious.

Setting up the sheet and opening the script editor

Create a spreadsheet and rename the first tab to Holdings. Put these headers in row 1: Token, Wallet, Chain, Balance. In row 2, paste the USDC address in A2, the wallet address in B2, and evm_ethereum in C2. The other chain values are evm_base, evm_bnb_mainnet and evm_polygon.

Now open the script editor: in the menu bar click Extensions, then Apps Script. A new tab opens with a file called Code.gs containing an empty myFunction. Delete everything in it. You will paste code here in the next steps. A script opened this way is bound to the spreadsheet, which matters later for the snapshot function.

Storing the API key where cells cannot see it

Do not paste the key into a cell or into the code. Anyone who can view the sheet can read cells, and the code gets copied around. Apps Script has a place for secrets: in the script editor click the gear icon (Project Settings), scroll to Script properties, click Add script property, enter EVMQUERY_API_KEY as the property and your key as the value, then Save script properties.

Anyone with edit access to the spreadsheet can open the script and read this value, so share edit rights accordingly. Viewers cannot.

The request that reads the balance

Paste this at the top of Code.gs.

const API_URL = "https://api.evmquery.com/api/v1/query";
const CACHE_SECONDS = 600;

function fetchBalance_(token, wallet, chain) {
  const apiKey = PropertiesService.getScriptProperties().getProperty("EVMQUERY_API_KEY");
  if (!apiKey) {
    throw new Error("Add EVMQUERY_API_KEY under Project Settings > Script properties");
  }

  const response = UrlFetchApp.fetch(API_URL, {
    method: "post",
    contentType: "application/json",
    headers: { "x-api-key": apiKey },
    muteHttpExceptions: true,
    payload: JSON.stringify({
      chain: chain,
      schema: {
        contracts: { token: { address: token } },
        context: { wallet: "sol_address" },
      },
      context: { wallet: wallet },
      expression: "formatUnits(token.balanceOf(wallet), token.decimals())",
    }),
  });

  const code = response.getResponseCode();
  let body;
  try {
    body = JSON.parse(response.getContentText());
  } catch (e) {
    throw new Error("HTTP " + code + ": the response was not JSON");
  }
  if (code !== 200) {
    throw new Error(body.errors ? body.errors[0].detail : "HTTP " + code);
  }
  return body.result.value;
}

UrlFetchApp.fetch is Apps Script’s HTTP client. The schema block names the token contract token and declares wallet as an address, and the expression asks for balanceOf divided by decimals(), so USDC (6 decimals) and an 18-decimal token both come back as readable numbers. There is no ABI to paste anywhere. The same request in raw JavaScript would need an ABI, an RPC provider and a Multicall3 call to batch the two reads.

muteHttpExceptions: true is there because by default UrlFetchApp throws on any non-200 reply and hides the response body. With it on, the script can read the API’s own error message and show that in the cell instead of a generic failure. The JSON.parse sits in a try so that an HTML page from a proxy or a gateway timeout in front of the API shows up as a clear HTTP 502 message rather than a parse error. The trailing underscore in fetchBalance_ is an Apps Script convention: Apps Script treats functions ending in _ as private, which keeps the helper out of the way of the one you call from cells.

Before using it in Sheets I ran the same body with curl. The result for the wallet above, when this was written:

{"result":{"value":39.192124,"type":"double"},"meta":{"blockNumber":"26378269","totalCalls":2,"totalRounds":1},"performance":{"latencyMs":359},"units":{"consumed":3}}

value is a plain number, so the cell gets a number and not text. That means =SUM() and charts work on it directly.

The custom function

Paste this below the first function.

function ERC20_BALANCE(token, wallet, chain) {
  chain = chain || "evm_ethereum";
  const address = /^0x[0-9a-fA-F]{40}$/;
  if (!address.test(token) || !address.test(wallet)) {
    throw new Error("token and wallet must be 0x addresses with 40 hex characters");
  }

  const cache = CacheService.getScriptCache();
  const key = ["erc20", chain, token, wallet].join(":").toLowerCase();
  const cached = cache.get(key);
  if (cached !== null) return Number(cached);

  const value = fetchBalance_(token, wallet, chain);
  cache.put(key, String(value), CACHE_SECONDS);
  return value;
}

The address check at the top catches a blank cell or a half-pasted address before any network call happens. Pass 0x addresses for both the token and the wallet.

The cache key joins chain, token and wallet and lowercases them, so 0xA0b8... and 0xa0b8... share one entry. CacheService.getScriptCache() is shared by everyone using the script. Entries last up to 21,600 seconds (6 hours) per Google’s CacheService reference, and each value can be up to 100 KB. Ours is a few bytes, and 600 seconds means a balance can be at most ten minutes old.

Save with the disk icon, go back to the sheet and type =ERC20_BALANCE(A2, B2, C2) in D2. The cell shows Loading... for a moment and then 39.192124. Fill it down for more rows. If you leave out the third argument it defaults to Ethereum.

What Google limits, and what that means here

• 30 seconds per call. The custom functions guide says a call “must return within 30 seconds”, otherwise the cell shows #ERROR!. Calls in my tests mostly finished well under a second, so there is room, but you should not chain several reads in one cell.
• Which services work. UrlFetchApp, PropertiesService and CacheService are all on the list of services custom functions may call, and custom functions never ask the user to authorize. Services that need authorization are refused. The same page calls the cache “not particularly useful in custom functions”, and I see why: Sheets can start many cells at once, so on a first fill, many cells may all miss the cache together. It pays off on later recalculations, which is what the next point is about.
• When it recalculates. The function reruns when a cell you passed in as an argument changes, or when you edit the formula. Google notes that volatile functions like NOW() are not allowed as arguments, and a function depending on one shows Loading... forever. So you cannot build a refresh-every-minute cell this way. For scheduled refreshes, use the trigger below.
• Request quota. The quotas page lists 20,000 UrlFetchApp calls per day for consumer accounts and 100,000 for Google Workspace accounts. A sheet with 300 balance cells that is fully recalculated every few minutes would burn through the consumer quota, which is the reason the cache exists.

A daily snapshot row

A formula shows the balance now, and finance usually wants to know what it was on the 1st. Make a second tab called Snapshots with the headers Time, Chain, Token, Wallet, Balance, then paste this function below the others.

function snapshot() {
  const ss = SpreadsheetApp.getActive();
  const holdings = ss.getSheetByName("Holdings");
  const log = ss.getSheetByName("Snapshots");
  if (holdings.getLastRow() < 2) return;
  const rows = holdings.getRange(2, 1, holdings.getLastRow() - 1, 3).getValues();
  const now = new Date();

  const out = rows.map(function (row) {
    const token = row[0], wallet = row[1], chain = row[2] || "evm_ethereum";
    try {
      return [now, chain, token, wallet, fetchBalance_(token, wallet, chain)];
    } catch (e) {
      return [now, chain, token, wallet, "ERROR: " + e.message];
    }
  });

  log.getRange(log.getLastRow() + 1, 1, out.length, 5).setValues(out);
}

It reads rows 2 and down of Holdings, fetches each balance with fetchBalance_ (skipping the cache, because a snapshot should be fresh), and appends everything in one setValues call. A bad row writes ERROR: ... into the Balance column and the loop moves on, so one typo does not lose the other rows. Custom functions cannot write to other cells, but a trigger-run function is an ordinary script and can.

Run it once by hand to grant permissions: choose snapshot in the function dropdown above the editor and click Run. Google shows an authorization dialog because this function reads the spreadsheet and calls an external service. Click through Review permissions, pick your account, and on the “Google hasn’t verified this app” screen choose Advanced and Go to (project name). It is your own script, so this warning is expected.

Then schedule it: click the clock icon (Triggers) in the left sidebar, Add Trigger, choose snapshot, event source Time-driven, type Day timer, a time window, and Save. The quotas page lists a 6-minute limit per execution, 90 minutes of total trigger runtime per day on consumer accounts (6 hours on Workspace) and 20 triggers per user per script. A few dozen holdings finish in seconds.

When a cell shows an error or a zero

An error in a cell. The message from the API is passed through, so read it first. Check the token address and the Chain column together, because a contract address only means something on the chain it was deployed to. The chain values are evm_ethereum, evm_base, evm_bnb_mainnet and evm_polygon.

A zero is a real answer. USDC on Base for an address that holds none returned 0, not an error. An empty-looking cell is not necessarily a bug, so confirm the wallet and chain before assuming the formula is broken.

Next steps

• Scan many wallets in a single request from code: Multi-wallet ERC-20 balance scanning in TypeScript.
• Get notified when a balance changes instead of looking at a sheet: Wallet balance change alerts in n8n.
• Other no-code and low-code setups: evmquery for automation.
• Look up any token before adding it to the sheet: ERC-20 inspector.