Zoho Voice Integration with Google Sheets via 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

Open Google Sheets and create a new spreadsheet.
Go to Workspace → Install API Connector extension.
Check the API connector in the Google Sheet extension tab.
Open the API Connector from the Extensions menu in Google Sheets.
Click Create Request to set up a new API request.
Fill in the following details to request configuration:
- API URL: Paste the target API URL.
- Method: Select GET or POST based on your API's requirement.
- OAuth: Go to Manage Connections → Add Custom OAuth.
Set up a custom OAuth connection:
- Go to the link: 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 (specify as mentioned in this documentation).
Once the application is created, copy the generated Client ID and Client Secret.
In the API Connector, fill in the Custom OAuth form:
- 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

Create Google Sheets connection

Go to Zoho Voice → Settings → Workflow → Connections.
Click Create Connection.
Select the Google service.
Enter the Connection Name as googlesheet. Ensure the name matches exactly.
In the Scopes section, search and select the link: 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.

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";

For 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.";