Push to Custom Widgets from a Google Sheet

Steps to use a Google Sheets script that pulls data from a sheet, before pushing the data to a Custom Widget.

Updated over a week ago

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 the Funnel, Pie, Map, RAG, Monitoring, Text, and List widgets. You can also send an Image to a Text widget (example named TEXTIMG). 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,
              }
            })
          }
        },
    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); }}
    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 widget
The 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
The 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

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:

  1. Click on Triggers in the script editor.

  2. Click on Add Trigger and set up an automation to run at the frequency you’d like.

Did this answer your question?