Push to custom widgets from a Google Sheet

Important note

We encourage you to try Datasets for the most simple, powerful way to access your most important data with Geckoboard.

In this article we'll demonstrate a Google Sheets script that pulls data from a Sheet before pushing the data from it to the configured Geckoboard custom widgets.

Note

This script supports just the funnel, RAG, and list widgets. It also pulls from just the active/single sheet.

To push to custom widgets from a Google Sheet, follow these steps:

  1. Create a new Google Sheet. Click on Tools > Script editor.Script editor on Google Sheets
  2. Remove what's there by default and paste in the following code:
    function main() {
      var data = getRows();
      pusher(data.key, data.widgets);
    }
    
    function pusher(key, widgets) {
      widgets.forEach(function(widget) {
        var widgetURL = "https://push.geckoboard.com/v1/send/" + widget.key;
        var payload = {
          "api_key": key,
          "data": widget.data
        }
        var options = {
          "method" : "post",
          "contentType": "application/json",
          "payload" : payload
        };
        options.payload = JSON.stringify(options.payload);
        Logger.log(options);
        UrlFetchApp.fetch(widgetURL, options);
      });
    }
    
    function getRows() {
      var sheet = SpreadsheetApp.getActiveSheet();
      var rows = sheet.getDataRange().getValues();
      var key = rows[0][1];
      var data = rows.slice(1);
      var widgetRows = findWidgets(data);
      var widgets = splitWidgetData(data, widgetRows);
      var payloads = makePayloads(widgets);
      return {
        key: key,
        widgets: payloads
      }
    }
    
    function makePayloads(widgets) {
      return widgets.map(function(widget) {
        var key = widget[0][1];
        var type = widget[1][1];
        var data = widgetType(type, widget.slice(2));
        return {
          key: key,
          data: data
        }
      });
    }
    
    function splitWidgetData(data, widgetRows) {
      var widgets = [];
      widgetRows.forEach(function(_, i) {
        if (widgetRows[i + 1]) {
          widgets.push(
            data.slice(widgetRows[i], widgetRows[i + 1])
          );
        } else {
          widgets.push(
            data.slice(widgetRows[i])
          );
        }
      });
      return widgets;
    }
                   
    function findWidgets(rows) {
      var widgetIndexii = [];
      var column = rows.map(function(row) {
        return row[0];
      });
      column.forEach(function(row, i) {
        if (row === 'Widget') {
          widgetIndexii.push(i);
        }
      });
      return widgetIndexii;
    }
    
    function widgetType(type, data) {
      var types = {
        RAG: function(items) {
          return { 
            item: items.map(function(item) {
              return { 
                value: item[0],
                text: item[1]
              }
            })
          }
        },
        LIST: function(items) {
          return items.map(function(item) {
            var bit = { title: { text: item[0] } };
            if (typeof item[1] !== undefined) {
              bit.label = {};
              bit.label.name = item[1];
            }
            if (typeof item[2] !== undefined) {
              bit.label.color = item[2];
            }
            if (typeof item[3] !== undefined) {
              bit.description = item[3];
            }
            return bit;
          });
        },
        FUNNEL: function(items) {
          var itemList = items.map(function(item) {
            return {
              value: item[0],
              label: item[1]
            } 
          })
          var obj = { item: itemList };
          if (items[0][2] == 'percentage' && items[0][3] == 'hide') {
            obj.percentage = 'hide';
          }
          return obj;
        },
        PIE: function(items) {
          var slices = {
            item: items.map(function(slice) {
              return {
                value: slice[0],
                label: slice[1],
                color: slice[2],
              }
            })
          }
          return slices;
        },
        TEXT: function(items) {
          return { 
            item: items.map(function(item) {
              return { 
                text: item[0],
                type: item[1] || 0,
              }
            })
          }
        },
        MONITORING: function(items) {
          var item = items[0];
          return { 
            status: item[0],
            downTime: item[1] || '',
            responseTime: item[2] || 0,
          } 
        },
        MAP: function(items) {
          return {
            points: {
              point: items.map(function(item) {
                var latitude = item[0];
                var longitude = item[1];
                var size = item[2];
                var color = item[3];
                var point = {
                  latitude: latitude,
                  longitude: longitude,
                }
                if (size) {
                  point.size = size;
                }
                if (color) {
                  point.color = color;
                }
                return point;
              })
            }
          }
        },
      };
      return types[type](data);
    }
    
    Google Sheets Script Editor
  3. Set up your sheet in the correct format.Setting up the Custom Widgets spreadsheet
  4. In cell B2 put your Geckoboard API Key. Follow these instructions to find your API Key.
  5. In cell A2 put 'Widget', then in cell B2 put the Push widget’s key.

    Tip

    If you aren’t yet familiar with finding a Push widget key, follow these steps:

    1. Navigate to the Dashboard your custom widget lives.
    2. Click Add widget.
    3. Find custom widgets from list of integrations.
    4. Choose and configure your widget type.
    5. Set the method to Push
    6. Copy the Widget key.Copy widget key from config
  6. In cell A3 put 'Type', then in B3 put the widget type.
  7. In the next rows put the parameters of the widget. For a RAG widget it expects a Value and Text, and this add-on expects the Value in Column A and the Text in Column B.
  8. The script will pull data row by row and split the rows into groups where it finds the ‘Widget’ and ‘Type’ declarations.
  9. Click the Play > button from the script editor to run the code. It will ask you to authorize the application, so you’ll need to click Allow.Run the code by pressing the play button on the script editor
  10. Check your dashboard. If all goes well you should see the data there.RAG custom widget

Setting up your spreadsheet for List widgets

List widgets require your spreadsheet to be formatted in the following way:

It requires item title in column A, the label in column B, the label color in column C and the description in column D.Setting up your Google Sheet for a list widgetThe List widget

Setting up your spreadsheet for Map widgets

Map widgets require your spreadsheet to be formatted in the following way:

The columns are latitude, longitude, size, and color.Setting up your Google Sheet for a map widgetThe List widget

Note

As Color requires a hex code, you'll need to change the cell formatting of that column to Plain Text. This will prevent Google Sheets automatically changing 008850 to just 8850.Setting your color column to plain text

Using triggers to automatically update your data

To update your data automatically, follow these steps:

  1. Click on the Resources menu in the script editor.
  2. Then click Current project’s triggers and set up an automation to run at the frequency you’d like.Current project triggers menuCurrent project triggers screen
Was this article helpful?

Awesome! 👍  Thanks so much for your feedback!