Data Transfer to Zoho CRM

Hello everyone!

Welcome back to another exciting Kaizen post! This time, we will discuss the solution for a use case—leveraging Client Scripts and Functions to get the job done efficiently by integrating with Zoho Sheets.

In this kaizen post,

---

1. Handling Manual Entries of Data in Subforms
2. Scenario 
3. Solution
4. Add a button in Create Page(Canvas)
5. Create a Function to fetch data from Sheet
6. Create a Client Script to get Sheet Details, fetch and populate data
7. Summary
8. Related links

---

1. Handling Manual Entries of Data in Subforms

Manually entering data into Zoho CRM subforms is simple and straightforward when dealing with a few rows. However, repeatedly copying a large set of rows from a Sheet to a Subform can be challenging. Manually mapping lookup fields and adding parent IDs is not only time-consuming but also prone to errors, leading to inefficiencies in data entry. Automating this process can greatly improve efficiency, saving both time and effort.

Here’s how you can seamlessly fetch data from Zoho Sheets and populate Zoho CRM subforms—including lookup fields—using Client Scripts and Functions.

2. Requirement

Zylker manufactures medical instruments, and its sales representatives manage bulk orders from distributors and hospitals. Product details are typically provided in a sheet, which the sales reps must manually enter into the subform on the Order Creation Page. However, creating numerous entries is tedious, time-consuming, and prone to errors. To streamline the process, a "Populate from Sheet" button is embedded in the Order module of Create Page(Canavas). When clicked, it should

• Display a pop-up asking to enter the Sheet ID.
• Fetch order details (product names, quantities, and prices) from a specific Zoho Sheet.
• Fetch the id of the Product name from the Products module and populate the lookup field "Product".
• Populate other data into the Product list Subform inside Zoho CRM.

3. Solution 

To accomplish this requirement with a button click, you need to use a Canvas Page and add a button to it. (Currently, Client Scripts do not support Custom Buttons on the Create Page(Standard). When clicked, the Client Script should be triggered, it should retrieve the Sheet ID from the user, invoke a function to read the data, and populate the content into the Subform.

So you need to follow the following steps.

• Add a button in Create Page(Canvas)
• Create a Function to fetch data from Sheet
• Create a Client Script.

a. Add a button in Create Page(Canvas)

• Go to Settings > Canvas > Form View tab. Click the three dots next to the Canvas Order Create Page and click edit.
  
  
  

• Right click on the button and add the Element ID for the new button and click Save.
  
  
  
  

b. Create a Function to fetch data from Sheet

• Go to Setup > Developer Hub > Functions.
• In the Functions page, click + Create New Function.
• Choose the category as Button.
• Click Create.
• In the Create Function page, enter a name and description for your function.
• Write your function code. The below function is used to integrate CRM with Zoho Sheets and reads the content of the Sheet using zoho.sheet.getRecords(sheetid ,tab name)

1. SheetData = zoho.sheet.getRecords(sheetid,"Sheet1");
2. d = list();
3. for each  record in SheetData.get("records")
4. {
5. d.add(record);
6. }
7. info d;
8. return d.toString();

• Click Edit Argument next to the function's namespace and define the parameter "Sheetid" and click Save.
• Click the three dots next to the Function name and select REST API.
  
• Enable the OAuth2 for the Function csvRead as shown below.
  
  
  
  
  c. Create a Client Script
  
  
• Go to Setup > Developer Hub > Client Script. Click +New Script.
• Specify the details to create a script and click Next.
• Enter the following script and click Save.

1. var a = ZDK.Page.getField("Product_list").getValue();
2. var casesheetid = ZDK.Client.getInput([{ type: 'text', label: 'Enter the Sheet ID' }], 'Sheet details', 'OK', 'Cancel');
3.     console.log("casesheetid " + casesheetid);
4. if (casesheetid == null) {
5.         ZDK.Client.showAlert("Enter the *Case Sheet ID - Import* to import data");
6.     }
7.     let csvdata = []; // list to store csv data
8.     let imported_list = [
9.         {
10.         "Product_Name": {
11.         "id": "",
12.         "name": ""
13.         },
14.         "Quantity":"",
15.         "Unit_Price": "",
16.         "Amount": ""
17.         }
18.     ]
19.     try {
20. 
    
21.      ZDK.Client.showLoader({type: 'page', template:'vertical-bar', message: 'Loading ...'});
22.         resp = ZDK.Apps.CRM.Functions.execute("getData", casesheetid); // invoke function that extracts data from Sheet
23.     }
24.     catch {
25.         ZDK.Client.showAlert("Unexpected error occured" + resp);
26.     }
27.     var k = resp._details.userMessage;
28.     k = "[" + k + "]";
29.     let jsonArray = JSON.parse(k);//Convert to a valid JSON array format
30.     jsonArray.forEach((pr, i) => {
31.         csvdata.push(pr);
32.     });
33. 
    
34.     if (csvdata.length) {
35.         log("csvdata length" + csvdata.length);
36.         csvdata.forEach(row => {
37.             log("Product_Name" + row.Product_Name);
38.             log("Case Version" + row.Version);
39.             log("Case " + row.Case);
40.             imported_list.push({ Product_Name: { id: "", name: row.Product }, Quantity: row.Quantity, Unit_Price: row.Unit_Price, Amount: row.Amount});
41.         });
42.     }
43.     imported_list.shift(); // remove the dummy row
44.     imported_list.forEach((row, i) => {
45.         apiResponse = ZDK.Apps.CRM.Products.searchByCriteria(`(Product_Name:equals:${row.Product_Name.name})`);
46.  if (apiResponse.length == 0) {
47.             // If there is no matching record, ask user if a new record has to be created or not.
48.             var count = i + 1;
49.             ZDK.Client.showAlert("*S.No* " + count + " - '" + row.Product_Name.name + "'" + " is an *invalid Product Name*", "Invalid Product Name", "Okay");
50.         }
51.         apiResponse.forEach((rec) => {
52.             if (!(rec.id == null)) {
53.                 row.Product_Name.id = rec.id;
54.             }
55.         });
56.     });
57. ZDK.Page.getField('Product_list').setValue(imported_list);
58. ZDK.Client.hideLoader();

• In this code, several ZDKs are used for various purposes. Click on the ZDK hyperlinks to learn more.
  

1. User Input: The code uses ZDK.Client.getInput() to fetch a "Sheet ID" from the user. This input is crucial for further operations.
2. Input Validation: If the user cancels or doesn't enter a sheet ID, the code checks for null and prompts the user to enter the ID using ZDK.Client.showAlert().
3. Function Invocation: The code invokes a CRM function named getData using ZDK.Apps.CRM.Functions.execute(), passing the sheet ID as a parameter. 
4. Loader Display: To indicate that a process is running, a loader is displayed using ZDK.Client.showLoader(). 
5. Data Parsing and Transformation: The response from the function "resp" is parsed into a JSON array format. This involves converting the response into a string and then parsing it into a JSON object. The processed data is transformed into a format suitable for populating a CRM field. This involves creating a new list (imported_list) and pushing the transformed data into it.
6. Subform Population: Finally, the transformed data is used to populate the Subform named Product_list using ZDK.Page.getField().setValue(). After completing the process, the loader is hidden using ZDK.Client.hideLoader().

• Here is how the Client Script works.
  
  
  
• Here, using Client Script, you can instantly add all products from the Sheet with a single click and populate the lookup field without specifying ID for the lookup field "Product".
  
  

4. Summary

In this post, we have discussed,

• How to get input from the user and pass the input while invoking a Function
• How to Invoke a Function
• How to import data from Sheets to Zoho CRM Subform
• How to use Search Records API to fetch the ID of a field.
  

5. Related Links

• Client Script Overview
• Creating a Client Script
• Client Script Events
• Client Script IDE
• Use cases of Client Script
  
• Troubleshooting Client Script

Previous Post: Kaizen 179: Choosing between ZDK CLI and Web UI : When to Use Each |  Kaizen Collection: Home

• Topic Participants

• Maadhari M

  

• Sticky Posts

• Kaizen #197: Frequently Asked Questions on GraphQL APIs

  🎊 Nearing 200th Kaizen Post – We want to hear from you! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.

• Kaizen #198: Using Client Script for Custom Validation in Blueprint

  Nearing 200th Kaizen Post – 1 More to the Big Two-Oh-Oh! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.

• Celebrating 200 posts of Kaizen! Share your ideas for the milestone post

  Hello Developers, We launched the Kaizen series in 2019 to share helpful content to support your Zoho CRM development journey. Staying true to its spirit—Kaizen Series: Continuous Improvement for Developer Experience—we've shared everything from FAQs

• Kaizen #193: Creating different fields in Zoho CRM through API

  🎊 Nearing 200th Kaizen Post – We want to hear from you! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.

• Client Script | Update - Introducing Commands in Client Script!

  Have you ever wished you could trigger Client Script from contexts other than just the supported pages and events? Have you ever wanted to leverage the advantage of Client Script at your finger tip? Discover the power of Client Script - Commands! Commands

• Recent Topics

• Link Payment Mode and Paid Through Accounts

  For most users, it's very difficult for them to understand that the Payment Mode is totally independent of the Paid Through account when paying bills. It seems (and is) redundant for them to have to select what is basically the same thing twice. The current

• Lets enable business to choose the default payment mode

  Lets enable business to choose the default payment mode so that we do not have choose payment mode again and again for each and every transsctions

• Add Attachment Support to Zoho Flow Mailhook / Email Trigger Module

  Dear Zoho Support Team, We hope you are well. We would like to kindly request a feature enhancement for the Mailhook module in Zoho Flow. Currently, the email trigger in Zoho Flow provides access to the message body, subject, from address, and to address,

• South African Payment Gateways

  Since the "Demise" of Wave many South African users have moved over to Zoho and yet for years users have been requesting Integration with a South African Payment Gateway to no avail. Payfast was the most commonly requested gateway as it supports recurring

• Has anyone verified if Zoho is PCI compliant?

  We are planning on using Zoho to process payments via Authorize.net. We have everything set up and are attempting to complete the PCI DSS SAQ-A requirement for our merchant account. This requires us to prove Zoho has completed the SAQ-D for Service Providers. We need a way to verify compliance, or a copy of an attestation of compliance signed by the appropriate officer at Zoho. I assume I'm not the first person to use Zoho to process payment, and therefore not the first to require this information

• Support for Custom Fonts in Zoho Recruit Career Site and Candidate Portal

  Dear Zoho Recruit Team, I hope you're doing well. We would like to request the ability to use custom fonts in the Zoho Recruit Career Site and Candidate Portal. Currently only the default fonts (Roboto, Lato, and Montserrat) are available. While these

• Bigin Plugin for Outlook

  Could we get this added? The Gmail version already exists, and I would like to avoid having to make a switch.

• Date does not fit the field

  Hi There. I am having fun learning zoho sign API. Today I noticed the "Signed Date" field does not fit, or alternatively the font is to large for the auto field space. See screenshot below. The signed date field is created by putting {{Signdate}} on the

• Tip of the Week #69 – Automate your Zoho TeamInbox tasks with n8n integration.

  Don’t waste time repeating the same tasks—like sending follow-up emails or adding new contacts. Let automation save the day. With n8n, an open-source automation tool, you can connect your favorite apps and let them handle the busywork for you. You don’t

• Multi Page/Step Forms in creator

  Greetings i was wondering if it's possible to create multipage/step forms on creator similar to what we have on zoho forms. is that possbile? Thanks

• Package Geometry

  how can i add the dimensions and weight capacity of the available boxes to be default in the system everytime we use it ?

• How to create a Master Kanban Board that syncs with Child Projects?

  Hello, We're currently using Zoho Sprints for managing our interdepartmental teams, and we're looking to enhance our workflow using Kanban boards as part of a company-wide productivity improvement initiative. Our goal is to implement a project structure

• Writer.. Broken?

  Hello,  Writer has been really good to me during the months I've used it, up until now.  I usually launch the app by tapping the icon and I could immediately pick up where I left off.  Now I'm greeted by a loading circle not reaching 100% and I only have the option to create a new account.  By pressing that button it now switches to a login screen and I can access my account. However, it seems (only speculating ofc) to be stuck in cell-phone mode? everything looks scrambled.  I can't access any of

• How to access Recruit Variables in a Deluge function?

  I have set up Recruit Variables in Zoho Recruit, and I would like to know how to retrieve these variables from within a Recruit custom function (Deluge). Could someone please explain the correct way to access them? I tried the following code, but it did

• Upon De activate a user what name doe sthe contacts candidates go under?

  When deactivating a user, does the user name remain the same, as the candidate owner? If not what/who, does it change to? Do I need to change the user name in contacts and candidates before I deactivate the user?

• Weekly Tips: Customize alerts from your Priority Users

  You might receive hundreds of emails daily, but messages from your manager, clients, or team leads often require immediate attention, as they may contain urgent requests or critical updates. How would you ensure you never miss important messages from

• Maximum 100 records in Sheet View is limiting. How can I increase this?

  Thanks in advance for any help with this. There was a similar post that showed answered but it did not help with increasing the number of records you see in a Sheet View. Editing in the Sheet View is fast and efficient but I have 3500 records and I need

• Revenue Management: #3 Revenue Recognition Simplified

  In continuation of the previous post on how to compute revenue recognition, let's explore a solution that helps businesses handle real-world complexities. While the Accounting Standards provide a clear framework for recognizing revenue, the real challenge

• Tip #40- Strengthen Remote Support with IP-based Restrictions in Zoho Assist– ‘Insider Insights’

  Protecting sensitive data and preventing unauthorized access is a top priority for any organization. With IP-based restrictions in Zoho Assist, you can ensure that only users from trusted networks can initiate remote support sessions. Say your IT team

• Push Invoices to Xero Manually

  Hi guys, I'm wondering if anyone has wanted to do this and has a workaround or knows of an app that may be able to help with this. I sell B2B and B2C. The customers can purchase on our website or through marketplace, all of which send sales to zoho. The

• OpenAI error code: 1010 in a Zobot

  Please see short linked screen recording. Insights welcome. Please and thank you! https://workdrive.zohoexternal.com/external/f3247ba9c872639157b707700c0300c433c7664aea924a034f4da3c3ad2e355f

• Ability to Create Sub-Modules in Zoho CRM

  We believe there needs to be a better, more native way to manage related records in Zoho CRM without creating clutter. Ideally, Zoho would support "sub-modules" that we can create and associate under a parent module. Our use case: We have a custom module

• Installing EMAIL Setup in New Domain

  Respected Support team, I'm facing an issue with cloudflare in Pakistan, I want to setup Zoho Mail Setup but I Don't know how to enable Zoho mail setup without cloudflare. My Website https://stumbleguymod.com/ is using CF, and I want a different Zoho

• Does Zoho Sheet Supports https://n8n.io ?

  Does Zoho Sheet Supports https://n8n.io ? If not, can we take this as an idea and deploy in future please? Thanks

• Signature change

  I cannot see how to change signature or out of office details easily now in the new format.

• Inventory API - Retrieve all uploaded product / item images

  I know that I can get the primary image for each product / item or composite item, by using the /image endpoint.  https://inventory.zoho.com/api/v1/compositeitems/<item-id>/image?authtoken=<TOKEN> This will return only one photo, even if the item has multiple images uploaded. Is there a way to retrieve all images stored for an item via the Zoho Inventory API?

• Ebay Integration malfunction

  My eBay integration in Inventory has always worked well. It suddenly malfunctioned. It is creating its own parts in Inventory that are unavailable instead of selling the parts I've always sold. Tech help was unable to resolve this. The latest sale attempt

• Introducing Bin Locations In Zoho Inventory

  Hello users, We are excited to let you know that your wait for the Bin Locations feature has now come to an end! Yes, you heard us right! We are here to introduce the much-awaited Bin Locations now in Zoho Inventory. But before we dive into the feature

• how to get all the records in the custom View more than 200 records , Without using the page Concept

  how to get all the records in the custom View more than 200 records , Without giveing page as default in the Loop Concept Pls help how We can Achive this void schedule.Lead_Attempt_To_contact_schedule_10_30() { pages = {1,2}; for each pg in pages { query_map

• The way that Users can view the ticket

  I have created users. What I would like to achieve is the following: All users under the same company account should be able to view each other’s tickets.

• Zoho UAE SMS/WHATSAPP

  Hello everyone, so I have a question as regards DC and their impact on automation, integration and app usage. For example I am working with a UAE clientniw but each time I tried to connect their WhatsApp and sms then automate their process I tend to receive

• Looking to Flag or Tag contacts/ accounts on Zoho Desk?

  I am looking for a way to flag certain accounts and make it obvious on the views pages. So for example if a has a certain package or needs extra attention it is clear before even clicking on the ticket. This could be via adding a tag or flag onto an account,

• setting date-time field from string

  hello everyone, i hope someone could help me. i have a date-time field in a form that i want to fill in from two separate fields of date, and time. i need to combine the two fields to a one date-time field but can make it work. i tried to convert the

• Calendars and CRM Contacts

  I'm finding having multiple calendars in Zoho One so confusing. I have a few questions so I can get this straight. We have a meeting room that we have set up as a resource in Calendar. Can this be set up in Bookings and the CRM Calendar? Using Zoho Calendar,

• Announcing new features in Trident for Mac (v1.23.0)

  Hello everyone! Trident for macOS (v.1.23.0) is here with interesting features and thoughtful enhancements to elevate your workplace communication and productivity. Here's a quick look at what's new. Record your meetings. You can now record audio and

• Applying a record template

  Hi all, I can't figure this out. I hope you can help. The scenario: We have learners who have to complete a 'digital' journal with tasks in order to qualify. Those tasks, once completed, need a final signature from their 'Mentor', which will trigger their

• Quickbooks invoice with Zoho Creator

  Is it possible to push data from Zoho Creator directly to an invoice on QuickBooks? If so, where can I find information on how to do this?

• Help: Capture full page URL in hidden field when same Zoho Form is embedded on multiple pages (iframe)

  Hi all, Goal Use one Zoho Form across multiple pages and record the exact page URL (incl. subdomain + path + hash) where the user submitted it. Example pages: https://www.example.com/cargo/ https://www.example.com/cargo/containers/#contact https://cargo.example.com/auto/

• Automatically Populate fields - HELP!

  There have been many discussions on this but I still can't seem to get it to work for me. I am trying to create a lookup field and have other fields automatically populate. Based on the instructions in the Help Center, I should be using the "on user input". It's just not working, here is the layout...   Both forms are in the same application. Current form is called Add Note, form to fetch records from is called Add Client. Lookup field is called Select_Client_ID related field in fetch form is called

• Push notifications to portal users

  Hi all, it is possible to send push notifications to portal users?

• Next Page