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

    • Profile Image Showing Incorrectly in Zoho Mail

      Hi everyone, I’m facing a serious issue with Zoho Mail. The profile image showing for my email address is incorrect — it’s not my image. When I send an email to my Gmail account, it displays someone else’s image. This looks very suspicious and can make

    • I need access to my old email

      I need access to my old email bromzeman@zoho.com to verify it for forwarding. I can’t access the inbox to get the confirmation code. Please assist with recovery or forwarding. as you might already know, they made alot of the email addresses to have that

    • Set Defaults for Email Groups

      Is there a way to set defaults for all of the groups that I establish as a moderator? For example, I want every group I establish to have the following settings: 1. Moderator is <user> 2. User is <user>, <user> 3. Notifications for new group turned

    • LOGS FOR RECEIVED MAIL ( READ DATE & TIME)

      In Zoho mail we can extract the read logs of received mails so that we can see when we have read the mail and at what time & we have read it.

    • Domain verification probem

      Hello, i use a domain from Namecheap with hosting from Cinfu when i try to verify my domain on zoho i get "TXT Verification failure" i even tried the HTML verification and the code appears but also giving me the verification failure error.

    • Switching to Cloudflare email routing from Zoho Mail

      Hello, I'm currently working on migrating from Zoho Mail to Cloudflare's email routing solution. This requires changing MX and TXT records for our custom domain - when we do this, will our users still be able to log into their accounts and access an archived

    • Un Subscription Button

      How can i Add the Un Subscription Button in Zoho mail

    • Documents unable view and need to downlad

      I can't view .doc files in Zoho mail unless I download them, but I can view PDF files without downloading.

    • we encountered a problem while sending your email. please try again later.

    • Adding and removing people from groups

      We're having problems adding people to a group. Apparently Zoho has one email address and will not recognize a different email address.

    • MAIL SEARCH IS NOT WORKING

      THIS ISSUE HAS BEEN BUGGING ME SINCE MORNING, PLEASE RESOLVE THIS AT THE EARLIEST

    • URL Parameter on Help Center SIgn in link

      Is it possible to add a url parameter to the sign in link on the Help Center?

    • migrating from HelpScout

      I am attempting to import a conversation file from helpscout into desk and am receiving size errors. What is the current file size restriction. Does anyone have any tips for a successful migration?

    • Layout Rules Don't Apply To Blueprints

      Hi Zoho the conditional layout rules for fields and making fields required don't work well with with Blueprints if those same fields are called DURING a Blueprint. Example. I have field A that is used in layout rule. If value of field A is "1" it is supposed to show and make required field B. If the value to field A is "2" it is supposed to show and make required field C. Now I have a Blueprint that says when last stage moves to "Closed," during the transition, the agent must fill out field A. Now

    • Article Name Sorting in Zoho Desk Knowledge Base (agent / admin side)

      Dear Zoho Desk Support, We are writing to request an enhancement to the Knowledge Base management feature within Zoho Desk. Currently, there is no option to sort articles by their name, which significantly hinders efficient article management, especially

    • How to parse JSON data with SQL in Zoho Analytics?

      Hi all, I have a column with JSON data. I want to show this column in a chart, but it is very messy, and no JSON parsing function is supported on Zoho Analytics. data example: {"id": 5, "status": "false", "date": "15/10/22"} what I want to do in SQL is

    • Ability to turn off "Would you like this amount to be reflected in the Payment field?" message

      Team, Is there any way to turn off the message" Would you like this amount to be reflected in the Payment field?" when I make a payment? This is so annoying. This happens EVERY TIME I put an amount in the Payment Made field.

    • How to activate RFQ? What if a price list has ladder price for items?

      Where can I find the option to activate request for quotation? How does it work? If the item has ladder price, does it gets calculated depending on how many items are in the cart?

    • Add an Equation Field (Or update the Formula Field)

      Hi, I would like to be able to have one field as a Text Field with QR Code, and then have multiple Equation/Formula Fields that then take parts of that fields data with LEFT, MID, RIGHT, REGEX, etc. Thanks Dan

    • How to parse column having JSON data using SQL?

      We have a daily sync from a PostgreSQL database that brings data into Zoho Analytics. Some of the columns store raw JSON data. We need to build SQL queries on top to parse data from JSON and store them in discrete columns. There is no option for "Data

    • Enable report button based on the current user role

      Greetings  i have a report that contains action buttons, i want these buttons to appear as enabled only when the current logged in user has a certain role, for example only CEO role users will be able to use this button. but when setting the conditions

    • 500 Internal Error In Mail API

      I'm getting 500 Internal Error when using mail API. I'm getting this error for this one account, it works fine for other Account IDs which I have in my system.

    • Piss poor service in Support in Domains and email

      Srijith Narayanan B contacted me today. Very pleasant fellow. Just didn't want to tell him how bad your support service is. You help the person, but you leave before we can finish the next stage. Which causes a lot of frustration. It's been 8 days now

    • Zoho live chat widget in React Js

      I am trying to test Zoho live chat widget code in react js, below is the sample code void(0)} onClick={()=>window.$zoho.salesiq.floatwindow.visible("show")}>LIVE CHAT window.$zoho = window.$zoho || {};window.$zoho.salesiq = window.$zoho.salesiq

    • Are there any plans to add Triggers for Subform edits?

      By The Grace of G-D.  Hi, How are you? Can you tell me if you have any plans to support subform edit as a workflow trigger? And what about have them trigger an "onChange" client script?

    • Zoho commerce

      i am facing issue with order summary emails.i am getting 1 continuous email for order received yesterday and today.ideally 1 email should be received for a particular date ie for 02/08 i should received 1 email from 12.01am till 11.59pm but it is being

    • Feature Request: Improve Category Page Sorting for "Out of Stock" Products

      Hi there, I'm writing to request a new feature that I believe would significantly improve the user experience in my online store. Currently, on category pages, products are sorted by popularity. However, when a popular product goes "Out of Stock," it

    • POSTMAN - There was an error in evaluating the Pre-request Script:Error: Cannot read properties of undefined (reading 'json')

      I am beginning the journey to learn how to use the API for Zoho Sign. I am getting the following error when I try to use postman. To walk you through how I am getting this error... I wanted to start with a simple GET and expand my learning from there.

    • How do i integrate shipstation with zoho inventory

      Wanting to set up my own delivery driver in ship station so we can get real time tracking of where the package is but then i want it to automatically update zoho inventory packages/shipments how can i do this

    • Invalid value passed for salesorder_id

      Hi, I am using sales return API, details are given below: API: https://inventory.zoho.com/api/v1/salesreturns?organization_id=700571811 Post Json Data: { "salesreturn_number": "", "date": "2020-11-12", "reason": "Testing from API", "line_items": [ { "item_id":

    • Create Invoice and Invoice Items from Sales Order via API

      Currently, when creating an Invoice associated with a Sales Order via the API, it appears that I must manually include all of the items (line_items) even though they are already part of the Sales Order. My question is this: is it possible to raise an Invoice via the API based on all of the information associated with a Sales Order--such as the  items? In other words, do I always have to manually include the items (line_items) when raising an Invoice via the API when the Invoice is associated with

    • Outlook 2013 Calendar Syncs but "Related To" Field in Zoho is blank

      Outlook 2013 Calendar Syncs but Related To Field in Zoho is blank I expect the "Realted To" field to be populated with the calendar participants

    • Export a Course

      Is it possible to export a course from Zoho Learn to a SCORM file?

    • Add and Remove Agents from Departments and Groups in Zoho One

      Hi Zoho Flow Team, We hope you're doing well. Currently, Zoho Flow provides an action to add an agent to a group in zoho one, but there is no action to remove an agent from a group or a department. Another action that we find missing is the option to

    • Zoho learn Custom portal - networkurl & CustomPortalId

      I want to get my individual account’s networkurl and customportalId to use in this API: https://learn.zoho.com/learn/api/v1/portal/<networkurl>/customportal/<customportalId>/manual How can I retrieve the networkurl and customportalId using the API? I

    • Consumer Financing

      Does Zoho currently have a payment gateway (such as Stripe, Square, etc) which offers financing for customers? So, let's say the estimate we give the customer is greater than what they can afford at the time, but we can sell the service now, letting them

    • Intégration de la gestion des Passkeys dans Zoho Vault

      Zoho Vault est depuis plus d’une décennie une solution fiable pour les entreprises : pour la gestion, le partage et le stockage des mots de passe. En 2018, nous avons fait un pas en avant en proposant la connexion unique (SSO). Nous sommes fiers de franchir

    • Scan & Fill with double quote key/value pairs

      Hi, An old Ticket moved to a Topic/Idea: I love the idea of the new Scan & Fill as it nearly covers my previous request for a QR Scanner to read a multi-part QR Code. My QR Codes are hard-coded as below: {"key1":"value1","key2":"value2","key3":"value3"}

    • Analytics SQL Queries should allow # as comment

      # and // are very common for commenting in SQL. Not sure why analytics only allows /* and */ for commenting. Especially when # grays the line as if it's being commented out. This should be added for sure.

    • SalesIQ Operator Activity Reports in Zoho Analytics

      I'm busy building a dashboard in Zoho Analytics and I want to include SalesIQ stats in the dashboard, but I'm unable to get the statistics mentioned in the attached image. Any idea where I can get the stats for Operator Activity?

    • Next Page