Export Organizations To Google Sheets

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

Step 1. Generate API Key

You’ll need an API key to allow 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 recognize it in the future
5. Copy the key and save it securely (e.g. in a password manager)
6. Click Save. You’ll need this key later.

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

Step 3. 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 into the editor

var zendeskSubdomain = 'yourSubdomain';
var zendeskEmail = 'yourZendeskAdminEmail/token';
var zendeskApiToken = 'yourToken';

function exportZendeskOrganizations() {
  var url = 'https://' + zendeskSubdomain + '.zendesk.com/api/v2/organizations.json';
  var response = fetchZendeskData(url, zendeskEmail, zendeskApiToken);
  if (!response) {
    Logger.log('Failed to fetch data from Zendesk.');
    return;
  }

  var organizations = response.organizations;
  while (response.next_page) {
    response = fetchZendeskData(response.next_page, zendeskEmail, zendeskApiToken);
    organizations = organizations.concat(response.organizations);
  }

  var headers = [
    'ID', 'URL', 'External ID', 'Name', 'Domain Names', 'Created At', 'Updated At', 
    'Group ID', 'Shared Tickets', 'Shared Comments', 'Notes', 'Details', 'Tags', 
    'Organization Fields'
  ];

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.clear();
  sheet.appendRow(headers);

  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 editor, overwriting anything already there

5. Replace ‘yourSubdomain’ with your actual subdomain (find it under Admin Center > Brand)

6. Replace ‘yourZendeskAdminEmail’ with your Zendesk administrator email

7. Replace ‘yourToken’ with the API token you created above

8. Click Save

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

10. Choose your Google account – you need to authorize access for this script

11. If you see a warning, click Advanced

12. Click Go to Export Zendesk Organizations (unsafe) (or your project name) and then click Allow

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

To Recap

To do a one-click export of all your Zendesk orgs to Google Sheets, set up this script and you’ll be able to export with a single click.