All Collections
Connect your spreadsheets
Connect your spreadsheet
Send Data from Google Sheets using Datasets to calculate metrics and build visuals
Send Data from Google Sheets using Datasets to calculate metrics and build visuals

Useful for doing simple aggregations directly within your dashboard.

Updated over a week ago

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 Scripts.

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

  1. Open your Google Sheet containing your data.

  2. Navigate to Extensions > Apps Script.

  3. Delete the existing default code:

    function myFunction() {
    }
  4. Copy and paste the following code block into the Script editor, then save the project:

    function onOpen(e) {
    GeckoboardPusher.buildAddonMenu()
    }
  5. Click on Libraries and Enter the Library ID:

    1pxVzJ450w-E69rA6YO5PdsYNxq3hEHHvdnPCYp5IpbClaJh73IDm9LEk
  6. Select the most recent (highest) version.

  7. The Identifier should be set to: GeckoboardPusher

  8. Click the Add button.

  9. Click on the Run icon to run your code block:

  10. 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

  1. From the Geckoboard Pusher menu, configure your API Key.

  2. Then create your Geckoboard Sheet (to set the parameters of your dataset).

  3. Add an optional trigger to push regular updates every 5 minutes.

  4. 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.

  1. Navigate to the newly created "Geckoboard Config" sheet.

  2. Enter the name of the spreadsheet that contains your data into Cell: A2.

  3. Enter a new name for your Geckoboard dataset into Cell: B2.

  4. Enter the Cell Range you would like to send to Geckoboard into Cell: C2.

  5. Select Run Now option from the Geckoboard Pusher menu.

  6. If the push is successful, the Log Output in Cell: E2 will be “All Good”:

  7. 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:

  • Gaps in your data.

  • The data type for a column has changed.

  • A new column has been added or removed from the dataset.

If this is expected:

  • It is advised to delete the dataset in Geckoboard.

  • Re-push the data from your Google Sheet using the Geckoboard Pusher Menu.

Error: Invalid time value

Error: Mismatch data type found

All cells in each column must use the same data format.

Did this answer your question?