Esprezzo Blog

Automatically log on-chain activity to Google Sheets without code

Written by Jialin L | February 5, 2024
If you already use Zapier, you know how useful it can be, especially for those of us who want to integrate or connect services to each other without needing to bother engineers. Dispatch webhooks work with Zapier — so you can use on-chain activity to automate with any app in the Zapier ecosystem in a few clicks. Auto-updating a Google Sheet with on-chain activity? Check. Creating a Slack bot to let you know about select potential security-related activity? Check. No code required.
 
Today we're going to show you how easy it is to automatically send on-chain events to Google Sheets. This creates an always-up-to-date historical activity log for the smart contracts and wallets you're interested in. With that data automatically flowing to Google Sheets, you can also use it to create some basic data visualizations.
 
If you're more interested in collecting and analyzing data about how people are interacting with certain smart contracts or what wallets are doing over time rather than getting real-time alerts, this is a great place to start.

We'll be looking at a fun xNFT project called Smart Cats, and seeing what people are doing with their cats.

How the webhook works

Triggers

Dispatch and Zapier are both no-code automation tools that use triggers — events with conditions (filters) that start an automation workflow. Dispatch listens for the on-chain activity you're interested in, and works with Zapier to start your workflow whenever the specific on-chain activity happens.

Zapier Triggers

"Webhooks by Zapier" is the Zapier trigger that allows you to use just about any app to automatically start workflows in any of the services in Zapier's app directory.

Dispatch Triggers

We currently offer three automation Triggers:

  • Balance changes: Dispatch monitors wallets for balance change activity.
  • Balance threshold crossings: Handy if you have a wallet or contract you don't want to fall below a certain amount to keep things running, Dispatch can let you know when thresholds are crossed.
  • Smart contract activity: Dispatch lets you know when certain events are emitted, or functions are called by smart contracts
New here? Check out our Dispatch Quickstart Guide

Actions

Currently Dispatch integrates directly with Discord, Telegram, email, and webhooks. With our webhook Action, you can integrate with Zapier, as well as any other service that accepts webhooks, and thus use on-chain activity to trigger workflows in just about any other app with publicly accessible endpoints.

Dispatch automations = Patches

"Patches" are what we call automations in Dispatch — we're music nerds at Esprezzo, and the name comes from old-school analog synthesizers that relied on "patching" cables together to create sounds.

Patches (automations) consist of a Trigger (on-chain activity) and the resulting Action that automagically happens when your Trigger conditions are met.

Add to a spreadsheet when...

In this example, we'll be using Dispatch webhooks and Zapier's "Create Spreadsheet Row in Google Sheets" as the automation actions.

This means when the on-chain activity you want to know about happens, a new row will automatically be added to your spreadsheet with the information you want.

In a nutshell, we're going to

  1. Create a Patch in Dispatch (sending to a webhook)
  2. Create a corresponding Zap in Zapier, for webhook to Google Sheets

Dispatch monitors blockchain networks for activity that matches your conditions, and sends that data via webhook to the app of your choice. In this example that app will be Google Sheets via Zapier.

Our goal with this automation is to see a new row in a Smart Cats activity sheet whenever anyone does anything with their cats. Each row in the spreadsheet will tell us

  • What the activity was
  • When the activity took place
  • The transaction hash for that activity
  • A link to view the transaction on a block explorer
  • The network where the activity happened

With fun activities like inviting and accepting cat playdates, cat feeding and cleaning, it will be exciting to see what people are doing with their cats. Let's get started! 

What you'll need

To start sending on-chain activity data to Google Sheets, you'll need:

  1. A paid Zapier account (webhooks are considered a "Premium" Zapier integration)
  2. Free Dispatch account
  3. Free Google account
Don't have a Dispatch account yet? Request a free account.

Sort your browser tabs

Because we're going to be going back and forth between Dispatch, Zapier, and Google Sheets to get this workflow set up, we'll want to make sure we don't accidentally close any of the browser tabs with those apps in the process.

We recommend using one browser window for Zapier, another for Dispatch, and a third for Google Sheets.

Okay, let's get started!

Pick the data you want

Let's start by seeing what data is available through Dispatch by looking at the webhook references.

We have sample payloads for each Dispatch Trigger:

After looking at the reference for the Smart Contract Activity Trigger, I've decided I want this data automagically sent to a Google Sheet when interesting things happen to Smart Cats:

  • Timestamp — when the activity happened
  • Activity name — the name of the smart contract event or function
  • Transaction (Tx) hash
  • Block explorer Tx link — makes it super easy to view the details of the transaction if needed later
  • Network — useful if we want to add the contract on another network later, and compare activity for this project across networks

Now that we know what data we want, we'll set up our Google Sheet.

Create a Google Sheet (spreadsheet)

This is where the smart contract activity data will go.

We're going to call ours "Smart Cats activity".


Here's a handy sample Google Sheet with instructions and an example of the data you can get by using Dispatch with Zapier.

Feel free to make a copy and customize it for your own Zaps and Patches.

Add column headings to the Google Sheet

Let's add column headings for the data.

Based on the data we decided on, we're going to use these column headings:

  • Timestamp
  • Activity name
  • Transaction (Tx) hash
  • Block explorer Tx link 
  • Network

Now our Google Sheet is ready to receive data. Keep this tab open, and let's move on to Dispatch.

Start setting up your Patch in Dispatch

From the Dispatch Dashboard, you can choose a Trigger and Webhook as the Action, with "Smart contract activity" as your Trigger.

But, there's an even easier way: Patch templates. Patch templates have a Trigger and Action already selected, so it's even faster to create your automation.

Once you've clicked one of those templates or chosen the Trigger and Action, you'll be taken to the Patch Builder, step 2: Trigger conditions.

Add the Smart Cats smart contract

Let's add the Smart Cats contract.

  • Network: The network that your contract is on. Smart Cats is on Polygon, so that's what we've selected here.
  • Smart contract: Click the "Add a smart contract" option at the bottom of the menu.

To add the contract, paste in the contract address, and give it a nickname. You can change the nickname later, so don't worry about it much as long as you remember what it is.

  • Contract address0x7573933eB12Fa15D5557b74fDafF845B3BaF0ba2
  • Contract nickname: Smart Cats

Select the smart contract events and/or functions you're interested in

After adding the contract, you'll see all the events and functions available for monitoring (i.e. those that can change states).

We'll be monitoring the following:

    • acceptPlayDate
    • cleanCat
    • feedCat
    • inviteCatForPlaying
    • levelUp

Press "Continue".

Webhook should already be selected as the "Action"

Press "Continue" again to move on to Step 4 — Action details.

In the Patch Builder Webhook dropdown, select the Add a webhook option.

We'll need the Zapier webhook to add the URL where Dispatch will send data.

Do not close the tab or window; we'll be pasting the Zapier webhook here after the next step.

Start creating a Zap in Zapier

Go to your Zapier tab or window. We're going to try their beta AI feature to pick our Trigger and Action.

In the "Create a Zap" text box, type "webhook to google sheets"

Their recommended Zap — Webhook to Google Sheets — looks right!

Click the "Try it button.

In the Zap editor, click to expand the "Test" step. After a few seconds, you should see your webhook URL that starts with "https://hooks.zapier.com..." 

Copy the Zapier webhook URL

Click the "Copy" button next to the webhook URL.

Don't close the window/tab; we'll be coming back and hitting that "Test trigger" button after we send the test webhook payload from Dispatch.

Add the webhook to Dispatch

In your Dispatch tab, paste the Zapier webhook URL in the "Webhook URL" input, and give it a name.

  • Webhook name: We're going to call ours "Smart Cats activity".
  • Webhook URL: Paste the Zapier webhook URL.

Save the webhook, and press "Continue".

Send a test payload to Zapier

In Dispatch Patch Builder Step 5, press the "Send a test JSON payload" button.

You should see a confirmation that Dispatch sent data to your Zapier webhook:

Check Zapier for the test payload

Back in your Zapier tab/window, press the "Test trigger" button.

If the test was successful, you should see the sample webhook payload with examples of the data Dispatch will send.

Sample webhook payload from Dispatch in Zapier's Zap editor

We'll be using this to help us make sure we're getting the data we want in the right columns of our spreadsheet. Click "Continue with selected record".

Select the Google sheet

After the successful webhook test, you should be taken to the second part of the Zap, the Google Sheet setup.

If you haven't already granted Zapier access to your Google account, you'll need to do that first. If your Google account has access to multiple Google Drives, you'll need to pick the one where your Smart Cats or other data destination Sheet resides.

In the Zap editor,

  • Spreadsheet refers to the Google Sheet
  • Worksheet refers to the tab in the Sheet where you want the data to go

Select what data should go in each column

Once we select the Spreadsheet and Worksheet, Zapier automatically detects the column headings.

Under the name of each column heading, we'll select the data from Dispatch we want to go in each column. Using the "Insert Data" search box can be really helpful. Just make sure when you're searching, your typing in the box under "Insert Data", and not the box above it.

If you have an "Activity name" column in your Sheet to capture smart contract event and function names, search for "Signature name".

Here's how it looks with all the Dispatch data mapped to the Google Sheets columns:

Press "Continue".

Test and turn on the Zap

Let's make sure the data looks correct in the Google Sheet: in Zapier, press the "Test step" button.

If the test was successful, you should see the data from the sample payload in the spreadsheet. Note that this is sample data, not actual data from the Patch you're creating. We're just checking to make sure the right type of data is going into each column.


The sample data looks good; it's the type of data we expect to see in each column.

Let's delete the test row and turn the Zap on by pressing the "Publish" button.

Turn the Patch on

Go back to Dispatch. You should be on Step 6.

Give your Patch a name if you want (you can rename it later).

Press the "Turn Patch on" button.

You're done!

Now that your Patch and Zap are on, anytime a Smart Cat invites or accepts a playdate, is fed, cleaned, or levels up, you'll see a new row in the spreadsheet with the data you selected.

Here's how mine looks after a little formatting:

This is just one example of how useful webhooks can be. In addition to Zapier, you can use Dispatch webhooks to send on-chain data to apps and DApps you're building, and apps with publicly accessible endpoints.

Ready to try Dispatch?

If you liked this tutorial and want to try other automations, sign up for a free Dispatch beta account.