Call Rail to Google Sheets

• Official comment

  

  Robin

  June 27, 2023 18:56

  Community manager

  Hey David, 

  It looks like there are curly quotes in the syntax that don't always play well. Try using this version below that only uses straight quotes.

  We'll get this updated in the blog as well! Thank you for bringing this to our attention, and let me know if this does not work for you either. 

  var AGENCYID = [your_account_ID]; // like var AGENCYID = 000000000;
  
  var TOKEN = '[your_api_key]'; //like var TOKEN = '123abc456def789hij012klm345nop';
  
  function getSheet() {
  
    var ss = SpreadsheetApp.getActiveSpreadsheet();
  
    var sheet = ss.getSheetByName('Sheet1'); // you can change this to whatever sheet you want to populate data on.
  
    return sheet;
  
  }
  
  function writeToSpreadsheet(data) {
  
    var sheet = getSheet();
  
    var calls = data.calls;
  
    // if its the first page, clear the sheet and create a header row.
  
    if (data.page == 1) {
  
      sheet.clear();
  
      var columnNames = Object.keys(calls[0]);
  
      sheet.appendRow(columnNames);
  
    } 
  
    calls.forEach(function(call) {
  
      sheet.appendRow(Object.values(call));
  
    });
  
  }
  
  function fetchCalls(page) {
  
    var dateRange = 'today'; // you can change this to any acceptable date range.
  
    var fields = 'company_id,company_name,direction'; // you can change this to any fields you would like to include.
  
    var url = 'https://api.callrail.com/v3/a/' + AGENCYID + '/calls.json?fields=' + fields + '&date_range=' + dateRange;
  
    url += '&page=' + page;
  
    var response = UrlFetchApp.fetch(url, {
  
      'method': 'get',
  
      'headers': { 'Authorization': 'Bearer ' + TOKEN }
  
    });
  
    return response;
  
  }
  
  function getTodaysCalls() {
  
    // fetch page one
  
    var initialFetchResponse = fetchCalls(1);
  
    var initialFetchResponseJson = JSON.parse(initialFetchResponse.getContentText());
  
    writeToSpreadsheet(initialFetchResponseJson);
  
    // now that we have our first response for the api we know the total pages
  
    // so now start fetching on page 2 and stop on the last page
  
    var resp;
  
    var parsedResp;
  
    for (var i = 2; i <= initialFetchResponseJson.total_pages; i++) {
  
      resp = fetchCalls(i);
  
      parsedResp = JSON.parse(resp.getContentText());
  
      writeToSpreadsheet(parsedResp);
  
    }
  
  }

Please sign in to leave a comment.