If you are using CallRail to analyze and optimize your marketing impact, there is an abundance of data at your fingertips. Thankfully, CallRail offers a wide range of reports to visualize this data, ranging from clean and simple to robust and powerful. You can even create custom reports incorporating the measures and dimensions you care about.
Sometimes, though, you may find that you need to export your CallRail data into a spreadsheet. This could be for a number of reasons, such as:
- On-the-go calculations or aggregations
- Combining the data with that of other sources
- Using the spreadsheet as an intermediary between another service (e.g. a dashboarding service like Geckoboard)
Thankfully, Google Sheets makes it easy to get data from external sources through the use of its Google Apps Script editor. With just a few steps, you can create a script to poll any API, retrieve the data and append it to a sheet, then set up a time-driven “trigger” to run the script regularly. The data will update automatically so you can build off of the data input to the sheet confidently knowing it’s up to date.
Below are the step-by-step instructions to get a working example of how to get recent calls from your CallRail account into a Google Sheet.
Step 1: Get a few values from CallRail
First off, you’ll need a CallRail account. If you don’t have one already, start a free 14-day trial today, create a phone number and place a few test calls. Next, you’ll need an API Key. You can get an API key in your user profile section. Check out our support article for more details on how to create a new API Key.
Last, you’ll need to know your CallRail account number for the account that you’re interested in viewing calls from. You can get this number in 2 places:
- There is a 9-digit number in the URL of any page in the app, found here: app.callrail.com/v2/a/[9-digit-account-number]/… That is your account number.
- You can also find your account number by clicking the box in the top left corner, which expands the Account Overview panel. Your account number is directly under “Manage Account”
Step 2: Prepare the Google Sheet
- Create a new Google Sheet and open the script editor: click Tools in the toolbar, then “< > Script editor”.
- Copy + paste the following code into the script editor. It’s okay to remove the dummy function myFunction ().
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);
}
}
- Update the script to include your API key and account number (from step 1) and click Save. Lines 1 & 2 are the only lines you should need to update in the code.
- Run the script to test that it works properly. In the script editor, select “GetTodaysCalls” from the function dropdown then click the Play icon
- Click “Allow” on the prompt requesting permission.
- After the script runs, you should see your sheet populate with today’s phone calls.
Step 3: Create a trigger to run the script automatically
Open the Google Apps Script Project Triggers page by clicking the triggers icon, here:
Then click “create a new trigger”. Choose “getTodaysCalls” from the function list, and choose “Time-driven” from the “Select event source” dropdown. Set your preference for how often you’d like the script to run, and click Save.
Other Potential Uses
This script uses the Calls endpoint from the CallRail API, but you could modify the script to include functions to poll any endpoint you wish, such as Form Submissions, Text Messages, Trackers, etc. Or use the Summarizing Call Data by Time Series endpoint to view aggregate call data for an account or company, grouped by date. You could also add various filters, date ranges and field selection to get just the data you need. The full list of endpoints, filters, date ranges, and fields available can be found on our API docs.
With your CallRail data available and updating automatically, you can get the answers you need to maximize your marketing impact and get on with your day.
The post Import CallRail Data to Google Sheets appeared first on CallRail.