Rather than writing formulas and calculating metrics in your Google Sheets spreadsheet, you can send your data to a Dataset in Geckoboard using Google Apps Script.
This approach is useful for doing simple aggregations directly within your dashboard. It can also be a useful workaround if you don’t wish to link your entire Google Drive with Geckoboard but still want to build visuals and metrics with data from your spreadsheet.
Before you start
Make sure your dataset has been set up and formatted properly. Check that:
You have short and concise column headings.
The first row of data (below your headings) contains no blank cells.
The data formats for each column are set appropriately - dates, numbers, plain text, etc.
Step 1: Adding the Google Apps Script Library to your Google Sheet
Open your Google Sheet containing your data.
Navigate to Extensions > Apps Script.
Delete the existing default code:
function myFunction() {
}Copy and paste the following code block into the Script editor, then save the project:
function onOpen(e) {
GeckoboardPusher.buildAddonMenu()
}Click on Libraries and Enter the Library ID:
1pxVzJ450w-E69rA6YO5PdsYNxq3hEHHvdnPCYp5IpbClaJh73IDm9LEk
Select the most recent (highest) version.
The Identifier should be set to: GeckoboardPusher
Click the Add button.
Click on the Run icon to run your code block:
Navigate back to your Spreadsheet and verify the Geckoboard Pusher menu has been added to your menu bar:
Step 2: Configurations using the Geckoboard Pusher menu
From the Geckoboard Pusher menu, configure your API Key.
Then create your Geckoboard Sheet (to set the parameters of your dataset).
Add an optional trigger to push regular updates every 5 minutes.
You’ll now see a New Spreadsheet added to your workbook called “Geckoboard Config”.
Step 3: Configuring your Geckoboard Config Spreadsheet Fields
Note: The parameter fields will be automatically generated. These will need to be updated to reflect your unique dataset and spreadsheet.
Navigate to the newly created "Geckoboard Config" sheet.
Enter the name of the spreadsheet that contains your data into Cell: A2.
Enter a new name for your Geckoboard dataset into Cell: B2.
Enter the Cell Range you would like to send to Geckoboard into Cell: C2.
Select Run Now option from the Geckoboard Pusher menu.
If the push is successful, the Log Output in Cell: E2 will be “All Good”:
And that's it! Use the Datasets data source in Geckoboard to locate your newly created Dataset. You may also find this step-by-step video useful.
Tip: If the push is unsuccessful, follow the error guidelines to adjust your raw data fields and configurations accordingly.
Common Error Messages you may see
Error | Notes |
Error: The data has caused the schema to change | This either means there are:
If this is expected:
|
Error: Invalid time value |
|
Error: Mismatch data type found | All cells in each column must use the same data format. |