question

Matt LaHatt avatar image
Matt LaHatt asked Phong Vu answered

Need help with a Google Apps Script for Google Sheet that pulls in the full detailed Call log

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


rest api
1 |3000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

1 Answer

Phong Vu avatar image
Phong Vu answered

Not sure how many developers in this Forum are familiar with Google script to help you. But alternatively, one can use a RingCentral supported SDK, for example the Node JS to access the call log, then use Google Sheets API to import the data.

1 |3000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Developer sandbox tools

Using the RingCentral Phone for Desktop, you can dial or receive test calls, send and receive test SMS or Fax messages in your sandbox environment.

Download RingCentral Phone for Desktop:

Tip: switch to the "sandbox mode" before logging in the app:

  • On MacOS: press "fn + command + f2" keys
  • On Windows: press "Ctrl + F2" keys