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.
![google sheets - extensions - apps script.jpg 857](https://files.readme.io/781f249-google_sheets_-_extensions_-_apps_script.jpg)
- 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.
![code.gs blank.jpg 842](https://files.readme.io/cb4f4c8-code.gs_blank.jpg)
- 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.
![app script - save proj icon.jpg 920](https://files.readme.io/21af02a-app_script_-_save_proj_icon.jpg)
- On the top navigation menu, ensure that buildMenuOnOpen is selected from the dropdown menu.
![app script - buildmenuonopen.jpg 959](https://files.readme.io/b64a7bd-app_script_-_buildmenuonopen.jpg)
- Click Run. An Authorization required popup window will open—click Review permissions.
![appscript - run - review perms.jpg 1195](https://files.readme.io/760d2fd-appscript_-_run_-_review_perms.jpg)
- In the popup window, you must select your Google account, then click Allow to authorize the connection.
![appscript - google auth.jpg 630](https://files.readme.io/983f788-appscript_-_google_auth.jpg)
- The Apps Script project should indicate Execution completed in the Execution log.
![app script - execution log.jpg 1108](https://files.readme.io/adc3d93-app_script_-_execution_log.jpg)
- Return to your empty Google Sheet. Select the new Teachable API dropdown menu from the top navigation bar, then click Get Data.
![app script - teachable api - get data.jpg 969](https://files.readme.io/f4300e6-app_script_-_teachable_api_-_get_data.jpg)
- 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).
![app script - enter api key.jpg 1277](https://files.readme.io/68d8221-app_script_-_enter_api_key.jpg)
- Once the script finishes running, your transactions data should appear in your Google Sheet!
![apps script - finished project.jpg 1309](https://files.readme.io/08a1516-apps_script_-_finished_project.jpg)
Updated about 1 year ago