Export Organizations To Google Sheets

---

Leverage the Zendesk API combined with Google Sheets to easily export orgs

There are a few ways to export your orgs from Zendesk typically using 3rd party apps or via API but it can be expensive or daunting. This post gives you a simple and free three-step process to export all organization data directly to a Google Sheet.

Let’s step through how you can achieve this using only Google Sheets and a script.

There are 3 steps involved:

1. Generate an API key in Zendesk to allow access to Google Sheets

2. Create a Google Sheet and populate it with your data

3. Create a Google AppScript using the code provided

---

Generate API Key

You'll need an API key to allow you script access to your Zendesk instance. You can revoke it later if you like.

1. Open Zendesk Admin Center

2. Navigate to Apps & Integrations > Zendesk API

3. Click Add API Token

4. Give it a descriptive name so you or another admin recognises it in the future

5. Copy the key to your clipboard and save it somewhere securely like a password manager

6. Click Save. You'll need this key later.

Generate an API key in Zendesk to allow Google Sheets access your orgs

---

Create Google Sheet

1. Go to https://docs.google.com/spreadsheets

2. Log in to your Google account or create a new one

3. Create a new blank Google Sheet

---

Create Your Script

1. From within your Google Sheets file click Extensions > Apps Script

2. Give your project a meaningful name

3. Copy the following script to your clipboard

var zendeskSubdomain = 'yourSubdomain';

var zendeskEmail = 'yourZendeskAdminEmail/token';

var zendeskApiToken = 'yourToken';

function exportZendeskOrganizations() {

// API endpoint to fetch organizations

var url = 'https://' + zendeskSubdomain + '.zendesk.com/api/v2/organizations.json';

// Fetch organizations

var response = fetchZendeskData(url, zendeskEmail, zendeskApiToken);

if (!response) {

Logger.log('Failed to fetch data from Zendesk.');

return;

}

var organizations = response.organizations;

// If there are more pages, fetch them

while (response.next_page) {

response = fetchZendeskData(response.next_page, zendeskEmail, zendeskApiToken);

organizations = organizations.concat(response.organizations);

}

// Define headers for the Google Sheet (all possible fields)

//Organization Fields is included for completeness. This is for custom fields. Learn more here: https://developer.zendesk.com/api-reference/ticketing/organizations/organizations/#custom-organization-fields

var headers = [

'ID', 'URL', 'External ID', 'Name', 'Domain Names', 'Created At', 'Updated At', 'Group ID',

'Shared Tickets', 'Shared Comments', 'Notes', 'Details', 'Tags', 'Organization Fields'

];

// Get active spreadsheet and sheet

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// Clear any existing content

sheet.clear();

// Set headers in the first row

sheet.appendRow(headers);

// Add organization data to the sheet

organizations.forEach(function(org) {

var row = [

org.id,

org.url,

org.external_id,

org.name,

org.domain_names ? org.domain_names.join(', ') : '',

org.created_at,

org.updated_at,

org.group_id,

org.shared_tickets,

org.shared_comments,

org.notes,

org.details,

org.tags ? org.tags.join(', ') : '',

JSON.stringify(org.organization_fields)

];

sheet.appendRow(row);

});

Logger.log('Export complete.');

}

function fetchZendeskData(url, zendeskEmail, zendeskApiToken) {

var options = {

'method': 'GET',

'headers': {

'Authorization': 'Basic ' + Utilities.base64Encode(zendeskEmail + ':' + zendeskApiToken)

}

};

try {

var response = UrlFetchApp.fetch(url, options);

return JSON.parse(response.getContentText());

} catch (e) {

Logger.log('Error fetching data: ' + e.message);

return null;

}

}

4. Paste your script into the main window ensuring you overwrite anything already in that window

5. Replace the text 'yourSubdomain' with your actual sub-domain. You can find your sub-domain in Admin Center > Brand

6. Replace the text 'yourZendeskAdminEmail' with your Zendesk administrator email

7. Replace the text 'yourToken' with the API token you created above

8. Click the Save icon

9. Click Run - you’ll be prompted to Review Permissions

10. Choose your Google account - you need to authorise that your Google account where the script and Google Sheet are stored can access your Zendesk data

This warning is because you're about to let the script execute on your Google Account.

11. You may or may not receive a warning. If you do, click Advanced

Click Go to Export Zendesk Organizations (unsafe)

12. Click Go to Export Zendesk Organizations (unsafe) - or whatever you named your script and click Allow

Google Access Permission

13. Your script will run now and you should see an Execution Log beneath the script

Execution Log

---

To Recap

To do a one-click export of all your Zendesk orgs to Google Sheets just set up this script to access and export with a single click.