Sending charts via Gmail using QuickChart in dashdash

Inspired by Brian’s tweet and Martin Hawksey’s suggestion on using QuickChart

Problem statement

There’s always a need for charts :white_check_mark: & being able to visually represent data in different formats is a common ask among several of our users.

While we’ve not incorporated a charting feature inside dashdash (yet :wink:), the question that I’d be trying to answer in this post is what we can do to create and send charts while sharing email updates or reports using dashdash.

A preview of what’s to come

Before we begin

  1. Try and familiarize yourself with what’s QuickChart (the image on left :point_up_2:t4:) as that’s what we’d be using to render our charts over emails
  2. This is a JSON-heavy setup and so, it would help if you could review using dashdash’s PAIR2JSON & RANGE2JSON functions
  3. We’ll also be modifying a ton of JSON objects in half-a-dozen different ways and so, would highly recommend going through How to Modify JSON Objects in dashdash as well

Implementation

We can break this into 2 parts -

  1. Building the query parameter to use in the quickchart.io API endpoint (parts :point_down:t4: highlighted in Yellow)

  2. Embedding this endpoint inside the appropriate HTML attributes that we can use to send over an email (using our Gmail integration)

Setup

Here’s how I started -

  • Looking at the snippet from their homepage (IMO, that’s genius of QuikChart :+1:t4:) it was evident that we could achieve being able to build the query using our PAIR2JSON / RANGE2JSON functions and from what I could infer, it basically required a range of data from the spreadsheet to be in the form of an array

    • this can get a little tricky as we currently don’t have a single function to convert a range to an array but you could use this for now -

      =SUBSTITUTE(SUBSTITUTE(RANGE2JSON(B3,B4:B7),’{"’&B3&’":’,""),"}","")

  • Next, use similar combinations of SUBSTITUTE, PAIR2JSON & RANGE2JSON to arrive at what would eventually look something like this; in my case, I was trying to replicate the exact same image as shown on their homepage -

    • This part of the solution would depend on the kind of data structure you have on your spreadsheet and may not look the same when you try to implement it

      raw JSON data

    • Note: There’s also no single approach to achieve this data structure so feel free to experiment :sunglasses: if you look closely, you can see that I make use of CONCATENATE too but you can totally choose to omit that altogether

  • Final step was to arrive at how to send it over an email but thanks to QuickChart’s documentation on How to create and send charts in email this was figureoutable (I know, that’s a fake word :stuck_out_tongue:)

I hope this helps at least a few of you out there who may’ve tried to achieve something like this before :slight_smile:

Feel free to DM me or drop a comment in case you need any assist with having this implemented in your spreadsheet on dashdash.

In case you just landed on this article and don’t yet have an account with us, do sign up here to get started!

Let’s go :rocket:
Sourabh | No-Code Builder

2 Likes