Stream Blockchain Data to Google Sheets

Overview

Spreadsheets are among the most widely used data-storing solutions due to their ease of use and accessibility. Google Sheets, in particular, is a popular spreadsheet platform that allows users to not only store and retrieve data but also manipulate, transform, and enhance that data without needing to be experienced developers. With its built-in formulas, functions, and intuitive tools, users can perform complex calculations, analyze data, and even create visualizations like charts and graphs.

In this Function example, we will set up a blockchain data stream to Google Sheets, using QuickNode Streams and Functions.

Sample Function

We will establish the data stream using QuickNode Streams. Streams offer various popular data solutions such as Webhook, Postgres, AWS S3, Snowflake, etc. However, there are instances where you may require a specific or custom destination. With Functions, we can create blockchain serverless functions that send data to custom destinations. Furthermore, Streams ensures guaranteed data delivery with retries on delivery failures. We have implemented error handling using try-catch blocks in our Function's code. This means that if something goes wrong with our Google Sheets or Sheets API, our Stream will attempt redelivery via Function, and any errors will also be logged on the Streams dashboard.

We'll be using Google APIs Node.js client and dotenv for storing keys, so let's first install it:

npm i googleapis

Create a .env file to store the service account key file and Google Sheet ID.

Learn how to get service account key JSON file and Google Sheet's ID.

To get service account key JSON 👇

Go to the Google Cloud Console.
Create a new project or select an existing one.
Enable the Google Sheets API:
- In the sidebar, click on APIs & Services > Library
- Search for Google Sheets API
- Click on it and then click Enable
Create a service account:
- In the sidebar, click on APIs & Services > Credentials
- Click Create Credentials at the top of the page
- Select Service account
- Fill in the details for the service account and click Create
- You can skip adding roles in the next step (click Continue)
- Click Done
Generate the JSON key:
- In the Credentials page, find your newly created service account
- Click on the service account name to open its details
- Go to the Keys tab
- Click Add Key > Create new key
- Choose JSON as the key type
- Click Create

Your Google Sheet's ID is an alphanumeric key present in your Google Sheet's URL. For example, in the URL https://docs.google.com/spreadsheets/d/1herNXsSNbg_6qdt8DPE3_iQBHpeXFU2EknHnhBe6eEE/edit 1herNXsSNbg_6qdt8DPE3_iQBHpeXFU2EknHnhBe6eEE is the ID.

GOOGLE_APPLICATION_CREDENTIALS='SERVICE_ACCOUNT_KEY_JSON'
SPREADSHEET_ID=GOOGLE_SHEET_ID

Replace SERVICE_ACCOUNT_KEY_JSON with the content of your service account key file and GOOGLE_SHEET_ID with your ID which you got earlier.

Now, create an index.js and paste the following code in it:

require('dotenv').config();
const { google } = require('googleapis');

// Initialize the Google Sheets API client
function initGoogleSheetsApi() {
    try {
        const credentials = JSON.parse(process.env.GOOGLE_APPLICATION_CREDENTIALS);
        console.log('Service Account Email:', credentials.client_email);
        const auth = new google.auth.JWT(
            credentials.client_email,
            null,
            credentials.private_key,
            ['https://www.googleapis.com/auth/spreadsheets']
        );
        return google.sheets({ version: 'v4', auth });
    } catch (error) {
        console.error('Error initializing Google Sheets API:', error);
        throw error;
    }
}

// Add headers to the Google Sheet
async function addHeadersToSheet(sheets, spreadsheetId) {
    const headers = ['Dataset', 'Network', 'Amount', 'Block Number', 'Spender', 'Token', 'Transaction Hash', 'Wallet'];
    const request = {
        spreadsheetId: spreadsheetId,
        range: 'Sheet1!A1:H1',
        valueInputOption: 'RAW',
        resource: { values: [headers] },
    };

    try {
        const response = await sheets.spreadsheets.values.update(request);
        console.log(`${response.data.updatedCells} cells updated with headers.`);
        return response.data.updatedCells;
    } catch (err) {
        console.error('Error in addHeadersToSheet:', err);
        if (err.response) {
            console.error('Response data:', err.response.data);
            console.error('Response status:', err.response.status);
        }
        throw err;
    }
}

// Upload data to Google Sheets
async function uploadToGoogleSheets(sheets, values) {
    const spreadsheetId = process.env.SPREADSHEET_ID;
    console.log('Spreadsheet ID:', spreadsheetId);

    try {
        // First, add headers
        await addHeadersToSheet(sheets, spreadsheetId);

        // Then, append the data
        const request = {
            spreadsheetId: spreadsheetId,
            range: 'Sheet1!A2', // Start appending from A2 to preserve headers
            valueInputOption: 'RAW',
            resource: { values: values },
        };

        const response = await sheets.spreadsheets.values.append(request);
        console.log(`${response.data.updates.updatedCells} cells appended with data.`);
        return `${response.data.updates.updatedCells} cells appended with data.`;
    } catch (err) {
        console.error('Error in uploadToGoogleSheets:', err);
        if (err.response) {
            console.error('Response data:', err.response.data);
            console.error('Response status:', err.response.status);
        }
        throw err;
    }
}

// Main function to process input and upload data to Google Sheets
async function main(params) {
    console.log('Starting main function with params:', JSON.stringify(params, null, 2));

    try {
        // Initialize Google Sheets API
        const sheets = initGoogleSheetsApi();

        // Extract data from params
        const { data } = params;

        // Validate input data
        if (!Array.isArray(data)) {
            throw new Error('Invalid input: data is not an array');
        }

        // Prepare the values to be inserted
        const values = data.map(item => {
            // Convert amount from wei to ether for readability
            const amountInEther = BigInt(item.amount) / BigInt(1e18);
            
            return [
                'Streams', // Dataset name
                item.network || '', // Network name (if available)
                amountInEther.toString(), // Amount in Ether
                item.blockNumber || '',
                item.spender || '',
                item.token || '',
                item.transactionHash || '',
                item.wallet || ''
            ];
        });

        // Check if there's data to upload
        if (values.length === 0) {
            return {
                message: "No data to upload to Google Sheets",
                result: "0 cells appended.",
            };
        }

        // Upload data to Google Sheets
        const result = await uploadToGoogleSheets(sheets, values);
        console.log('Upload result:', result);

        // Return success message with details
        return {
            message: "Streams data uploaded to Google Sheets",
            result: result,
            processedTransactions: values.length
        };
    } catch (error) {
        console.error('Error in main function:', error);
        throw error;
    }
}

module.exports = { main };

The above script takes data from QuickNode Stream, extracts it, creates columns in Google Sheets, and sends the Stream data into Google Sheets.

Zip the Files

QuickNode Functions supports several popular web3 libraries and Node.js v20 core modules natively, but since we've used googleapis library, we will need to upload a .zip file of index.js, .env, package.json, and package-lock.json along with node_modules folder so that our function can work on the QuickNode Functions platform.

Learn more about QuickNode Functions Code Editor and .Zip file uploading.

Set up a QuickNode Stream

For our example, we'll stream token approval data from the Ethereum mainnet. But, with Streams, you can do all kinds of things, from getting raw data to highly granular and enriched data; the possibilities are endless. You can check some examples in our backfill library.

The following is the Streams Filter being used:

Try it yourself by deploying the filtered Stream to your account.

function extractAddress(paddedAddress) {
  if (typeof paddedAddress !== 'string' || paddedAddress.length < 40) {
    console.error('Invalid address format:', paddedAddress);
    return null;
  }
  return '0x' + paddedAddress.slice(-40);
}

function hexToDecimal(hexValue) {
  if (typeof hexValue !== 'string') {
    console.error('Invalid hex value:', hexValue);
    return '0';
  }
  // Remove '0x' prefix if present
  hexValue = hexValue.replace(/^0x/, '');
  // If the resulting string is empty, return '0'
  if (hexValue === '') {
    return '0';
  }
  // Parse the hex string to a BigInt (to handle large numbers)
  try {
    return BigInt('0x' + hexValue).toString();
  } catch (error) {
    console.error('Error converting hex to decimal:', error);
    return '0';
  }
}

function findAndFilterLogs(data) {
  const targetTopic = "0x8c5be1e5ebec7d5bd14f71427d1e84f3dd0314c0f7b2291e5b200ac8c7c3b925";
  let results = [];
  let signatureFound = false;

  function search(item) {
    if (Array.isArray(item)) {
      item.forEach(search);
    } else if (typeof item === 'object' && item !== null) {
      if (item.topics && item.topics[0] === targetTopic) {
        signatureFound = true;
        try {
          const wallet = extractAddress(item.topics[1]);
          const spender = extractAddress(item.topics[2]);
          if (wallet && spender) {
            results.push({
              token: item.address,
              wallet: wallet,
              spender: spender,
              amount: hexToDecimal(item.data),
              blockNumber: hexToDecimal(item.blockNumber),
              transactionHash: item.transactionHash
            });
          }
        } catch (error) {
          console.error('Error processing log entry:', error);
        }
      } else {
        Object.values(item).forEach(search);
      }
    }
  }

  search(data);
  return signatureFound ? results : null;
}

function main(data) {
  const filteredData = findAndFilterLogs(data);
  console.log(JSON.stringify(filteredData, null, 2));
  return filteredData;
}

Now, while selecting a destination for your QuickNode Stream select the previously created Function as the destination for your Stream and create the Stream. Once the Stream starts streaming the data to Function, the Function will take the data and push to the Google Sheet. It'll look something like this:

Data in Google Sheet

Learn more about QuickNode Functions.

We ❤️ Feedback!

Let us know if you have any feedback or requests for new topics. We'd love to hear from you.

Stream Blockchain Data to Google Sheets

Overview​

Sample Function​

Zip the Files​

Set up a QuickNode Stream​

We ❤️ Feedback!

Overview

Sample Function

Zip the Files

Set up a QuickNode Stream