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

• Zoho Forms - Zoho Drive connection - Shared Drives not supported

  Hello i am stuck with Google Drive Connection There is no supported shared drives Connection is not support shared drives boolean Query Parameters - supportsAllDrives=true&supportsTeamDrives=true to activate fetch files from the shared drives. Ahat need

• TikTok (and other social platform) Messages and comments of the past

  When I link a social channel, Zoho will show in "Inbox", "Messages" and "Contact" sections the interaction done in the past? (comment, messages...)

• Email Integration - Zoho CRM - OAuth and IMAP

  Hello, We are attempting to integrate our Microsoft 365 email with Zoho CRM. We are using the documentation at Email Configuration for IMAP and POP3 (zoho.com) We use Microsoft 365 and per their recommendations (and requirements) for secure email we have

• How do I fix this? Unable to send message; Reason:554 5.1.8 Email Outgoing Blocked.

  How do I fix this? Unable to send message; Reason:554 5.1.8 Email Outgoing Blocked.

• Restrict Employee mail deletion

  Dear Zoho, Is there a way where i can restrict my employees to delete any mails from their account

• 554 5.1.8 Email Outgoing Blocked.

  Hi guys, I just singed up for mateusz.nowicki@zoho.com mail and I can't send any mails.. Why? Everytime I try to send something I got error like the one in the screenshot. Please, help me.

• Zoho IP blocked by SpamHaus

  ERROR CODE :550 - 5.7.0 Your server IP address is in the SpamHaus SBL-XBL database, bye

• File Upload in Creator's Subfrom

  Hello Sir/Madam, Here is a Problem......... Scenario: In CRM One Custom Module (Payments) have one File Upload Field now we have to Upload that File into Creator's Custom Form (Documents) have one Subform (Documents) in Document Upload Field using Deluge

• integarting attachments from crm to creator

  when i tried to integrate pdf attachments from crm to creator via deluge i am getting this error {"code":2945,"description":"UPLOAD_RULE_NOT_CONFIGURED"} the code i used is attachments = zoho.crm.getRelatedRecords("Attachments","Sales_Orders",203489100020279XXX8);

• Error AS101 when adding new email alias

  Hi, I am trying to add apple@(mydomain).com The error AS101 is shown while I try to add the alias.

• Trigger workflow base on email clic

  Searching the help and forum, I see that there were workflow trigger rules based on email. But now, I can't find this type of trigger when I create a custom workflow. What I'm looking for would be to automate the sending of an email for a new prospect,

• Bigin Form Acknowledgement

  How to troubleshoot and find out why form acknowledgement is not sending emails after form submission?

• Option to Customize Career Site URL Without “/jobs/Careers”

  Dear Zoho Recruit Team, I hope you are doing well. We would like to request an enhancement to the Career Site URL structure in Zoho Recruit. In the old version of the career site, our URL was simply: 👉 https://jobs.domain.com However, after moving to

• Zoho Mail POP & IMAP Server Details

  Hello all! We have been receiving a number of requests regarding the errors while configuring or using Zoho Mail account in POP/ IMAP clients. The server details vary based on your account type and the Datacenter in which your account is setup. Ensure

• Ever since the new Android App udpates notifications are not working

  notifications are not working for the app is its closed I followed the tutuorial to the notificaction fixed and everythig seems to be right but notifications are not workig

• Zoho Analytics & Zoho Desk - but not all desks

  I have several desks in our company and one of those is used by our HR department. I want to bring through the data to the shared Zoho Analytics workspace - except for the HR desk. Can this be excluded at data import stage ?

• Incoming Emails Not Showing Up in Zoho Inbox

  Hi - I have my Zoho email account set up to forward a copy of all incoming emails to a secondary Gmail address, whilst retaining the original email in the Zoho inbox. However, all my incoming emails are currently not showing up in my Zoho inbox, so I'm

• Form Accessibility

  Hi, is there an update on the accessibility standard of Zoho forms? Are the forms WCAG 2.1 AA compliant? 

• How to retrieve my following requests on this forum?

  Sorry, but I did not find the proper subforum for this question.

• How to list emails in a folder, e.g. Inbox, on multiple pages when using Zoho mail webpage?

  Something as shown in the figure. There are totally 50 emails in Sent folder. If "Mail per page" equals 20, then the Sent folder is split into 3 pages. When I wander through Sent folder, I can just select a specific page to jump to. BTW, it seems that

• Unable to Create Zoho Booking via the Book Appointment API

  Its giving the below error {     "response": {         "errormessage": "Error setting value for the variable:customer_details\n null",         "status": "Error"     } } Request: POST Url: https://www.zohoapis.in/bookings/v1/json/appointment attached Zoho-oauthtoken

• SHEET - Send email when a cell changes

  I would like to create a custom function for Zoho Sheet that triggers when a paticular cell changes to a specific value. This would result in sending an email to a recipient (this would be an address that remains the same and included in the script). Example: = IF(N4= "Drafted", <>EmailFunction) 1)     Cell N4 changes to "Drafted" 2)    Email is sent to recipient            or alternatively 3)    Post to chat channel I have found the Custom function editor in Sheet. I am not bad at scripting, but

• 【開催報告】 福岡 ユーザー交流会 2025/8/8（金）

  皆さま、こんにちは。コミュニティチームの中野です。 8/8（金）に、福岡 ユーザー交流会を開催しました。 本投稿では、その様子をお届けします。当日の登壇資料などもこちらに共有しますので、参加できなかった皆さまもご参照ください。 今年初の開催となる福岡 ユーザー交流会では、CreativeStudio樂合同会社 前田さんによるZoho CRM / Sign / Survey の事例セッションのほか、 Zoho社員セッションでは、Zoho Forms の活用法を解説。 さらに、「見込み客・顧客データの管理/活用方法」をテーマに参加者同士でZoho

• hiding a topic from all but one segment (or list)

  My organization sends out a number of newsletters using Zoho Campaigns. One of those newsletters is for volunteers. In order to become a volunteer, a person has to first go through our volunteer orientation (training). After that, they can receive newsletters

• no me llegan los correos a Zoho mail

  No puedo recibir correos pero sí enviarlos, ya hice la modificación de MX y la verificación de teléfonos, qué es lo que ocurre? gracias

• Error: Invalid login: 535 Authentication Failed

  I have used zoho with nodemailer. const transporter = nodemailer.createTransport({ host: 'smtp.zoho.com', port: 465, secure: true, auth: { user: 'example@example.com', pass: 'password' } }); While sending the mail, it shows the following error: Error:

• Zoho Renewal

  Hello, If I am not going for zoho email renewal. will i get back my free zoho account? and if yes then is it possible to get back my all free user. how many user get back 10 or 25?

• javax.mail.authenticationfailedexception 535 authentication failed

  Hi, I am facing 535 authentication failed error when trying to send email from zoho desktop as well as in webmail. Can you suggest to fix this issue,. Regards, Rekha

• Not reciving emails

  Apparently i cannot recive emails on my adress contact@sportperformance.ro I can send, but do not recive. The mail i'm trying to send from mybother adress gets sent and doesn't bounce back... but still doesn't get in my inbox. Please advise

• Not receiving MailChimp verification e-mail

  It seems that their verification e-mails are blocked. I can receive their other e-mails, but not their verification of domain ownership e-mail. I've checked and double checked how I typed the e-mail, using different e-mails (my personal e-mail can receive it), white listing the domain and all that is left is for the IP's to be white listed, but I don't have that power.  If a staff member could take a look at this -> http://mailchimp.com/about/ips/ And perhaps white list them for me, that would be

• Creating my 2nd email account

  After creating my first email address, I decided to get another email address. I would like to use this new address as the primary address too. I don't know how to set it up there doesn't seem to be an option for that

• Cannot - create more email account - Unusual activity detected from this IP. Please try again after some time

  Hello, I come across the error message in Control Panel. Unusual activity detected from this IP. Please try again after some time and i cannot create any more users We are an IT company and we provide service for another company Please unlock us.

• "Unable to send message;Reason:553 Relaying disallowed. Invalid Domain"

  Good day. When I try to send mail through ZOHO mail I get the following error : "Unable to send message;Reason:553 Relaying disallowed. Invalid Domain" I need help with this. My zohomail is : @eclipseweb.site Thank you,

• Transfert de domaine pour création des comptes emails

  Bonjour , je ne parviens point à créer des mails avec le domaine 'raeses.org' suite à la souscription du domaine auprès d'un autre hébergeur, dont j'ai fait la demande du code de transfert qui est le suivant : J2[U8-l0]p8[ En somme, attente de l'activation

• Help! Unable to send message;Reason:554 5.1.8 Email Outgoing Blocked.

  Kindly help me resolved this issue that i am facing here.

• How are people handling estimates with Zoho inventory?

  We are often using Zoho Books for estimates that then get converted to invoices within Books. We would like the sales team to migrate entirely to Zoho Inventory and no longer need to use Zoho Books so that they are only on one system. How are people managing

• Relative Date Searches

  Currently in the search options, it has "date", "from date" and "to date". I think it would be great if there were options like "date greater than x days ago" and "date less than x days ago". I realise that as a once off you can just use the existing

• Performance is degrading

  We have used Mail and Cliq for about three years now. I used to use both on the browser. Both have, over the past 6 months, had a severe degradation in performance. I switched to desktop email, which appeared to improve things somewhat, although initial

• Ask the Experts 23: Customize, utilize, and personalize with Zoho Desk

  Hello everyone! It's time for the next round of Ask the Experts (ATE). This month is all about giving Zoho Desk a complete makeover and making it truly yours. Rebrand Zoho Desk with your organization’s details, customize ticket settings based on your

• Dear Zoho CEO: Business Growth is about how you prioritise!

  All of us in business know that when you get your priorities right, your business grows. Zoho CRM and Zoho Books are excellent products, but sadly, Zoho Inventory continues to lag behind. Just this morning, I received yet another one-sided email about

• Next Page