The Custom Widgets API is a legacy option that no longer receives updates. If you're trying to bring custom data into your dashboard, we strongly recommend using the Datasets API or another option in this guide instead.

In this guide, we'll cover how to use Google Apps Script to push Google Sheet data to a Custom Widget in Geckoboard. Custom Widgets are a legacy API, and we recommend that you have programming experience to follow along with this method. You will need to review the script during your setup to ensure your configuration is set up correctly.

For reference, this script fetches data from just the active, single sheet and supports sending data to the Funnel, Pie, Map, RAG, Monitoring, Text, and List widgets as well as sending an image to a Text widget type.

Add the Apps Script to a Google Sheet

Create a new Google Sheet.
Then select Extensions > Apps Scripts.
Remove the default code, then paste this code in:

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

Configure your spreadsheet

Setting up the Custom Widgets spreadsheet

Head to Geckoboard and locate your API Key.
Next, create a new widget using the Custom Widgets data source.
Add the specific visualization type you're trying to create.
1. Be sure to select the Push method (not Poll).
2. Once the widget is saved to your dashboard, the widget key will be available for copying, which you'll need to do shortly.
Return to the Google Sheet and in A1 add the text API Key.
Then in B1 add your Geckoboard API Key.
In A2, add the text Widget.
Return to Geckoboard where your new Custom Widget is and click the Copy widget key link on the face of the widget.
Paste this key into B2.
In cell A3 put Type, then in B3, add the widget type.
1. Supported types are Funnel, Pie, Map, RAG, Monitoring, Text, and List widgets, and must match the Custom Widget you created earlier.
In the next rows of the sheet, add the widget parameters.
1. For example, if you're using a RAG widget, the custom widget expects a Value and Text.
2. You would add Value in Column A and the Text in Column B.
3. The script will pull data row by row and split the rows into groups where it finds the Widget and Type declarations.
Return to Extensions > Apps Scripts.
Click the Run button to run the script for the first time.
When prompted, you'll need to authorize the script to run.
Return to your dashboard once the script has executed successfully and your data will be displayed.

List widget type configuration

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

Colum A: Title
Column B: Label
Columb C: Label color
Column D: Description

Setting up your Google Sheet for a list widget

Map widget type configuration

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

Column A: Latitude
Column B: Longitude
Column C: Size
Column D: Color
- 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 from automatically changing 008850 to just 8850.

Setting up your Google Sheet for a map widget

Use the text widget type to display an image

If you want to send an image to a Custom Widget, your spreadsheet needs to be formatted in the following way:

The Type field must be set to TEXTIMG
Column A must contain the image URL
Column B should be set to 0

Configure a trigger to automatically refresh your data

To update your data automatically, follow these steps:

In the Apps Script editor, click Triggers.
Click Add Trigger, then set up an automation to run at the frequency you’d like.

Use Google Apps Script to import Stripe data to Google Sheets

Create a weather widget using Weatherstack and Google Sheets

Node.js module for Push method widgets

Use Google Apps Script to import Richpanel data to Google Sheets

Display Google Calendar events on your dashboard via Google Sheets