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

1. Go to Workspace → Install API Connector extension.

2. Check the API connector in the Google Sheet extension tab.

Step 3: Configure the API Connector.

1. Open the API Connector from the Extensions menu in Google Sheets.

2. Click Create to set up a new API request.

3. 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.

4. 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).

5. Once the application is created, copy the generated Client ID and Client Secret.

6. 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

7. 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   

1. Go to Zoho Voice → Settings → Workflow → Connections.

2. Click on Create Connection.

3. Select the Google service.

4. Provide a Connection Name (Use googlesheet - exactly this name).

5. In the Scopes section:

• Search and select: https://www.googleapis.com/auth/spreadsheets

• This grants access to read/write Google Sheets.

6. Click Create and Connect.

7. 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.

1. Create a Workflow with Call Logs module.

2. In the Function section, click New Function.

3. Paste the script provided.

4. At the top of the script, change:

sheetID = "your-google-sheet-id";

sheetName = "your-sheet-tab-name";

Example:

sheetID = "1b8CxYHkh0obXsJphn3sq56GheRnOV199eZu-lS2HIis";
            sheetName = "ZVoiceLogs";

5. 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 

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

Note: For additional support, reach out to us at support@zohovoice.com