Geckoboard Developer Community

Push to Geckoboard's custom widgets from a Google Sheet


#1

Here’s a Google Sheets script that pulls data from a Sheet and then pushes the data from it to the configured Geckoboard custom widgets.

What’s the use case? It’s most likely that your needs for Google Sheets will already be met by using Geckoboard’s native Spreadsheets integration – as you can even pull into a Sheet data from other sources, such as this example from @gideahttps://community.geckoboard.com/t/how-to-get-mixpanel-data-into-spreadsheets-and-then-in-geckoboard

You then might consider this approach if you’d like to use features of Geckoboard’s custom widgets that aren’t available in the Spreadsheets integration, such as some visualization types (e.g. RAG, bullet graph) or you want to fine tune the parameters of the widgets.

At the moment there are some gotchas:

  • It supports just funnel, RAG and list widgets (more widgets to come if there is interest, but most likely this will focus on features, widgets, and parameters that aren’t available by default in Geckoboard’s native Spreadsheet integration).
  • It pulls from just the active/single sheet

That said, he’s how to use it:

  • Start a new Google Sheet and click on Tools > Script editor

  • Next set up your sheet in the right format:

  • In cell B2 put your Geckoboard API Key, which you can find by click on your initials at the top right corner of your dashboard, then Account
  • In the next row put in column A ‘Widget’ and then in column B the Push widget’s key. If you aren’t yet familiar with that, first from your dashboard click Add widget, then find custom widgets from the left side list. Once you’ve chosen the widget type, set the method to Push and copy the key:

  • In the next row put in column A ‘Type’ and then in column B the widget type
  • In the next rows put the parameters of the widget. For a RAG widget it expects a Value and Text (see here for reference – https://developer.geckoboard.com/#rag), and this add-on expects the Value in column A and the Text in column B

As you can see in the example, it pulls data row by row and splits the rows into groups where it finds the ‘Widget’ and ‘Type’ declarations.

Finally click the play > button from the script editor to run the code. It will ask you to authorize the application and you’ll need to click Allow.

Now check your dashboard. If all goes well you should see the data there!

If you’d like the data to update automatically, click on the Resources menu in the script editor, then Current project’s triggers and set up an automation to run at the frequency you’d like.

Let me know if you need a hand setting it up, if you’d like to see any other widget types or if you have any questions.

Update: I just added the List widget – https://developer.geckoboard.com/#list

It expects the item title in column A, the label in column B, the label color in column C and the description in column D.

Update 2: I added the Map widget – See the post below for more detail


#2

Hi,

I just tried your code, and it seems like there was missing a cloing bracket } in line 104.
Besides that. Nice work!

I will try to see if i can make it work for the Funnel Type.
Have you tried it yet?


#3

Hey phong, glad to hear that it was useful to you. Also, good catch! You’re right, it was missing a closing bracket there. I’ve updated the script - thanks!

I also added the funnel type to the script - if you try it let me know how it works for you. It expects the Sheet to look like this:

(Have a look at https://developer.geckoboard.com/#funnel for the parameters)

and results in this:


#4

Hi jaon,

Funnel works fine. Although I tried to enter numbers which end with % symbol in colomn B, and that didn’t work.
But besides that, everything looks nice!

I have tried to search the community, and it seems like no one else have looked into custom widgets with google sheets. So keep up the good work!


#5

Hi phong,

Yes, the widget expects absolute values and it calculates the percentages from that, so entering in a % value won’t work.

And thanks! I’m glad it works for you :slight_smile:


#6

Hi,

Are you developing further on this project? Like being able to have several widgets on one google sheet page, rather then one single sheet at a time.

And for all these custom widgets and integration with Google Sheets, have you looked at the optional parameters`?

Br/ Phong


#7

Hi Phong,

I don’t have any further plans for the project at the moment, but if you have any other features you’d like to see, I’d be interested to know.

Like being able to have several widgets on one google sheet page, rather then one single sheet at a time.

Actually, you can append as many widgets on one sheet at a time. You can see here there are three widgets in a row:

As for the widget parameters - I’ve implemented some but not all (e.g. the highlight color for a list widget, but not the percentage option for the funnel)


#8

Perfect! Just what i needed! :ok_hand:


#9

Hi Jason,

Would it be possible for you to look at the optional parameter for the Funnel?
What I wanna show on our Funnel, is different steps where each step represent a % value. This doesn’t make sense if Geckoboard calculates and shows it own procentage value.

I dont understand why the option is implemented and available in the CVS version of the Funnel but not in the Custom Widget Funnel chart.

Hope you can help with this one too.

Best regards,

Phong


#10

Hi phong,

I’ve added that parameter to the script.

Here’s the updated script – https://github.com/jasonmendes/geckoboard-custom-widgets-google-sheets/blob/master/index.gs

(It’s getting to feel a bit hacky and I suppose it’s poor vision on my part!)


#11

Hey Jason! Any chance you could provide guidance on how to do this for Geckoboard’s Mapping widget? I’m trying to convert a list of lat/lon coordinates to a Geckboard Map via Google Sheets, and I’d loooooove your guidance. LMK!


#12

:ok_hand: Our dashboard is perfect now! Thanks to you!


#13

Hey @sfam, that sounds fun! Could you share a sample of the data you’re working with? I’d be glad to help :slight_smile:


#14

Very glad to hear that! :slight_smile: If you’re up for sharing I’d be glad to know more about your use case for this method/these widgets


#15

Jason you’re amazing!

Here’s a sheet with some sample data: https://docs.google.com/spreadsheets/d/11MWEHepenkFTIBkgMj_S4qNG78WoWtwTer9dknzGr_g/edit?usp=sharing

Could you help me supercharge that sheet with the right code to integrate with Geckoboard’s Mapping widget? If so I’d be ridiculously grateful.


#16

Hey @sfam

I decided that for now I would make a simpler/more streamlined script for the mapping widget that works for your data (rather than appending it to the above collection of widgets) but if you (or anyone else) would like to use them in combination, I can modify it to work that way.

[Edit] If you take a look at the sheet you linked above and click on Tools -> Script editor you’ll find the code you need. Just configure the WIDGET_KEY and GECKOBOARD_API_KEY variables.

Here as well is the code:

var WIDGET_KEY = "paste your widget key in here";
var GECKOBOARD_API_KEY = "paste your Geckoboard API key in here";

function main() {
  var mapData = makeMap();
  pusher(mapData);
}

function makeMap() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange().getValues();
  return {
    points: {
      point: rows.slice(1).map(function(row) {
        return {
          size: row[0],
          latitude: row[1],
          longitude:row[2],
        }
      })
    }
  }
}

function pusher(data) {
  var widgetURL = "https://push.geckoboard.com/v1/send/" + WIDGET_KEY;
  var payload = {
    "api_key": GECKOBOARD_API_KEY,
    "data": data
  }
  var options = {
    "method" : "post",
    "contentType": "application/json",
    "payload" : payload
  };
  options.payload = JSON.stringify(options.payload);
  UrlFetchApp.fetch(widgetURL, options);
}

You’ll see above that you will need to edit the WIDGET_KEY and GECKOBOARD_API_KEY variables. Once you’ve set those, save the code and then select ‘main’ from the function menu:

Then click the play (looks a triangle) button on that same menu bar. It will ask you authorize the app. Click OK/continue and then check your dashboard. You should see the points mapped there.

So far it seems that these points are concentrated in South Africa? I configured the widget on my dashboard to focus in on Africa > South Africa and got this:

Thanks for your patience and let me know how it works for you or if you need a hand further!


#17

Hey Jason–this is AMAZING!!! Thank you so much for your help. I really truly appreciate it!

One final question: If I add a 4th column called “team”, how can I color-code the dots to represent the different teams? Do I just append the code so it reads…

   return {
          size: row[0],
          latitude: row[1],
          longitude:row[2],
          color:row[3],
        }

and then make sure the colors in that column are hex codes minus the “#”?

Thanks again for all your help on this. You’re a total rockstar!!!

Cheers,
Seth


#18

Hi Seth, I’m glad it’s working for you! And yep, you got it - that’s how you add in a field for the color :slight_smile:


#19

Hi Jason, Thanks - I love this. I am trying to make a pie chart type addition for this. I thought it might be something like this:

PIE: function(items) {
return {
item: items.map(function(item) {
return {
var slices = range.map(function(slice, index) {
return {
label: slice[0],
value: slice[1],
color: colors[2]
}

I am doing a lot of guessing with the code, so if you could let me know whether or not I am close, I will know whether to give up or not.

Thanks,
Caitliln


#20

Hi Caitlin,

Thanks for asking about this! You’re quite close. I’d do it like this:

    PIE: function(items) {
      var slices = {
        item: items.map(function(slice) {
          return {
            value: slice[0],
            label: slice[1],
            color: slice[2],
          }
        })
      }
      return slices;
    }

I’ve also amended the original script here to include the PIE type – https://github.com/jasonmendes/geckoboard-custom-widgets-google-sheets/blob/master/index.gs

The Google Sheet column ordering is value, label, color (note that color is a hex code without the # symbol):

And here’s the result:

I hope that this helps! Let me know if you need a hand further :slight_smile: