All Collections
Pair with task automation tools
Zapier and Geckoboard
Display Xero metrics in Geckoboard using datasets and Zapier
Display Xero metrics in Geckoboard using datasets and Zapier

Steps to pair Zapier with datasets to display sales revenue data from Xero.

Updated over a week ago

Use Datasets and Zapier to display Xero metrics on your Geckoboard dashboard. As Zapier isn't meant for pulling historical data, we'll first need to populate a new dataset with exported Xero data.

Step 1: Import existing Xero data to Geckoboard

To include existing data from Xero, you can export it as a CSV file and send it to a dataset in Geckoboard using Google Apps Scripts.

In this example, we'll export sales revenue data:

  1. In Xero, click the Business menu, and select Sales overview.

  2. Click Paid from the invoice status options to filter by paid invoices.

    Click Paid from the invoice status options to filter by paid invoices.
  3. To refine your search, select a Start date, End date or Date type.

  4. Click Export to download a CSV file containing the invoices.

    Export to download a CSV file.
  5. Upload the CSV file to Google Drive and open it with Google Sheets.

  6. Locate the data types you want to include in your dataset and drag the columns in the order you want, starting from column A.

  7. Delete the unwanted columns. This is to ensure there are no blank cells in your row below your headings.

  8. Make sure the data formats for each chosen column are set appropriately - dates, numbers, plain text, etc.

  9. Navigate to Extensions > Apps Script.

  10. Delete the existing default code:

    function myFunction() {
  11. Copy and paste the following code block into the Script editor, then Save the project.

    function onOpen(e) {
  12. Click on Libraries and Enter the Library ID:

  13. Select the most recent version.

  14. The Identifier should be set to 'GeckoboardPusher'.

  15. Click the Add button.

  16. Click on the Run icon to run your code block.

    Run the code block
  17. Navigate back to your Spreadsheet and verify the Geckoboard Pusher menu has been added to your menu bar.

    Geckoboard pusher menu.
  18. From the Geckoboard Pusher menu, configure your API Key.

  19. Create your Geckoboard Sheet, which is used to set the parameters of your dataset.

  20. Navigate to the newly created 'Geckoboard Config' sheet.

  21. Enter the name of the sheet that contains your data into cell A2.

  22. Enter the name for your Geckoboard dataset into cell B2.

  23. Enter the cell range you're sending to Geckoboard into Cell: C2.

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

  25. If the push is successful, the Log Output in Cell: E2 will be 'All Good'. If you're getting errors, you can read this.

    'Geckoboard Config' sheet.
  26. Use the Datasets data source in Geckoboard to locate your newly created dataset.

Step 2: Create a Zap that connects Xero to your dataset

With the dataset created, we can now add new sales records to it. We'll make a new Zap in Zapier that connects both your Xero and Geckoboard accounts before linking it to your new dataset.

  1. Make a new Zap in Zapier. In the first Trigger step, search for and select Xero.

  2. Next, we'll set up our trigger event. From the drop-down list, select New Sales Invoice. This will trigger an update whenever you add a new sales invoice.

    New Sales Invoice trigger event in Zapier
  3. Connect to and select your Xero account. Then click Continue.

  4. Select your Organization, then select Paid status.

    Organization and invoice status.
  5. As we're using the Money field, Geckoboard expects a value in the currency’s smallest denomination. For example, the USD’s smallest denomination is the cent, so a USD field would specify $10.00 as 1000. You must multiply the amount by 100 to get the currency values Geckoboard expects. You can do this in Zapier by adding a Format step before sending data to Geckoboard.

    Perform a format action in Zapier
  6. Then you'd specify a Multiply operation.

    Perform a multiply operation in Zapier
  7. Click Test trigger. Zapier will attempt to find a recent new sales invoice to use in the Zap.

  8. With your trigger now set up, we'll now add an action to your Zap. Actions are events your Zap performs when your trigger event occurs. Search for and select Geckoboard.

  9. Click the Action Event drop-down list and select Add Record to Dataset. Then, click Continue.

    Add new record to Dataset.
  10. Connect to and select your Geckoboard account. You'll need to access your Geckoboard API key. Then click Continue.

  11. Under Dataset ID, select your new dataset.

  12. You'll now see all the schema fields for your dataset. Click each one and select the corresponding field from your sales invoice data.

    Xero action step in Zapier with data fields completed
  13. The Update Type dropdown menu is set to Add to existing records (Post) by default.

  14. Click Test & Continue. In this test Zapier will attempt to send a record to your dataset. You're now ready to click Turn on Zap.

  15. Finally, return to Geckoboard and add a new dataset widget powered by your Xero data. If you would like to learn more about creating dataset widgets, you can see our guide to creating visualizations from datasets.

    Geckoboard widgets powered by Xero data.

Did this answer your question?