Making a Google Sheet that shows the Average Token Price

As someone who has been working in the Coordination Working Group at the DAO, there was something that kept recurring. Which was we making proposals and trying to attach the price of our token to it. As someone who is familiar with DAOs will know that a good number of proposals have expenditure attached to them, and most times you have to pay Service Providers, Contributors, etc. So it came up that it would be nice to have a way to get the average token price from the last 90 days, as we know the crypto market can be quite volatile. I figured out how to do it and have it automated, too, and that is what this article is about.

Get API From Transpose

We will be leveraging Transpose’s API to get the data from the blockchain. They have a free tier, which is more than enough for what we want to do. As the spreadsheet will only have to query the database once a day. Go to Transpose’s website and sign up for it. Go to your profile and create your API key. As it is a free tier account, you can only create one API key, and it has a rate limit of 1 request per second, which is more than what we need.

Create a Google Sheet

Next, we go to Google spreadsheet and create a new file. Name A1 Prices 90 Days and B1 Average Price. The naming doesn’t matter. After that, rename the Sheet tab you want to run this on to anything you want. For me, I will rename it to Token. Then on B2, do the AVERAGE formula for it to be the AVERAGE of A2:A91 (=AVERAGE(A2:A91)).

Apps Script

Navigate to Extensions > Apps Script. In the editor, we create a function which wraps everything we will be doing.

function getPrices() {}

Define the API Endpoint

This line sets the URL for the Transpose SQL API endpoint. This is where we'll send our query to retrieve historical token prices from the Ethereum blockchain. The Transpose API supports SQL-style queries to fetch blockchain data in a structured way.

  const url = `https://api.transpose.io/sql`;

Prepare the SQL Query and Parameters

This block builds the query payload. It does two things:

• Constructs a SQL statement to get the latest 90 prices for a specific token.

• Substitutes the token address using a parameterized placeholder ({{token_address}}), making the query safe and flexible.

We then stringify the JSON object so it can be transmitted over HTTP.

const payload = JSON.stringify({
      sql: `SELECT price
        FROM ethereum.token_prices etp
        WHERE etp.token_address = '{{token_address}}'
        ORDER BY timestamp DESC LIMIT 90`,
      parameters: {
          token_address: '0x81f8f0bb1cb2a06649e51913a151f0e7ef6fa321'
      }
  });

Set Up HTTP Headers

Here, we configure the HTTP headers required by the API:

• 'Content-Type': 'application/json' tells the server we're sending JSON data.

• 'X-API-KEY' authenticates our request using a personal or project-specific Transpose API key.

  const headers = {
          'Content-Type': 'application/json',
          'X-API-KEY': 'YOUR_API_KEY'
      }

Define Request Options

This block organizes everything we need to make the POST request:

• method: HTTP method (POST)

• contentType: Ensures data is sent as JSON

• headers: Contains API key and content type

• payload: The SQL query we defined earlier

  const options = {
    method: 'post',
    contentType: 'application/json',
    headers,
    payload
  }

Send the API Request

This line executes the POST request to the Transpose API using Google Apps Script’s UrlFetchApp.fetch() method. It sends all the options we prepared earlier and captures the server’s response.

const response = UrlFetchApp.fetch(url, options);

Parse the API Response

Once we get the raw response, we convert it from a JSON string to a usable object using JSON.parse(). Then, we extract only the price values into an array using map(). This gives us a simple array like [1.01, 1.03, 1.05, ...].

  const jsonResponse = JSON.parse(response.getContentText());
  const prices = jsonResponse.results.map(result => result.price)

Connect to the Google Sheet

This line opens the active Google Spreadsheet and selects the tab (sheet) named "Token". That’s where we’ll write our data. Make sure this sheet exists in your Google Sheet before running the script.

 const sheet = SpreadsheetApp.getActive().getSheetByName('Token')

Clear Old Data

Before adding new data, this clears the price data range in column A (rows 2 to 91). It ensures you're not mixing new prices with old ones.

sheet.getRange('A2:A91').clearContent()

Write Prices to the Sheet

This loop inserts each price from the prices array to the correct row, starting from A2.

  for (let i = 0; i < prices.length; i++) {
  sheet.getRange(i + 2, 1).setValue(prices[i]);
  }

Full script

function getPrices() {
  const url = `https://api.transpose.io/sql`;

  const payload = JSON.stringify({
      sql: `SELECT price
        FROM ethereum.token_prices etp
        WHERE etp.token_address = '{{token_address}}'
        ORDER BY timestamp DESC LIMIT 90`,
      parameters: {
          token_address: 'ANY_ETHEREUM_TOKEN_ADDRESS'
      }
  });

  const headers = {
          'Content-Type': 'application/json',
          'X-API-KEY': 'YOUR_API_KEY'
      }

  const options = {
    method: 'post',
    contentType: 'application/json',
    headers,
    payload
  }

  const response = UrlFetchApp.fetch(url, options);

  const jsonResponse = JSON.parse(response.getContentText());
  const prices = jsonResponse.results.map(result => result.price)

  const sheet = SpreadsheetApp.getActive().getSheetByName('Token')

  sheet.getRange('A2:A180').clearContent()

  for (let i = 0; i < prices.length; i++) {
  sheet.getRange(i + 2, 1).setValue(prices[i]);
  }
}

Automating it

After adding the script, save it and click on run. Then go to the Triggers tab on the Apps Script extension. Add a trigger and let it have this configuration. It will automatically detect the getPrices() function we used. Change the event source to be time-driven; that way, it updates at a specific time. Then save.

Conclusion

This script fetches recent Ethereum token prices using a SQL-based blockchain API and logs them into a Google Sheet. It's great for building dashboards, monitoring token performance, or automating blockchain analytics.