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 cellB2
put the Push widget’s key.Tip
If you aren’t yet familiar with finding a Push widget key, follow these steps:
In cell
A3
put 'Type
', then inB3
put the widget type.In the next rows put the parameters of the widget. For a RAG widget it expects a
Value
andText
, and this add-on expects theValue
inColumn A
and the Text inColumn 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 spreadsheet for Map widgets
Map widgets require your spreadsheet to be formatted in the following way:
The columns are latitude
, longitude
, size
, and color
.
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: