This guide will demonstrate how to create and use custom functions within Google Apps Script to pull data from a Google Sheet, then push that data to the Custom Widgets integration in Geckoboard. This approach can come in useful when you want to use one of the following visualization types not supported by our dedicated Spreadsheets data source:
To use Custom Widgets, you must be on a paid plan.
Step 1: Prepare your Google Sheet
To set up your Google Sheet from scratch, follow the steps below.
- In Google Sheets, either create a new sheet or make a copy of this pre-prepared sheet which contains all widget types supported in this guide.
- In the cell A1, add the word API Key.
- In the cell A2, add the word Widget.
- In the cell A3, add the word Type.
- Navigate to Tools, then click Script editor.
- Delete the existing default code.
- Copy and paste all of the code from this GitHub Gist into the Script editor.
- Click the Save button.
- When prompted, assign a project name and click OK.
Step 2: Create your Custom Widget
Next, we'll return to Geckoboard to create your Custom Widget.
- In the top right of your dashboard, click Add widget.
- You'll see a scrollable list of Integrations. Search for Custom Widgets using the Search sources field.
- Select Custom Widgets.
- Choose the visualization you wish to use. For this guide, we’ll select the RAG widget type.
Please note, only some visualizations are supported for this method. Check the Supported Visualizations section for details on which visualizations work with this guide.
- Set the Method field to Push.
- Click .
- Locate the widget on your dashboard and click Copy widget key.
- Return to your Google Sheet.
- Next to Widget, paste the widget key into the B2 cell.
- Return to Geckoboard and obtain your API key.
- Return to your Google Sheet and next to API Key, paste the API Key into the B1 cell.
- In the B3 cell next to Type, add the type of widget visualization you are using. Since the RAG widget type was selected for this guide, type RAG in this cell.
- In column A below the other content, add your numeric values.
- In column B below the other content, add your text.
Step 3: Run the script
Now we'll run the script and push the data to Geckoboard.
- Open the Script editor.
- To run the script, click the Play button.
- Next, click Review Permissions.
- Follow the on-screen steps to select or sign into a Google account.
- To permit the script to run, when prompted, click Allow.
- Return to your dashboard and view your widget!
Step 4: Automate with triggers
Finally we'll use triggers to automate the process of pushing data to Geckoboard.
- In the Script editor, click Edit.
- Then select Current project's triggers.
- In the bottom left of the page that automatically opens, click + Add Trigger.
- In the Choose which function to run field, ensure that main is selected.
- In the Choose which deployment is run, ensure that head is selected.
- In the Select event source field, choose the type of trigger you desire. We recommend the Time-driven option.
- Fill in any other fields with your desired parameters.
- Click Save. Your script will now trigger and run automatically!