Hello dev community,
I am asking for a bit of help with something I am working on for one of my customers. I've been working on writing a Google Apps Script for Google Sheets that will pull the full detailed call log for an account log and dump it into a Google Sheet. The goal here is to use Sheets to enable some of the business users to do their own analysis of call log data directly inside of sheets without having to daily import CSV's. I am not a developer by any means, but I have been able to get quite a bit done with the help of Dave Hamel and Embbnux Ji and a few others from the RingCentral team.
Here's what I've got, I've gone through developer.ringcentral.com and created an App that uses only the Call Log API's and got it to pass the sandbox testing and it is now promoted for Production use. The Google App script I've got right now pulls only the specific user extension dumps the data into the Sheet (which is what Embbnux got working). The script needs to be tweaked to search for all extensions and then loop to pull the detailed call log for all users. For simplicity, I'd like it to just capture the last 30 days. Benjamin created a detailed call log export that pulls everything for an account as show in the link below.
https://github.com/bdeanindy/ringcentral-call-log-download-demo/blob/master/index.js
I've used the Article here to help me get things rolling with the original Google Apps script. https://medium.com/ringcentral-developers/using-ringcentral-api-in-google-apps-script-a91c3367f37c
Below is the content of my Google Sheets Script that I have right now.
Any thoughts or recommendations on what I'd need to tweak to get this working and pull in the full call log?
var RC_APP = {
CLIENT_ID: '[scrubbed]',
CLIENT_SECRET: '[scrubbed]',
SERVER: 'https://platform.ringcentral.com', // sandbox or production server
};
function getOAuthService() {
return (
OAuth2.createService('RC')
.setAuthorizationBaseUrl(RC_APP.SERVER + '/restapi/oauth/authorize')
.setTokenUrl(RC_APP.SERVER + '/restapi/oauth/token')
.setClientId(RC_APP.CLIENT_ID)
.setClientSecret(RC_APP.CLIENT_SECRET)
.setCallbackFunction('authCallback')
.setPropertyStore(PropertiesService.getUserProperties())
.setCache(CacheService.getUserCache())
.setTokenHeaders({
Authorization: 'Basic ' + Utilities.base64EncodeWebSafe(RC_APP.CLIENT_ID + ':' + RC_APP.CLIENT_SECRET)
})
);
}
function logout() {
var service = getOAuthService();
service.reset();
}
function authCallback(callbackRequest) {
try {
var authorized = getOAuthService().handleCallback(callbackRequest);
if (authorized) {
return HtmlService.createHtmlOutput(
'Success! <script>setTimeout(function() { top.window.close() }, 1);</script>'
);
} else {
return HtmlService.createHtmlOutput('Denied');
}
} catch (err) {
console.log('===>ERROR: auth callback', err);
return HtmlService.createHtmlOutput('Denied');
}
}
function showSidebar() {
var service = getOAuthService();
if (!service.hasAccess()) {
var authorizationUrl = service.getAuthorizationUrl();
var template = HtmlService.createTemplate(
'<a href="<?= authorizationUrl ?>" target="_blank">Authorize</a>. ' +
'Reopen the sidebar when the authorization is complete.');
template.authorizationUrl = authorizationUrl;
var page = template.evaluate();
SpreadsheetApp.getUi().showSidebar(page);
} else {
var res = makeRequest({ path: '/restapi/v1.0/account/~/extension/~' });
// var res1 = makeRequest({ path: '/restapi/v1.0/account/~/extension/~/call-log', query: { dateFrom: '2020-04-11T02:44:00.000Z' } });
// var text = JSON.stringify(res1, null, 2);
var template = HtmlService.createTemplate('authorized:' + res.name);
var page = template.evaluate();
SpreadsheetApp.getUi().showSidebar(page);
}
}
// function showCallLog() {
// var calls = getCallLog(7)
// SpreadsheetApp.getUi().alert(JSON.stringify(calls, null, 2));
// }
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu('RingCentral')
.addItem('Authorization', 'showSidebar')
.addItem('Sync Call Log', 'logCallsIntoSheet')
.addToUi();
}
function onInstall(e) {
onOpen(e);
}
function makeRequest(options) {
var method = options.method;
var path = options.path;
var body = options.body;
var query = options.query;
if (body) {
body = JSON.stringify(body)
}
if (query) {
var queryString = Object.keys(query).map(function(key) {
return encodeURIComponent(key) + '=' + encodeURIComponent(query[key]);
}).join('&');
path = path + '?' + queryString;
}
var service = getOAuthService();
var response = UrlFetchApp.fetch(RC_APP.SERVER + path, {
headers: {
Authorization: 'Bearer ' + service.getAccessToken()
},
payload: body,
contentType: 'application/json',
method: method || 'get',
muteHttpExceptions: true
});
var json = JSON.parse(response.getContentText('utf-8'));
var code = response.getResponseCode();
if (code >= 200 && code < 300) {
return json;
} else if (code == 401 || code == 403) {
console.log(
'error',
'will logout due to get code ',
code,
' when request ',
url,
' with ',
opts
);
service.reset();
throw 'Token expired';
} else {
console.log('error', 'RingCentral Backend Server Error', path, json);
throw 'RingCentral Backend Server Error: ' + (json.message || json.error_description);
}
}
function getExtensionInfo() {
var response = makeRequest({ path: '/restapi/v1.0/account/~/extension/~' });
return response;
}
function getDateFrom(daySpan) {
const d = new Date(Date.now() - daySpan * 24 * 60 * 60 * 1000);
d.setHours(0);
d.setMinutes(0);
d.setSeconds(0);
d.setMilliseconds(0);
return d;
}
function getCallLog(daySpan = 7) {
var dateFrom = getDateFrom(daySpan).toISOString()
var response = makeRequest({ path: '/restapi/v1.0/account/~/extension/~/call-log', query: { dateFrom } });
return response.records;
}
function logCallsIntoSheet() {
var sheet = SpreadsheetApp.getActiveSheet();
var calls = getCallLog(7);
var call = calls[0];
sheet.appendRow([call.id, call.sessionId, call.direction, call.startTime, call.duration, call.type, call.action, call.result, call.to && (call.to.phoneNumber || call.to.extensionNumber), call.to && call.to.name, call.from && (call.from.phoneNumber || call.from.extensionNumber), call.from && call.from.name]);
}
