Answered

Call Rail to Google Sheets

I am trying to pull call the calls into a google spreadsheet automatically . This article is out of date.

I have followed the step exactly and they have updated to sheet to App Scripts no Script Editor. I know its the same  but I keep getting syntax errors on the API key, Agency ID,  ss.getsheet by name.  Is there any updated script? I have followed the instructions exactly and it won't save.

https://www.callrail.com/blog/import-callrail-data-to-google-sheets

 

 var AGENCYID = [57379****]; // like var AGENCYID = 000000000;

var TOKEN = '2c78f237824d1c57b********'; //like var TOKEN = ‘123abc456def789hij012klm345nop’;

function getSheet() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheetByName(Sheet1);

  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);

  }

}  
 


0

Comments

4 comments
Date Votes
  • Official comment

    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);
    
      }
    
    }
  • Hello Robin,

    I have issues using the above script, I have used the exact one as provided.

    var AGENCYID = [250xxxxx]; // like var AGENCYID = 000000000;

    var TOKEN = '[77406cece7b78368f49f4bf49xxxxx]'; //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);

      }

    }

    Below is the error I get when I try running it for (gettodaycalls)

    7:47:37 PM    Error    
    Exception: Request failed for https://api.callrail.com returned code 401. Truncated server response: {"error":"HTTP Token: Access denied"} (use muteHttpExceptions option to examine full response)
    fetchCalls    @ Code.gs:51
    getTodaysCalls    @ Code.gs:67

    Kindly please let me know, what I am doing wrong here ?

    0
  • Hi, I use the same script..

    var AGENCYID = 6194XXXX; // like var AGENCYID = 000000000;

    var TOKEN = '[505f2b263f1efXXXXXXXXX]'; //like var TOKEN = '123abc456def789hij012klm345nop';

    function getSheet() {

      var ss = SpreadsheetApp.getActiveSpreadsheet();

      var sheet = ss.getSheetByName('Sheet'); // 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);

      }

    }

    Below is the error I get when I try running it for (gettodaycalls)

    8:37:32 PM
    Error
    Exception: Request failed for https://api.callrail.com returned code 401. Truncated server response: {"error":"HTTP Token: Access denied"} (use muteHttpExceptions option to examine full response)
    Code.gs:51
    Code.gs:67
    0
  • I'm getting the exact same error.

    9:01:12 PM

    Error
    Exception: Request failed for https://api.callrail.com returned code 401. Truncated server response: {"error":"HTTP Token: Access denied"} (use muteHttpExceptions option to examine full response)
    Code.gs:51
    Diid anyone figure out how to get this working??
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post