Kaizen #180: Automating Data Transfer from Zoho Sheets to Zoho CRM Subforms

Kaizen #180: Automating Data Transfer from Zoho Sheets to Zoho CRM Subforms



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.





        • Recent Topics

        • New in Zoho Forms: Inline OTP Verification

          Hello form builders, We are excited to announce the launch of Inline OTP Verification in Zoho Forms, a smarter way to ensure the authenticity of the contact details you collect. Until now, OTP Verification in Zoho Forms worked as a pre-access step: respondents

        • Zoho Mail : Associate emails with Meeting records and allow multiple emails to be assocaited at once

          Is there a workaround that would allow either of these? I want to associate emails with Meeting records. I also would like to be able to select multiple emails at once for association with a record.

        • Create task from email

          Is there a way on mobile to create a task from an email? I use this feature a lot and when traveling now I read email on mobile. By the time I get to my office I forget about them since I didn't add it to a task. Is this feature missing on moble?

        • Zoho Socials - Unable to view Channels and SmartQ

          Hi, The channel Facebook has been added by the admin, however, it is not visible on the User level (employee). Other channels are visible. Also, we have the premium account, and SmartQ is not working. Can anyone help? Regards, Priyanka

        • Eliminating Manual Consolidation: Automating Currency Field Sync from Task to Project

          Hello Everyone, A Custom function is a user-written set of code to achieve a specific requirement. Set the required conditions needed as when to trigger using the Workflow rules (be it Tasks / Project) and associate the custom function to it. Requirement:

        • We want to set the "Converted from Lead" value in Deals using a Workflow or via a Deluge script. How?

          For use in Zoho Analytics, we need the field "Converted from Lead" filled in our deal records. This field is empty everywhere, because we do not create deals directly when converting a lead to a contact. We want to do that using the API or a workflow

        • Sales Orders: Quoted_Items + items in another subform -> into Ordered_Items ?

          hello, When creating Sales Orders, is it posible to inherit/fill the Ordered_Items with all the items from Quoted_Items + all the items from a customized subform with similar fields? Since you can create a sales order in different ways (convert, new -

        • How to cancel the GSTR1 pushed to GSTN

          How to cancel the GSTR1 Pushed to GSTN, some rectifications to be done in HSN & SAC code

        • Zoho Books API — Invalid Operation Type / Scope does not exist

          Hello Team, We are unable to use the Zoho Books API from our registered application. We’ve already: Created a client in Zoho API Console using (Admin in Books) Generated the OAuth code and token successfully Used the correct scopes: ZohoBooks.fullaccess,ZohoOauth.userinfo.READ

        • Enhancements in Canvas

          Dear All, Greetings! Canvas lets you design the record details page to suit your brand or business preferences. We are glad to introduce the following enhancements to uplift your design experience. Reusable Components Style Presets Let's go! Reusable

        • Minimum order quantity

          Is there a way to enforce a minimum order quantity - ie has to have a minimum of 250?

        • 【Zoho CRM】ポータル機能のアップデート：UIとポータルの作成フローの変更

          ユーザーの皆さま、こんにちは。コミュニティチームの藤澤です。 今回は「Zoho CRM アップデート情報」の中から、ポータル機能のアップデートをご紹介します。 目次 概要 ユーザーグループの作成フローの変更 ユーザーグループ詳細画面内のタブについて 「タブと権限」タブについて 「設定」タブについて 概要 UIとポータルの作成フローが変更されました。ポータルの新機能に先立ち、UIを一部変更しました。タブやオプションの配置を見直し、機能へよりアクセスしやすくなっています。 また、「ポータルユーザーの種類」は今後、「ユーザーグループ」と呼称され、ページ上のボタンも「ユーザーグループを作成する」に変更されます。

        • Tax on Imported goods charged by Shipping Company

          Hi Folks, I imported goods from outside Canada, for better understanding I will give an example data. imported goods value: 2000$ The shipping company sent me an invoice containing the following information: Custom duty on imported goods: 400$ Administration

        • Prefilled Date fields auto-changed and then locked when using “Edit as new”

          If a document out for signature has date fields (not SignedDate fields) that were pre-filled before sending, and then you use “Edit as new” to create a new version of the same document, the value of those date fields gets automatically changed to today

        • Zoho Webinar via Social Media

          Hello, is it possible to stream a Zoho Webinar via Social Media like Linkedin or Facebook?

        • Add Hebrew & RTL Support to Feedback Widget

          Hello Zoho Desk Team, How are you? We are using Zoho Desk and would like to utilize the Feedback Widget. While Zoho Desk itself supports Hebrew and RTL, the Feedback Widget unfortunately does not. We kindly request that Hebrew and full RTL support be

        • OAUTH2 isn't working with Power Automate and N8N (Zoho Desk)

          Hello, I am trying to set up an OAuth2 connection to the Zoho Desk API, but the authentication flow fails immediately. I am experiencing this issue in two separate platforms: Microsoft Power Automate (using a Custom Connector) and n8n. Instead of being

        • Kit Items Breaking Automations - "Provide mapped components for all kit items"

          This has been brought up in other threads, but I believe this issue warrants its own topic. Whenever a sales document (Estimate, Sales Order, Invoice) is created or manipulated programmatically, trying to include a Kit as an Item throws this error: "Provide

        • Show item Cost value on Item screen

          The Item screen shows Accounting Stock and Physical Stock. It would be very helpful if value information could be displayed here as well, for instance Cost Price. Currently, to find the Cost Price (as used for inventory valuations) from inside the item

        • Mark shipment as delivered via api

          Hellloooo again Zoho guys !! More help required if you would be so kind, pleeeezz..... var options =        {         'method' : 'post',         'contentType' : 'application/json',         'muteHttpExceptions' : true       }; var myPackNo  = encodeURIComponent('###################');

        • Setting Alternative units for an item.

          Hello Team, How to create alternate units for an item. We are placing orders for stocks in boxes. One box contain 24 items. At the time of selling we have two categories of buyers wholesalers and retailers. So the sales will be in PCS and in boxes also.

        • Zoho Inventory search when adding items to SO/PO, etc.

          I do not see that Zoho Inventory searches within the item name for an item lookup. We have many products with variants. So when I search for a product, say a lighting system, and it comes in different sizes and colors, I can only get those products where

        • Item Group Attributes

          Hello, I would like to see more attributes under grouped items. We sell car parts, there are several suppliers for the same part but under different brands. We want to group them together but the attributes under groups are lacking. For example, the products

        • Remove HTML Format - Deluge

          Hello @all if you want to delete the HTML format from the text please follow the script. Data = "Text"; info Data..replaceAll("<(.|\n)*?>" , "").replaceAll("&nbsp;" , " "); Apart from this if you require anything please let me know Thanks & Regards Piyush

        • Using multiple languages in template

          I wanted to add the company name in the template in arabic. I found a way through the header and footer option, except when i print the quotation the arabic disappears both in the top and bottom of the page. I have attached pictures of the before and

        • ADDING 5% VAT TO PURCHASE ORDERS GENERATED ON ZOHO BOOKS UAE

          Please guide on how to add 5% VAT to Purchase Orders generated on ZOHO Books UAE edition.

        • Import from /csv file, some items fail with the error "Specify Tax Or Tax Exemption".

          Hello! I am trying to import a csv file of all of my expenses for a complete financial year. I get errors for some items with the error message "Specifiy Tax or Tax Exemption". These errors only occur on lines where I have "Postage" as the expense account.

        • About maximum number of requests per minute

          Hi, Our company has integrated Zoho inventory and we're using the shipping order creation and update functions and so on. Currently we're receiving "For security reasons you have been blocked for some time as you have exceeded the maximum number of requests

        • Approval - Report/Views

          Hi, On Zoho Desk - Is there a way to report on pending approvals, or a view or similar?

        • "Zoho CRM Integration" option is missing in Zoho Social Settings

          I am trying to integrate my Zoho Social account with my Zoho CRM account. I am on the Professional Trial plan and my user role is "Brand Admin". However, I cannot find the "Zoho CRM Integration" or "Lead Generation" option anywhere in my Zoho Social settings.

        • Error 553

          Não estou conseguindo enviar ou receber e-mail, sempre dando o erro 553, sendo que há mais de um mês o domínio está pago e liberado. Preciso de um suporte urgente

        • Automate insurance document workflows with Zoho Writer

          Insurance companies have to deal with creating and managing complex documents and forms, such as policy applications, explanation of benefits documents, brochures, renewals, and claim forms. Handling all of this manually is hugely time and effort intensive,

        • Create PDFs with Text so that we can copy from a generated PDF

          Currently, any information that a user enters into a field cannot be highlighted and copied from the PDF that Zoho Sign renders. For example, if someone were to provide a phone number in a Zoho Sign text field, you would not be able to copy the phone

        • How To Insert Data into Zoho Table using Api

          Hi Community, I have created a table inside zoho tables. How do I insert data into table using API. Please tell the exact endpoint and payload , I just have to insert data into table columns. Also tell how to find tableid, viewid, baseid etc. which are

        • How do I delete a folder in Marketing Automation?

          Folders are used across contact lists and segments, and email templates. How do I delete a folder once it's been created?

        • Portal Approval Process

          Hi Zoho team and fellow users, I am seeking a method to establish a multi-step approval process between a Zoho user and a portal user (Custom Portal) to review and approve requests through the Custom Portal. For instance, within this setup, one of the

        • Narrative 8: Intelligent in-app support that's instantly available anytime and anywhere

          Behind the scenes of a successful ticketing system - BTS Series Narrative 8: Intelligent in-app support that's instantly available anytime and anywhere The App Support Across Platforms (ASAP) add-on for Zoho Desk is an independent application that integrates

        • Navigation issue — unable to return to Customer page after opening Receipt from Transactions

          Steps to reproduce: Open a Customer record. Go to Transactions tab and open a Receipt by clicking its receipt number. After viewing the receipt, clicking browser Back or closing the receipt does not reliably return me to the original Customer record (I

        • Thermal Printer Option Needed for Delivery Challan Templates

          Currently in Zoho Books, the Delivery Challan template only supports A4 and A5 page sizes. However, in many businesses (especially retail and hardware), we use thermal printers (like 3-inch or 4-inch rolls) to print delivery challans. It would be very

        • Separate Default Payment Modes for Receipts vs. Payments

          Right now, when I set a default Payment Mode via a customer invoice or Payments Received screen, that same mode shows up for vendor payments (Purchases → Payments Made). 🔹 Request: We need different default modes for: Customer receipts (e.g., default

        • Next Page