Prerequisite

1. User must have an account in Zoho Sheet and Qntrl
   
2. User must configure a Connection to authorize the access of Zoho Sheet from Qntrl. 
   

Business Case

1. Reduce manual effort of recording client details in each card.
   
2. Maintain a single repository of client information.
   
3. Automate data entry to reduce errors.
   

Solution

1. The user creates a card by entering just the Client ID along with the Card details.
   
2. Once the Card is created, a Custom Function is triggered to pass the data from Zoho Sheet to the card.
   
3. After the details are automatically updated to the card, the status update is sent back to Zoho Sheet. 
   

How to configure

1. Mandate the Client ID field. This step is not compulsory, but it is recommended.
   
2. Disable the Visibility of the client detail fields Client Name, Contact, and Client Location. 
   

1. Note that the client ID along with other client details is stored in a single row in Zoho Sheet. 
   

//Deluge has predefined integrations with zoho services. Zoho Sheet is also supported

// https://www.zoho.com/deluge/help/sheet/get-records.html#Syntax

//sheet's getRecords API can be used to fetch all records in a sheet or specific records based on a criteria

//Required parameters

//1. Resource ID - can be picked from sheet url

//2. Sheet Name - Name of the sheet at the bottom

//3. Query Map - Key value map used to set criteria & range https://www.zoho.com/deluge/help/sheet/get-records.html#Example2

//4. connection link name

queryMap = Map();

//criteria format: "column name"="column value"

//example: "Client ID"="1001"

queryMap.put("criteria","\"Client ID\"=" + client_id);

data = zoho.sheet.getRecords("krz56389e2107d77a434ab731d3271a00c77d","Sheet1",queryMap,"zoho_sheet");

//this will fetch the records matching the criteria above

records = data.getJson("records");

//In our case, only record will be returned as client id is unique. So we are fetching the record at index 0

record = records.get(0);

//fetching each value by its column name

name = record.get("Client Name");

contact = record.get("Contact").toString();

location = record.get("Location");

info name;

//debugger print name

paramMap = Map();

//constructing the values in params for updating in card

//mapping with associated field name in qntrl

paramMap.put("customfield_shorttext18",name);

paramMap.put("customfield_shorttext16",contact);

paramMap.put("customfield_shorttext11",location);

job = invokeurl

[

url :" https://core.qntrl.com/blueprint/api/zylker/job/ " + job_id

type :POST

parameters:paramMap

connection:"zoho_qntrl"

];

info job;

//debugger to print update job response

//updating the record in sheet https://www.zoho.com/deluge/help/sheet/update-records.html#Example2

//we are using the same criteria that was used to fetch the record

newValues = Map();

newValues.put("Status","Updated");

optMap = Map();

updateResp = zoho.sheet.updateRecords("krz56389e2107d77a434ab731d3271a00c77d","Sheet1","\"Client ID\"=" + client_id,newValues,optMap,"zoho_sheet");

info updateResp;