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. This is useful when 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.

Note: This script supports just the Funnel, Pie, Map, 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:

Create a new Google Sheet. Click on Tools > Script editor.

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);
}}

Set up your sheet in the correct format.
In cell B2 put your Geckoboard API Key. Follow these instructions to find your API Key.
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.
In cell A3 put 'Type', then in B3 put the widget type.
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.
The script will pull data row by row and split the rows into groups where it finds the ‘Widget’ and ‘Type’ declarations.
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.
Check your dashboard. If all goes well you should see the data there.

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 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 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.

Using triggers to automatically update your data

To update your data automatically, follow these steps:

Click on the Resources menu in the script editor.
Then click Current project’s triggers and set up an automation to run at the frequency you’d like.

Push to Custom Widgets from a Google Sheet

Tip

Setting up your spreadsheet for List widgets

Setting up your spreadsheet for Map widgets

Note

Using triggers to automatically update your data