Embedded Files
CallRail Google
Overview
Overview
A Google Sheets template & script for connecting to the CallRail API and dropping call data into a Google Sheet. The script is left a bit open ended so that data is imported into one tab and then copied into a second tab in order to allow for easier backlogging. You may need to do some editing within the script to select the fields you want imported, as well as to add your account number and API key. But once you're done, you can schedule these functions to import data daily for the previous day on a continuing basis and linking into GDS reports, etc.
Getting Started
Getting Started
1. To get started, make a copy of the sheet using the link below.
2. Once you've copied the sheet, you'll need to open the Script Editor and adjust three things: the fields you want to import, your Account ID and your API Key.
3. To select the fields you want to import, you'll need to update the fields listed in line 4 under fields_variable. You can find a full reference of available fields under the Calls report here. Make sure that you keep the formatting the same as the current list – comma separated with no spaces.
4. Next you'll need to add your Account ID number. You need to add this in line 5 right where it says CALLRAIL_ACCOUNT_NUMBER.
5. Next you'll need to enter your API Key in line 7 where it says CALLRAIL_API_KEY. Get info on how to obtain an API Key here.
6. Once you've done that, you should now be all set! In the main spreadsheet, you can test these imports by going to the custom menu item Sheet Controls > Import Call Data To Sheet and subsequently selecting Sheet Controls > Log Yesterday's Data. You may have to approve the script to run and re-run it when you first select this option.
7. If the data was properly imported into Data Import after the first function and then properly copied over into Data Store after the second, you're all set!
8. You can now go back up to Sheet Controls and select Schedule Daily Automated Import to automate this process so that it imports & stores call data from CallRail for "yesterday" every day between 12am and 2am.
Notes
Notes
This script is only designed to import as many as 250 rows at a time (the max per page via the CallRail API. You'll need to update the script to iterate over every page available in order to capture more than 250 in any one data pull.
If you want to load in historical data with the same format here, you can simply manipulate the date_range variable on line 6 by changing it to all_time and then just manually pulling the data for page 1, 2, etc. as needed and logging the data between every import. An imperfect system, but the script was designed for a small account! Just be sure to reset the date_range variable back to yesterday before you schedule the data to pull automatically!
Full script for the Data Import is below for reference, but if you make a copy of the Google Sheet above, it'll already be pre-loaded into the Sheet's Script Editor!
CallRail Google Sheet Reporting Script
CallRail Google Sheet Reporting Script
function importYesterdaysCalls() {
//set custom fields. reference: https://apidocs.callrail.com/#welcome-to-callrail-api-v3
var fields_variable = 'answered,business_phone_number,call_type,company_id,company_name,created_at,customer_city,customer_country,customer_name,customer_phone_number,customer_state,device_type,direction,duration,first_call,id,lead_status,note,source,start_time,tags,total_calls,tracking_phone_number,value,voicemail,keywords,medium,referring_url,landing_page_url,last_requested_url,referrer_domain,utm_source,utm_medium,utm_term,utm_content,utm_campaign,speaker_percent,call_highlights,agent_email,campaign';
var account_id = 'CALLRAIL_ACCOUNT_NUMBER';
var date_range = 'yesterday';
var access_token = 'CALLRAIL_API_KEY';
//establishes url and options
var url = 'https://api.callrail.com/v3/a/'+account_id+'/calls.json?fields='+fields_variable+'&date_range='+date_range+'&per_page=250&page=1&sort=start_time&order=asc';
var options = {
method: 'GET',
headers: { 'cache-control': 'no-cache',
Connection: 'keep-alive',
'accept-encoding': 'gzip, deflate',
Accept: '*/*',
Authorization: 'Token token="'+access_token+'"'
}
};
//gets output and formats
var output = UrlFetchApp.fetch(url, options);
var content = output.getContentText();
var json = JSON.parse(content);
var calls = json.calls;
//establishes sheet
var sheet = SpreadsheetApp.getActive().getSheetByName('Data Import');
//builds array for fields
var fields_array = fields_variable.split(",");
//sets up array to build
var rows = [],
data;
for (i = 0; i < calls.length; i++) {
data = calls[i];
var columns = [];
//builds out array: columns
for (j = 0; j < fields_array.length; j++) {
columns.push(data[fields_array[j]]);
}
rows.push(columns);
}
//clear import sheet
var clearRange = sheet.getRange(2, 1, 250, fields_array.length);
clearRange.clearContent();
//reset headers
sheet.getRange('1:1').clearContent();
for (z=0; z<fields_array.length; z++){
sheet.getRange(1,z+1,1,1).setValue(fields_array[z]);
}
//log data
if(rows.length > 0) {
var dataRange = sheet.getRange(2, 1, rows.length, fields_array.length);
dataRange.setValues(rows);
}
else {
Logger.log('No calls recorded yesterday.');
}
}
Google Sites
Report abuse