Geckoboard

You can use the Google Apps Script platform to get ticket data automatically from Richpanel into Google Sheets and then create a new <a href="https://support.geckoboard.com/en/collections/3381709-connect-your-own-data">Spreadsheets</a> widget.

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

Find your <a href="https://developer.richpanel.com/docs#get-api-key" rel="nofollow noopener noreferrer" target="_blank">Richpanel API key</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 the 'Ticket Data' sheet tab is now populated with your Richpanel ticket data. 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>.

Once connected, you can build dashboard widgets from your Richpanel data. For guides to creating spreadsheet dashboard widgets, select from the following visualization types:

1. 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>.
2. Find your <a href="https://developer.richpanel.com/docs#get-api-key" rel="nofollow noopener noreferrer" target="_blank">Richpanel API key</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 the 'Ticket Data' sheet tab is now populated with your Richpanel ticket data. 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>.
9. Once connected, you can build dashboard widgets from your Richpanel data. For guides to creating spreadsheet dashboard widgets, select from the following visualization types:

<a href="https://support.geckoboard.com/en/articles/6055539-create-number-widgets-from-spreadsheets">Number</a>

<a href="https://support.geckoboard.com/en/articles/6055586-create-line-chart-widgets-from-spreadsheets">Line chart</a>

<a href="https://support.geckoboard.com/en/articles/6055601-create-column-chart-widgets-from-spreadsheets">Column chart</a>

<a href="https://support.geckoboard.com/en/articles/6055613-create-bar-chart-widgets-from-spreadsheets">Bar chart</a>

<a href="https://support.geckoboard.com/en/articles/6055622-create-leaderboard-widgets-from-spreadsheets">Leaderboard</a>

<a href="https://support.geckoboard.com/en/articles/6055630-create-table-widgets-from-spreadsheets">Table</a>

- <a href="https://support.geckoboard.com/en/articles/6055539-create-number-widgets-from-spreadsheets">Number</a>
- <a href="https://support.geckoboard.com/en/articles/6055586-create-line-chart-widgets-from-spreadsheets">Line chart</a>
- <a href="https://support.geckoboard.com/en/articles/6055601-create-column-chart-widgets-from-spreadsheets">Column chart</a>
- <a href="https://support.geckoboard.com/en/articles/6055613-create-bar-chart-widgets-from-spreadsheets">Bar chart</a>
- <a href="https://support.geckoboard.com/en/articles/6055622-create-leaderboard-widgets-from-spreadsheets">Leaderboard</a>
- <a href="https://support.geckoboard.com/en/articles/6055630-create-table-widgets-from-spreadsheets">Table</a>
- <a href="https://support.geckoboard.com/en/articles/6055644-create-text-widgets-from-spreadsheets">Text</a>

The script fetches ticket data from the RichPanel API and updates the sheet 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 does offer more.

- 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 does offer more.

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

Learn how to use a script to get data automatically 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

Script behaviour

Script functions in detail

Error handling