Data Transfer to Zoho CRM

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 {

  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.     });

  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.


    • 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

    • DYK 1 - Color Palette for Enhanced Visual Identification of Status

      Introducing the Did You Know series of posts. The goal of this series is to familiarize users with certain features or enhancements in Zoho Projects that may not be evident at first glance. The first post in this series deals with color palettes for indicating

    • Invalid field in the COQL query

      Dear Zoho Support! I believe that you already helped me with a similar problem a few years ago. One of my clients has a custom field named "LOB" in the "Deals" Module (see the field's metadata below). The COQL query using this field: : "select id, Deal_Name,

    • Automating Employee Birthday Notifications in Zoho Cliq

      Have you ever missed a birthday and felt like the office Grinch? Fear not, the Cliq Developer Platform has got your back! With Zoho Cliq's Schedulers, you can be the office party-cipant who never forgets a single cake, balloon, or awkward rendition of

    • Transferring domain registration to new registrar and switching email hosting at the same time?

      I need to transfer an existing domain uv cure adhesive that's currently with SiteGround to Porkbun. I also need to move the existing custom email addresses from SiteGround to Zoho Mail. I'm not sure if I should transfer the domain first and then tackle

    • Split deposits

      Can Zoho do split deposits. One deposit, two checks for two separate invoices from different customers. This is one of the most common tasks I can imaging. When I mark the two invoices paid, there are two deposits in bank register. When I try to match,

    • Feedback: Streamlining Note Management in Zoho Notebook

      Dear Team/Support, I would like to share some feedback regarding the note management system that could help improve usability and accessibility for users like myself. Notebook 1 (screenshot attached): Currently, the system does not allow selecting and

    • Deactivate Desk Contact without Deleting Contat

      We have a client who has multiple tenants for regulatory purposes, and as such, has a few users that have email addresses in both tenants. They've then emailed into the ticketing system, so we have multiple contacts (no big deal, we want to keep their

    • Delete my store of Zoho commerce

      Hi Team, I want to delete my stores of commerce. Please help me asap. Looking for the positive response soon. Thanks Shubham Chauhan Mob: +91-9761872650

    • Ability to add VAT to Retainer Invoices

      Hello, I've had a telephone conversation a month ago with Dinesh on this topic and my request to allow for the addition of VAT on Retainer Invoices.  It's currently not possible to add VAT to Retainer Invoices and it was mutually agreed that there is absolutely no reason why there shouldn't be, especially as TAX LAW makes VAT mandatory on each invoice in Europe!   So basically, what i'm saying is that if you don't allow us to add VAT to Retainer Invoices, than the whole Retainer Invoices becomes

    • [Free Webinar] Learning Table Series - Zoho Creator for Asset Management with AI Enhancements

      Hello Everyone! We’re excited to invite you to another edition of Learning Table Series, where we showcase how Zoho Creator empowers industries with innovative and automated solutions. Each month highlights a specific sector, and this time our focus is

    • How to set different item selling prices for Zoho Commerce and Zoho Books

      Item selling prices for Zoho Commerce and Zoho Books are in sync. If we update the Item selling price in Books, the same will happen in commerce and vice versa. I need a separate commerce selling price for online users and a separate books selling price

    • Menu Building is completely broken

      I have been 3 hours, I have not been able to edit the menu. Either it is completely broken, very little intuitive or I do now know anything... There is no way to create a megamenu, no way to create a menu. Despite the fact I go to menu configurartion

    • Can you sell Subscriptions using Zoho Commerce?

      In addition to physical products and the apparently coming soon 'Digital Products', it is possible to sell Subscriptions using Zoho Commerce?

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

    • Multiple Languages for Product Names

      Hi, I use 2 languages: spanish and english. I want to have for every product a name in spanish and a name on english. I want to have to possibility of choosing one of these languages when making an invoice or a purchase order. Is there any way to do

    • Item with name in different languate

      Hello, is there a way to have an item with its name in different languages? For example: I sell an item in different markets and I'd like to have a Proposal and the Invoice with the Item Name in a specific language. Rino Bertolotto Zoho Specialist, STESA srl

    • Contacts with most tickets? Alarm for multiple tickets?

      Is it possible to see through the analytics/reports which contacts are creating the most tickets (not the most discussed ones)? Also, is there a way to set up a notification if a contact creates multiple tickets within a certain time frame?

    • Ugh! - Text Box (Single Line) Not Enough - Text Box (Multi-line) Unavailable in PDF!

      I provide services, I do not sell items. In each estimate I send I provide a customized job description. A two or three sentence summary of the job to be performed. I need to be able to include this job description on each estimate I send as it's a critical

    • Issue with Template Subject Line Format in Zoho CRM

      Hi Team, I’ve noticed that when I update the subject line of an email template in Zoho CRM, it sometimes appears in an incorrect format when used. Please see the attached screenshot for reference. Kindly look into this issue and fix this issue from backend

    • Function #53: Transaction Level Profitability for Invoices

      Hello everyone, and welcome back to our series! We have previously provided custom functions for calculating the profitability of a quote and a sales order. There may be instances where the invoice may differ from its corresponding quote or sales order.

    • Two Data Labels in Bar Chart

      I need to create a bar chart that has both the SUM and COUNT. I've concatenated them into a formula but it converts it into a stacked bar / scattered chart. The bar chart is no longer accessible. Since i'm comparing YOY, it would be best to have it in

    • Disable field on subform row

      Hi, Is it currently possible to disable a row item on a subform? I was just trying to do something whereby until another value is entered the field is disable but for the deluge scripting interface threw up an error saying such a function is not supported on a subform. Thanks in advance for your help. Shaheed

    • Leads - Kanban view fit to screen

      Hey guys, I created a custom layout for my leads, staged by lead status. I have 10 types of status. In Kanban view I see only 4 columns/stages and need to scroll to the right to see the rest. Is there a way to make columns/stages be displayed all together?

    • Request to Differentiate Auto-Closed WhatsApp Conversations in SalesIQ

      Hi Zoho Support, I’d like to raise a request related to the way WhatsApp conversations are auto-closed in SalesIQ. Every Monday, our Sales team has to manually review each closed WhatsApp conversation from the weekend to identify which ones were automatically

    • Kanban View UI gets a revamp

      Hello everyone, In the coming week you will notice design related enhancements in Kanban View. The UI has been changed and a new option is introduced under Kanban View Settings that allows to change the color of the category headers.  Please, note that the functionality is not changed. These changes will not apply to the Activities and Visits modules. Here are the details of the changes: 1. The column widths have been fixed to 300 px. The records will have a box around them for clear distinction.

    • Can you stop Custom View Cadences from un-enrolling leads?

      I'm testing Cadences for lead nurture. I have set un-enroll properties to trigger on email bounce/unsubscribe, and do NOT have a view criteria un-enroll trigger. However, help documents say that emails are automatically un-enrolled from a Cadence when

    • Issue with Anchor Link on Zoho Landing Page (Mobile/Tablet View)

      Hi Team, I have created a landing page using Zoho Landing Page and added an anchor link to it. The anchor link is working fine on desktop view; however, it does not work properly on mobile or tablet view. I’ve tried debugging this issue in multiple ways,

    • Simplest way to convert XML to a map?

      I've reviewed the help info and some great posts on the forum here by Stephen Rhyne (srhyne). At the moment I'm using XPath to generate a list of xml nodes, iterating through that to fetch the field name/value pairs and adding them to a map (one map for each record in the data). I then convert the row map to a string and add it to a list. Here's the function: list xml.getRecordListFromXML(string xml_data, string ele_name) {     result = List();     // get list of record nodes     rec_list = input.xml_data.toXML().executeXPath("//"

    • Introducing Creator Simplified: An exclusive learning series to enhance your app development skills

      Hey Creators! Welcome to Zoho Creator's new learning series, Creator Simplified. In this series, we'll dive into real-world business use cases and explore how to translate your requirements into solutions in your Creator application. You can also expect

    • [Product update] Updated Data Synchronization Process for QuickBooks - Zoho Analytics Integration.

      Dear QuickBooks integration users, We’re making an important update in the way data is currently synced in your QuickBooks integration within Analytics workspace. What’s changing: Previously, with every data synchronization, Zoho Analytics used to fetch

    • Zoho CRM new calander format cannot strikethrough completed task

      Hi, Recently there is a new format for calendar within Zoho CRM However, found out that a completed task will not cross out or strikethrough like previous format. Without strikethrough, it will be difficult to identify which task is still in Open status.

    • How to edit form layout for extension

      I am working on extension development. I have created all the fields. I want to rearrange the layout in Sigma platform. But there is no layout module in Sigma. How can I achieve this for extensions other than Zet CLI and putting the fields into widget

    • Employees not Users

      Hello, We are a construction company that has +180 employees and most of them are in remote location working onsite with no access to internet. Is it possible that we have data stored for all employees but have only 5-10 users who will be in charge of entering employees data? or do we have to pay for all +180 employees? even though they won't be using the system?

    • Zoho people generatimg pdf

      Hello , now i want to make a customm button in zoho people that is inside a deduction module , that fetches all the records and generate a pdf with a template that i have done in the mail merges template , i was told that i have to upload template on

    • Ability to Filter Alias Mailboxes in Zoho Recruit

      Dear Zoho Recruit Team, I hope you are doing well. We would like to request a feature enhancement regarding the handling of alias mailboxes in Zoho Recruit. Currently, when we connect an alias mailbox (e.g., jobs@domain.com) from our Zoho One account

    • zohorecruit.com career form postcode bug

      Dear, When I select a postcode from the drop down on a zohorecruit.com career form, the street text field is automatically filled with the name of the city, which should not happen. Any idea how I can fix this? Thanks, Bart

    • Office-365-agenda and Microsoft Teams Integration

      Dear, I have a trial version of Zoho Recruit and trying to evaluate the Microsoft Teams Integration in Zoho Recruit. After registering with my Office 365 account and checking the result of the registration/sign-in at https://mysignins.microsoft.com/ (which

    • Delegate Access - Mobile iOS/iPad

      We’re over the moon that delegate access is now available in Zoho Mail as we were nearly ready to switch platforms because of it! Is there a timeline on when delegate mailboxes will be accessible from the iOS and iPad OS applications? Thanks, Jake

    • How to add Connector in developer platform zoho?

      Hi, I am working on creating an Extension, and part of the development is to retrieve Email templates. In my CRM instance I can invokeURL by creating Zoho OAuth connection and get the template. But developer platform does not provide Zoho OAuth or any

    • How to archive Lost/Junk Leads so sales reps don’t see them, but keep them for reporting?

      Hi everyone, In our Zoho CRM we have two Lead Status values: Lost Lead and Junk Lead. What I want to achieve is: When a lead is marked as Lost or Junk, it should disappear from my sales reps’ Lead views (so they only see active leads). At the same time,

    • Next Page