Zoho Voice REST API via Google Sheet API Connector
This document provides a step-by-step guide on how to use the Voice REST API through the Google Sheet API Connector to populate voice data into a Google Sheet. Follow the instructions carefully to ensure successful integration.
Prerequisites
- Access to Google Sheets and a Google account.
- API Connector add-on installed in Google Sheets.
- Access to the Voice REST API with OAuth token.
Steps to Configure
Step 1: Open Google Sheets and create a new spreadsheet.
Step 2: Install API Connector.
- Go to Workspace → Install API Connector extension.
- Check the API connector in the Google Sheet extension tab.
Step 3: Configure the API Connector.
- Open the API Connector from the Extensions menu in Google Sheets.
- Click Create to set up a new API request.
- Fill in the following details:
- API URL: [Insert the target API URL here].
- Method: GET or POST (based on your API's requirement).
- OAuth: Go to Manage Connections → Add Custom OAuth.
- To set up a custom OAuth connection
- Visit https://api-console.zoho.com
- Create a new Server-based Application.
- Provide the Client Name, Homepage URL, and set the Redirect URL to: https://script.google.com/macros/d/12COOkin8nodCH7fZGIBu0D2jWY8-AEA0uvElt4Ph4wRbLUD4wslqQUfG/usercallback (as specified in the documentation).
- Once the application is created, copy the generated Client ID and Client Secret.
- In the Custom OAuth form inside API Connector:
- OAuth Grant Type: Authorization Code
- Name: ZohoVoiceOAuth
- Authorization Base URL: https://accounts.zoho.com/oauth/v2/auth
- Token URL: https://accounts.zoho.com/oauth/v2/token
- Client ID: Paste the Zoho OAuth Client ID
- Client Secret: Paste the Zoho OAuth Client Secret
- Click Save & Run to execute the request.
- Once the API request is successful, the data will be populated in the specified columns of your Google Sheet.
- Verify the data to ensure it matches the expected output.
Google Sheets Integration with Deluge
Step 1: Create Google Sheets connection.
- Go to Zoho Voice → Settings → Workflow → Connections.
- Click on Create Connection.
- Select the Google service.
- Provide a Connection Name (Use googlesheet - exactly this name).
- In the Scopes section:
- Search and select: https://www.googleapis.com/auth/spreadsheets
- This grants access to read/write Google Sheets.
- Click Create and Connect.
- Sign in with your Google Account and authorize access.
Now your connection is ready to use in Deluge scripts.
Step 2: Create Workflow & Add Script.
- Create a Workflow with Call Logs module.
- In the Function section, click New Function.
- Paste the script provided.
- At the top of the script, change:
sheetID = "your-google-sheet-id";
sheetName = "your-sheet-tab-name";
Example:
sheetID = "1b8CxYHkh0obXsJphn3sq56GheRnOV199eZu-lS2HIis";
sheetName = "ZVoiceLogs";
- Click Save and enable the workflow.
Note
Enable the Google Sheets API: Go to https://console.cloud.google.com/apis/library/sheets.googleapis.com and click Enable.
Deluge Script
Copied// ---------- CONFIG ----------
sheetID = "<SHEET ID";
sheetName = "<SHEET NAME>";
// ---------- Fetch Existing Headers ----------
fetchHeaderURL = "https://sheets.googleapis.com/v4/spreadsheets/"
+ sheetID + "/values/" + sheetName + "!A1:Z1";
headersResp = invokeurl
[
url : fetchHeaderURL
type : GET
connection : "googlesheet"
];
headerMap = Map();
headerRow = List();
indexOfCol = 0;
if(headersResp.containsKey("values"))
{
existingHeaders = headersResp.get("values").get(0);
for each colName in existingHeaders
{
key = colName.toLowerCase();
if(headerMap.containKey(key))
{
key = key + "_col" + indexOfCol;
}
headerMap.put(key, colName);
headerRow.add(colName);
indexOfCol = indexOfCol + 1;
}
}
else
{
// No headers found — create from data keys
headerRow = data.keys();
payload = map();
payload.put("range", sheetName + "!A1:Z1");
payload.put("majorDimension", "ROWS");
payload.put("values", {headerRow});
headerUpdateURL = "https://sheets.googleapis.com/v4/spreadsheets/"
+ sheetID + "/values/" + sheetName
+ "!A1:Z1?valueInputOption=RAW";
response = invokeurl
[
url : headerUpdateURL
type : PUT
parameters : payload.toString()
connection : "googlesheet"
];
for each colName in headerRow
{
headerMap.put(colName.toLowerCase(), colName);
}
}
// ---------- Flatten Multi-value Fields ----------
nestedFields = list();
nestedFields.add("departments");
nestedFields.add("agents");
nestedFields.add("call_queues");
flattenedData = map();
for each key in data.keys()
{
val = data.get(key);
if(nestedFields.contains(key))
{
nameList = list();
for each item in val
{
nameList.add(item.get("name"));
}
flattenedData.put(key, nameList.toString(", "));
}
else
{
flattenedData.put(key, val);
}
}
// ---------- Add New Headers (if needed) ----------
newHeaderFound = false;
for each key in flattenedData.keys()
{
if(!headerMap.containsKey(key.toLowerCase()))
{
headerRow.add(key);
newHeaderFound = true;
}
}
// ---------- Update Header Row if New Headers Exist ----------
if(newHeaderFound)
{
payload = map();
payload.put("range", sheetName + "!A1:Z1");
payload.put("majorDimension", "ROWS");
payload.put("values", {headerRow});
headerUpdateURL = "https://sheets.googleapis.com/v4/spreadsheets/"
+ sheetID + "/values/" + sheetName
+ "!A1:Z1?valueInputOption=USER_ENTERED";
response = invokeurl
[
url : headerUpdateURL
type : PUT
parameters : payload.toString()
connection : "googlesheet"
];
}
// ---------- Construct Row Data ----------
rowData = list();
for each col in headerRow
{
key = col.toLowerCase();
val = flattenedData.get(key);
if(val == null)
{
rowData.add("");
}
else
{
rowData.add(val);
}
}
// ---------- Append Data Row ----------
payload = map();
payload.put("values", {rowData});
appendURL = "https://sheets.googleapis.com/v4/spreadsheets/"
+ sheetID + "/values/" + sheetName
+ "!A1:append?valueInputOption=USER_ENTERED";
response = invokeurl
[
url : appendURL
type : POST
parameters : payload.toString()
connection : "googlesheet"
];
info "Data appended to sheet successfully.";