Guide Metadata Editor
Block Type:

https://zero.sh/guides/build-a-free-dashboard-with-google-sheets

Build a Free Dashboard with Google Sheets

Use Google Sheets to build a free dashboard which works great on mobile and it's always updated with the latest data.

Zero can visualize data coming from any source.

This guide will show you how to integrate it with Google Sheets. As with our other guides you have a complete editable example below.

We will build a simple chart, but you can use the same concepts and apply them to any other block type. If you need any help do not hesitate to contact us.

Authenticated requests

In this short guide we are going to use a public Google Sheet. This will make things a bit easier as it doesn't require any authentication. If you do require authentication you have to use the official Google Sheets API which requires a bit of extra work to get the api_key from Google

A great solution if you need to write data is to have a public spreadsheet which Zero can read without having to deal with the Google authentication and use a service like IFTTT or Zapier which can receive a post request from Zero and write it on a Google sheet using the webhook trigger.

Let's start

Since the Google API is quite complicated we have our own open source wrapper which formats the response in a simpler JSON.

This is the spreadsheet we will use in this example its a simple table with some companies and their market cap:

Company Market Cap
Apple =GOOGLEFINANCE("AAPL","marketcap")
Facebook =GOOGLEFINANCE("FB","marketcap")
Tesla =GOOGLEFINANCE("TSLA","marketcap")

In order to build a chart Zero requires the data to be formatted as An array of objects, you can always check the required format at the bottom of each script in the schema tab.

What we need to do

Whenever you need to build a block with Zero there are 2 things you need to do:

  1. Fetch the data
  2. Format the data to match the required schema

Let's start with the first one, the Google Sheet API it's quite complicated so we will use our own opensource wrapper which instead returns a nicely formatted JSON. With Zero you can make api calls using the format const {data} = api.get() Of course you can also have api.post or api.put etc.

So to fetch that data we simply do:

const spreadsheet_url = "https://docs.google.com/spreadsheets/d/1AoSQoTnfCM7NKF1WrDEW_gXM7o6ISi7vWxodSEwEREw" 
const {data} = await api.get("https://labs.zero.sh/api/gs2json", {params:{format:3, url:spreadsheet_url}})

If you see we use format:3 This will automatically format the request and return an array of objects so there is nothing else for us to do and we can simply

return data

One more thing

The chart block requires you to tell him which one is the X axis. If you don't the data won't show up. In our case is Company so we will add that in the chart settings. We have also extended Chartjs config to add some custom colors and make it look a bit nicer.

Editable Block Example

Below you have a live example. Feel free to change the settings and reload the block.

Loading block preview

Need Help?

Have a look at the docs or the examples above

Questions?

Chat with us or leave a message below