Tutorial: Add start date params to filter transactions data in Google Sheets

This tutorial supplements our Pull transactions data into Google Sheets tutorial. The difference is that instead of returning all of the data from your school’s transactions, we will be able to filter our transactions by setting a start date.

Note: Users can easily enter a start date from when they want to see their school’s transactions up until today. The end date is defaulted to today’s date.

Before following the below steps, you will need to follow steps 1 - 3 from the Pull transactions data into Google Sheets tutorial first.

Once you have your Apps Scripts open, 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',
   muteHttpExceptions: true,
   headers: { Accept: 'application/json', apiKey: '' }
 };


 if (params['headers']['apiKey'] == "") {
   let result = ui.prompt('API Key', 'Please enter your Teachable key previously generated.', ui.ButtonSet.OK_CANCEL)
   let start_date = ui.prompt('Start Date',"Please enter the beginning of the time period \n to return results for (exclusive), in ISO8601 format.\n Ex: 2022-01-31", ui.ButtonSet.OK_CANCEL).getResponseText();


   // ISO 8601 | YYYY-MM-DD
   let start_date_formated = new Date(start_date).toISOString();
   let end_date_formated = new Date().toISOString();


   // Check to make sure the start date is valid
   // End date is defaulted to today 


   if(start_date_formated <= end_date_formated) {
     endpoint = endpoint + `?start=${start_date_formated}&end=${end_date_formated}`;
   } else {
     SpreadsheetApp.getUi().alert('Error','Please make sure your start date is before today\'s date.', ui.ButtonSet.OK);
     return
   }


   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 ui = SpreadsheetApp.getUi();
 var sheet = ss.getActiveSheet();
 sheet.clear() // Resets spreadsheet when rerun


 var transactions = getTeachableTransactions(); // Calls api


 if (transactions[0] == null || undefined) {
   SpreadsheetApp.getUi().alert('There are no transactions that match these filters.', ui.ButtonSet.OK);
   return
 }
  let output = []
 let keys = Object.keys(transactions[0]) // Headers for the sheet
 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).

Next, enter in the start date from when you want to see your transactions. Make sure that the date is in ISO8601 format. Note that this date is exclusive, so if you want to include a specific start date, make sure to enter in the day before. The default end date will be today’s date and is inclusive.

If there is no data that fits your filter search, you will receive an alert and the script will end.
Once the script finishes running, your transaction data should appear in your Google Sheet!
Note: When you rerun the script with new start dates, this will clear your original data in your spreadsheet.