Tutorial: Pull transactions data into Google Sheets

By using the /transactions endpoint and a custom script, you can pull detailed data on your school transactions into Google Sheets.

The data returned from the endpoint provides you with granular data on each transaction made—and by easily transposing this data into an easy-to-read Google Sheet, you have flexibility to use this data for business analytics, uploading into another third-party tool you might use, or other custom needs.

To pull your transactions data into a Google Sheet, follow the below steps:

Create a new Google Sheet.
Click the Extensions dropdown from the top navigation menu, then select Apps Script. This will open up a new Apps Script tab in your browser.

Make sure you are on the "Code.gs" file page—then delete all of the code on the page (i.e., the function myFunction() {} text) so the page is blank.

Copy the code below and paste it in the blank Code.gs file.

function buildMenuOnOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Teachable API')
    .addItem('Get Data', 'displayAPIData')
    .addToUi();
}

function getTeachableTransactions() {
  let ui = SpreadsheetApp.getUi();
  var root = 'https://developers.teachable.com/v1/';
  var endpoint = 'transactions';

  var params = {
    method: 'GET',
    headers: { Accept: 'application/json', apiKey: '' }
  };

  if (params['headers']['apiKey'] == "") {
    let result = ui.prompt('Please enter your Teachable API Key', ui.ButtonSet.OK_CANCEL)
    var button = result.getSelectedButton();
    var text = result.getResponseText();
    params['headers']['apiKey'] = text
  }

  //Call the URL
  let response = UrlFetchApp.fetch(root + endpoint, params);

  let json = JSON.parse(response.getContentText());

  let transactions = json['transactions']
  
  // fetch all pages of transactions
  while(json['meta']['page'] < json['meta']['number_of_pages']) {
    let page = json['meta']['page'] + 1
    response = UrlFetchApp.fetch(root + endpoint + "?page=" + page, params);
    json = JSON.parse(response.getContentText());
    transactions = transactions.concat(json['transactions'])
    if (json['meta']['number_of_pages'] > 100 ) {
      Utilities.sleep(1000)
    }
  }
  
  return transactions;
}


function displayAPIData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  var transactions = getTeachableTransactions(); // calls api

  let output = []
  let keys = Object.keys(transactions[0]) // headers for the sheet
  Logger.log(transactions[0])
  for(let i = 0; i < transactions.length; i++) {
    output.push(Object.values(transactions[i]))
  }

  
  sheet.getRange(1,1, 1, keys.length).setValues([keys])
  
  // insert data by selecting data range equal to the size of the data being added
  sheet.getRange(2, 1, transactions.length, keys.length).setValues(output) 
}

Click the Save project icon.

On the top navigation menu, ensure that buildMenuOnOpen is selected from the dropdown menu.

Click Run. An Authorization required popup window will open—click Review permissions.

In the popup window, you must select your Google account, then click Allow to authorize the connection.

The Apps Script project should indicate Execution completed in the Execution log.

Return to your empty Google Sheet. Select the new Teachable API dropdown menu from the top navigation bar, then click Get Data.

In the popup modal, enter in your Teachable API key and click OK. (For guidance on how to get your Teachable API key, check out the Authentication user guide).

Once the script finishes running, your transactions data should appear in your Google Sheet!