Create a dashboard with Google Sheets

Learn how to create a free dashboard with Google Sheets

Zero can import data from any source can build charts, tables & forms with it. In this blog post we will see how to import data from a Google Sheet into Zero. We will be using Zero's free plan so you can reproduce everything for free.

In this example we are going to see 3 different use cases, from that you can go on and build something which fits your use case exactly.

To start create a free account at https://zero.sh/auth and login. Once you have an account create a new board and open it.


Example 1: Dynamic data from Google Finance

This is an example of how we use Google Finance to create a board with the market cap of a few companies:

Create a spreadsheet with the following format:

CompanyMarket Cap
Apple=GOOGLEFINANCE("AAPL","marketcap")
Facebook=GOOGLEFINANCE("FB","marketcap")
Tesla=GOOGLEFINANCE("TSLA","marketcap")
You can of course add as many companies as you want and use any type of metric other than market cap.

This is the spreadsheet we will use in this example: https://docs.google.com/spreadsheets/d/1AoSQoTnfCM7NKF1WrDEW_gXM7o6ISi7vWxodSEwEREw/edit

We will use Zero's free Google Sheet to JSON Converter API to fetch the data. Make sure that your spreadsheet is public.

Let's create a new chart block:

Inside your Zero board click on new chart

In the script section paste the following code:

// Replace your URL below
const spreadsheet_url = "https://docs.google.com/spreadsheets/d/----------" 
const {data} = await api.get("https://labs.zero.sh/api/gs2json", {params:{format:3, url:spreadsheet_url}})
return data

Since we want the companies to be in the X Axis we will add Company in the X Axis input. As chart type we select Bar.

This will produce a chart like the one below:

CHART IMAGE

Below that we want a table with the values. So once we saved this block we can click on new table and paste the exact code from above. This will display a nice table.

The two blocks with a small gap between them. On the first block (the chart) click on the top right three dotted icon and then click on Remove Gap. This will remove the gap below and nicely link the two blocks.

BLOCKS

That's it!

Now you have a beautiful dashboard which works great on mobile with data fetched directly from Google Finance!


Example 2. Data from Facebook Lead Ads

If you run facebook lead ads you might want to have a dashboards where you see new leads. Let's build that with a little help from Zapier.

Signup to Zapier and use this automation - Once setup your lead will go directly to your Google Sheets.

From there you can follow the example above and do almost anything. These are a few examples:

With a spreadsheet like this:

LeadProcessedDate Added
tim@example.comFALSE1/13/2021
sara@exmaple.comFALSE1/13/2021
mike@example.comFALSE1/11/2021
  1. Display a table with a form to process leads: You could display a table with every lead and in the edit row script send data back to your backend once the lead has been processed.

    edit_row_script:

    const lead = data.new.Lead
    const processed = data.new.Processed
    if (!["TRUE", "FALSE"].includes(processed)){
    throw new Error ("Processed must be TRUE or FALSE")
    }
    // await axios.post() // Post your data to your backend
    return `${lead} processed:${processed}`
    

    EDIT CHART IMAGE

  2. Group the leads date and create a chart with the total number of leads in one day.

    Something like this:

    get_data_script:

    const spreadsheet_url = "https://docs.google.com/spreadsheets/d/---------------"
    const temp = []
    const {data} = await api.get("https://labs.zero.sh/api/gs2json", {params:{format:3, url:spreadsheet_url}})
    data.forEach(lead => {
    const date = lead['Date Added']
    existing_index = temp.findIndex(l => l.date === date)
    if (existing_index > -1) {
        new_value = temp[existing_index].value += 1
        temp[existing_index] = {date:date, value:new_value}
    } else {
        temp.push({date:date, value:1})
    }
    })
    return temp
    

    GROUP CHART IMAGE

Conclusion:

With Zero you can build any sort of interactive dashboards directly from Google Sheets. It's free to use.

Sign up today

Last Updated: July 13, 2021

Need Help?

Have a look at the docs or the examples above

Questions?

Chat with us or leave a message below