Request for Assistance with CMR Integration for Google Sheets

[MayurMali]

Hello 3CX Community,

I'm currently working on an integration between 3CX and Google Sheets and need some guidance on using Call Management Reports (CMR) instead of Call Data Records (CDR) for this purpose.

Objectives:​

1. Fetch Call Management Reports (CMR): I need to retrieve call metrics and detailed reports from 3CX.
2. Integrate with Google Sheets: Import the CMR data into Google Sheets, updating columns such as Call Date/Time, Caller ID, Callee ID, Call Duration, and Call Recording URL.

My Current Setup:​

• Google Sheets: Set up with necessary columns for call data.
• Script: Planning to use Google Apps Script to automate data fetching and updating.

Specific Questions:​

1. API Access for CMR: Does 3CX provide API access for Call Management Reports, and if so, how can I use it?
2. Endpoint Details: What is the endpoint URL for fetching CMR data?
3. Authentication: What type of authentication is required for accessing CMR via the API (e.g., API keys, OAuth)?
4. Data Structure: What is the expected data structure for CMR responses? Sample JSON format would be very helpful.
5. Best Practices: Are there any best practices or considerations when integrating CMR data with external tools like Google Sheets?

Sample Google Apps Script:​

Here’s a sample script I plan to use for fetching and updating the CMR data in Google Sheets. Any feedback or improvements would be greatly appreciated:

javascript
Copy code
function updateCMRData() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Replace with your sheet name
const url = "https://your-3cx-server.com/api/getCMR"; // Replace with your actual CMR API endpoint
const options = {
method: "get",
headers: {
"Authorization": "Bearer YOUR_API_TOKEN", // Replace with your actual API token
"Content-Type": "application/json"
},
muteHttpExceptions: true
};

const response = UrlFetchApp.fetch(url, options);

if (response.getResponseCode() === 200) {
const data = JSON.parse(response.getContentText());
const rows = data.records.map(record => [
record.callDateTime,
record.callerId,
record.calleeId,
record.callDuration,
record.callRecordingUrl
]);

sheet.getRange(2, 1, rows.length, rows[0].length).setValues(rows); // Assumes headers are in the first row
} else {
Logger.log("Error: " + response.getContentText());
}
}


I appreciate any help or pointers you can provide on accessing and integrating CMR data with Google Sheets.

Thank you in advance!