Geckoboard

In this guide, we'll cover how to use Google Apps Script to fetch ticket data from Richpanel and bring it into Google Sheets, which you can then use to power your widgets in Geckoboard. 

Find your <a href="https://developer.richpanel.com/docs#get-api-key" rel="nofollow noopener noreferrer" target="_blank">Richpanel API key</a>.

Make a copy of <a href="https://docs.google.com/spreadsheets/d/1dzu4QYxIgQTP5-WbadF-zUBkrVjxDuR_Ty5c8VExIQY/copy" rel="nofollow noopener noreferrer" target="_blank">this Google Sheet</a>.

Navigate to Extensions &gt; Apps Script.

In the pop-up code window, enter your API key in two places by replacing the placeholder <code>'&lt;your-richpanel-api-key&gt;'</code>.

Click on the Run icon to run the code block.

Navigate back to your Spreadsheet and verify that the Ticket Data tab is now populated with your Richpanel ticket data. 

1. We can now manipulate the data using queries and other functions for use in Geckoboard. For example, you could use the following array formula to display the number of tickets grouped by tag.
   =QUERY(ARRAYFORMULA(FLATTEN(IFERROR(TRIM(SPLIT(SUBSTITUTE('Ticket Data'!I2:I, ",,", ","), ",")), ""))), "SELECT Col1, COUNT(Col1) WHERE Col1 &lt;&gt; '' AND Col1 &lt;&gt; '#VALUE!' GROUP BY Col1 ORDER BY COUNT(Col1) DESC LABEL Col1 'Tag', COUNT(Col1) 'Number of Tickets'", 0) 

When you're ready, switch to Geckoboard and <a href="https://support.geckoboard.com/en/articles/6055517-connect-your-google-sheet-to-geckoboard">connect your sheet using our Google Sheets data source</a> and begin building your widgets.

1. Find your <a href="https://developer.richpanel.com/docs#get-api-key" rel="nofollow noopener noreferrer" target="_blank">Richpanel API key</a>. 
2. Make a copy of <a href="https://docs.google.com/spreadsheets/d/1dzu4QYxIgQTP5-WbadF-zUBkrVjxDuR_Ty5c8VExIQY/copy" rel="nofollow noopener noreferrer" target="_blank">this Google Sheet</a>. 
3. Navigate to Extensions &gt; Apps Script.
4. In the pop-up code window, enter your API key in two places by replacing the placeholder <code>'&lt;your-richpanel-api-key&gt;'</code>.
5. Click the Save disk icon.
6. Click on the Run icon to run the code block.
7. Navigate back to your Spreadsheet and verify that the Ticket Data tab is now populated with your Richpanel ticket data. 
 1. We can now manipulate the data using queries and other functions for use in Geckoboard. For example, you could use the following array formula to display the number of tickets grouped by tag.
 =QUERY(ARRAYFORMULA(FLATTEN(IFERROR(TRIM(SPLIT(SUBSTITUTE('Ticket Data'!I2:I, ",,", ","), ",")), ""))), "SELECT Col1, COUNT(Col1) WHERE Col1 &lt;&gt; '' AND Col1 &lt;&gt; '#VALUE!' GROUP BY Col1 ORDER BY COUNT(Col1) DESC LABEL Col1 'Tag', COUNT(Col1) 'Number of Tickets'", 0) 
8. When you're ready, switch to Geckoboard and <a href="https://support.geckoboard.com/en/articles/6055517-connect-your-google-sheet-to-geckoboard">connect your sheet using our Google Sheets data source</a> and begin building your widgets.

Things to keep in mind about how the script works: 

The script fetches ticket data from the RichPanel API and updates the tab Ticket Data. 

If you're adding the script to another sheet, you must create a sheet tab with the same 'Ticket Data' name. If you use a different name, you must modify the script accordingly.

The script has a <code>// ... add other fields as needed</code> part. The total fields are available as part of this script, but the Richpanel API offers more if you wish to make changes.

- The script fetches ticket data from the RichPanel API and updates the tab Ticket Data. 
- If you're adding the script to another sheet, you must create a sheet tab with the same 'Ticket Data' name. If you use a different name, you must modify the script accordingly.
- The script has a <code>// ... add other fields as needed</code> part. The total fields are available as part of this script, but the Richpanel API offers more if you wish to make changes.

Errors are logged, but it won't interrupt the script.

Learn how to use a script to automatically get data from Richpanel into Google Sheets.

Function	Description	Notes
`fetchAndPushToSheet`	Fetches ticket data for the last 100 tickets from Richpanel's API from the list conversations endpoint.	Tags are returned with an alphanumeric ID, so a second call is made to get the corresponding tag names (human readable names) from their API. For each ticket, it either updates an existing row or appends a new one in the 'Ticket data' sheet. So, if a ticket ID already exists in the sheet but, say, the state went from 'New' to 'Closed', or a tag was added, or the customer replies, and it goes back to 'Open', etc., the script will modify the existing sheet row for that ticket ID and not create a new one. This handles changes/updates to tickets without incorrectly counting them as new tickets. Only if a ticket ID doesn't exist in the sheet is a new row added with the ticket ID. On each run, new changes will modify existing tickets or append the new tickets to the sheet, creating historical data.
`fetchTags`	Fetches tags from RichPanel and maps each tag ID to its name. This is part of the work explained above.
`findRowByTicketId`	Pinpoints the row in the sheet for a given ticket ID

Use Google Apps Script to import Richpanel data to Google Sheets

Script behavior

Script functions in detail

Error handling