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 it to the configured Geckoboard Custom Widget.
Note: This script pulls from just the active/single sheet and supports the Funnel, Pie, Map, RAG, Monitoring, Text, and List widgets. You can also send an Image to a Text widget.
Adding the script
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,
}
})
}
},
TEXTIMG: function(items) {
return {
item: items.map(function(item) {
return {
text: '<img src="' + item[0] + '" alt="Image">',
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);
}}
Setting up your spreadsheet
In cell
A1put the text 'API Key', then in cellB1add your Geckoboard API Key. Follow these instructions to find your API Key.In cell
A2put 'Widget', then in cellB2put the Push widget’s key. Follow these steps to find your widget key:In Geckoboard, click Add widget.
Find Custom Widgets API from the list of integrations.
Choose and configure your widget type.
Set the method to Push and click on Add widget.
Click Copy widget key.
In cell
A3put 'Type', then inB3put the widget type (check the script to see available types).In the next rows put the parameters of the widget. For a RAG widget, the custom widget expects a
ValueandText. You would addValueinColumn Aand 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.
Setting up your spreadsheet for displaying an image using the Text widget
If you want to send an image to your custom widget, your spreadsheet needs to be formatted in the following way:
Type has to be set to TEXTIMG, column A needs to contain the image URL, and column B needs to be set to 0.
Using triggers to automatically update your data
To update your data automatically, follow these steps:
Click on Triggers in the script editor.
Click on Add Trigger and set up an automation to run at the frequency you’d like.
