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.


    • Sticky Posts

    • Kaizen #222 - Client Script Support for Notes Related List

      Hello everyone! Welcome to another week of Kaizen. The final Kaizen post of the year 2025 is here! With the new Client Script support for the Notes Related List, you can validate, enrich, and manage notes across modules. In this post, we’ll explore how

    • Kaizen #217 - Actions APIs : Tasks

      Welcome to another week of Kaizen! In last week's post we discussed Email Notifications APIs which act as the link between your Workflow automations and you. We have discussed how Zylker Cloud Services uses Email Notifications API in their custom dashboard.

    • Kaizen #216 - Actions APIs : Email Notifications

      Welcome to another week of Kaizen! For the last three weeks, we have been discussing Zylker's workflows. We successfully updated a dormant workflow, built a new one from the ground up and more. But our work is not finished—these automated processes are

    • Kaizen #152 - Client Script Support for the new Canvas Record Forms

      Hello everyone! Have you ever wanted to trigger actions on click of a canvas button, icon, or text mandatory forms in Create/Edit and Clone Pages? Have you ever wanted to control how elements behave on the new Canvas Record Forms? This can be achieved

    • Kaizen #142: How to Navigate to Another Page in Zoho CRM using Client Script

      Hello everyone! Welcome back to another exciting Kaizen post. In this post, let us see how you can you navigate to different Pages using Client Script. In this Kaizen post, Need to Navigate to different Pages Client Script ZDKs related to navigation A.

    Nederlandse Hulpbronnen


      • Recent Topics

      • How do I trigger a Flow based on a campaign response?

        Is there a way to trgiider a Zoho Flow based upon a lead opening an email sent via Zoho Campaigns? I see that the data is recorded in the 'Campaigns' section of Zoho CRM under 'Member Status' and I want to trgigger a flow based upon that record changing.

      • All Zoho Flows are filtered

        My two flows operate perfectly when I run them as a test, but when they're activated each run ends with a status of neither success, nor fail, but filtered. I haven't set up any filters. I don't see where to turn off filters. When I test run on a sequence

      • Creating Multiple Items on Sales Order

        Hi, I’m trying to automate some processes using Zoho Flow, specifically the creation of sales orders in my Zoho Inventory. However, Zoho Flow's Create Sales Order function can only add one item. I would like to include multiple items in a single sales

      • Problem Connection from Zoho Flow and Gravity Form

        I obtained my API key from Gravity Forms via WordPress. However, when I enter my Zoho Flow, it states: Gravity Forms says, 'You are not authorised to access the API." I tried recreating a new API key, but it is still not working.

      • Eventbrite Email Field in Zoho Flow Returns "Info Requested" Instead of Actual Email

        Hi Zoho team, I'm using Zoho Flow to connect Eventbrite with Zoho CRM. My goal is to automatically add event attendees as leads in Zoho CRM. I’ve set up the flow and mapped the ${trigger.profile_email}} field to the Email field in CRM. However, I'm running

      • "Invalid value passed for Product ID" Error in Zoho Flow "Create Sales Order" Node

        Hello Zoho Community, I’m facing an issue with Zoho Flow while trying to create a sales order in Zoho Inventory using the "Create Sales Order" node. Here’s a detailed explanation of my setup and the problem: What I’m Trying to Achieve I’m building an

      • Associating Project with an Account via Flow

        I'm using flow to create a Project based on a Deal status update using flow. The fields exist to pass the Account Name through properly, but when you view the Projects module in a CRM Account Record it doesn't automatically associate the new Project Record

      • How to follow up a member in a meeting?

        Hello, I make weekly meeting online with a lot of people. I want(I've been using calendly to do it). I want to do a follow-up to it. I want to send messages via Zoho-flow to all the member that participated in the meeting. How can I do it?

      • Setting Delays in Invoice Reminder Flow

        I am currently working on a flow that sends reminders for unpaid invoices. The flow is designed to delay actions until specific intervals before the due date: A reminder should be sent 7 days before the due date. A second reminder should be sent 3 days

      • Get Sales Orders Related to Inventory Item

        Dear Team, I'm just wondering if there is a way to get a list of all Sales Orders related to a specific Inventory Item. I did search all articles but couldn't find any article that could help.

      • Endpoint Central Cloud Asset Update from Fresh Service

        All, Does anyone use the asset management feature in Fresh Service? I'd like some help on building a flow to update asset attributes in Endpoint Central Cloud based off of an update to that same asset in Fresh Service. The trigger is "asset is updated"

      • Zoho Flow Export to Deluge

        It would be great to take a user built zoho flow and export the entire flow as a deluge script including having multiple connected applications (showing the API connections and webhooks) and different functionality in the other applications interacting

      • Action Iteration/Loop using Zoho Flow

        Trying to use Zoho Flow for automating following Context - A zoho form entry which has image upload field with upto 5 images setting and files are saved into Workdrive. After form is submitted need to create folder based on some fields and move files

      • Zoho Flow - Unable to evaluate formatDate with Zoho Invoice Date Field for Calendar Integration

        Hello Community, I'm trying to automate the creation of all-day events in Zoho Calendar whenever a new invoice is created in Zoho Invoice. I'm using Zoho Flow for this automation. My Goal: When an invoice is created with a specific "Event Date," I want

      • Zoho Inventory Sales Order Items

        I'm trying to build automation using Zoho Flow to add items to a Sales Order. In the automation options for both "create sales order" and "update sales order", The item ID is required. However, when I update the Sales Order, it's just replacing the item

      • Permissions for Azure Devops connection

        I am trying to set up a connection with our Azure DevOps org but it keeps giving me this error. On Azure I should be able to have admin-level access to everything. Can you please point me to which permission this is checking for so I can enable it?

      • Best way to start zoho inventory with bulk openning stock

        We are already using zoho book since long time for cars trading company. Now to streamline more, would like to import the excel data of closing stock of inventory to zoho inventory and to start on. Since we need to track each VIN (unique vehicle id number)

      • My IMAP mail suddenly stopped working

        On my iPhone and iPad, IMAP stopped working for my Zoho account with the error "User name or password incorrect" and "Invalid credentials failure" however I was able to access via web with the same credentials. Also stopped working on Apple Mail client.

      • Confused by the distiction between matched and categorized when reconciling a bank statement an how to

        I used to use quickbooks. In quickbooks, it was possible to use the check writing feature to add an expense that was on the bank statement that did not go through the AP and check writing process. I would write a check, assign it a number like etf (for

      • Not Receiving OTP • https://voters.eci.gov.in/home/family

        Hello Customer, Greetings from Zoho Mail. Upon a detailed review of our delivery logs, we can confirm that other Zoho Mail users are successfully receiving OTP emails from eci.gov.in. However, in your specific case, it appears that the OTP emails are

      • WorkDrive for Excel Add on

        Dear Sir/Madam Have installed Workdrive for Microsoft add on But unable to view the same added in Excel

      • Zoho Calendar s’enrichit avec une intégration à Zoho People et Zoho Cliq

        Les journées de travail ne se déroulent jamais exactement comme prévu. Une conversation informelle devient une séance d’échange d'idées, une absence modifie un planning, et votre agenda se retrouve vite décalé par rapport à la réalité. Chez Zoho Calendar,

      • Holidays

        Hi; For defining Holidays, you need to add logic to handle the year as well as the month & day. We need to be able to enter Holidays for the next year. I need to add a holiday for January 2, 2017, but I can't until January 1st, which is a Sunday and we

      • Zoho public comments are confusing and unnecessary

        Hi, we use zoho desk and have issues with public comments. We started using them because the "Reply" option just seemed very clumsy because of the following reasons: - the top "Reply" button starts what seems to be a regular email, showing the entire

      • Introducing parent-child ticketing in Zoho Desk [Early access]

        Hello Zoho Desk users! We have introduced the parent-child ticketing system to help customer service teams ensure efficient resolution of issues involving multiple, related tickets. You can now combine repetitive and interconnected tickets into parent-child

      • Zoho API - ticket creation - Validation failed for the condition : Ticket Status Info should not be empty

        Hi ! I'm trying to create ticket through Zoho API, and I'm getting a 422 response : "Validation failed for the condition : Ticket Status Info should not be empty" My request looks like this : curl --location 'https://desk.zoho.eu/api/v1/tickets' \ --header

      • Deleting unwanted ticket replies

        Hello, In a Zoho Desk Ticket thread, sometimes one of the recipients has auto-reply activated. This creates a new message in the Ticket thread that not only pollutes the thread, but most importantly cannot be replied properly because usually auto-reply e-mails don't do "reply all", so the other recipients are not included. I want to delete such a message in the Ticket thread. I searched the help of Zoho Desk, but only found a way to mark as Spam (https://help.zoho.com/portal/kb/articles/marking-support-tickets-as-spam)

      • Desk DMARC forwarding failure for some senders

        I am not receiving important emails into Desk, because of DMARC errors. Here's what's happening: 1. email is sent from customer e.g. john@doe.com, to my email address, e.g info@acme.com 2. email is delivered successfully to info@acme.com (a shared inbox

      • System-generated support email added in CC on “Reply All”

        Hi, I recently set up Zoho Desk for a client and we are trying to prevent the Zoho Desk system-generated support email address (not the mailbox used as the department’s "From address") from being automatically added in CC when agents use “Reply All” on

      • Applying Excess Payments & Conflict Invoices Due to Cancelled Items

        I’m running into several issues that appear to stem from deeper-than-expected overlap between Zoho Finance (Books/Inventory) and Zoho POS. The level of coupling between these systems seems greater than what was originally communicated, and it’s leading

      • Please, make writer into a content creation tool

        I'm tired of relying on Google Docs. I'm actually considering moving to ClickUp, but if Writer were a good content creation tool instead of just a word processor, I would finally be able to move all my development within the Zoho ecosystem, rather than

      • Zoho Sheet - Desktop App or Offline

        Since Zoho Docs is now available as a desktop app and offline, when is a realistic ETA for Sheet to have the same functionality?I am surprised this was not laucned at the same time as Docs.

      • Convenience Fees

        I use Zoho Invoice for invoicing my billboard customers.  I have a few customers that want to pay using credit cards and ACH.  As a result, I have integrated an Online Payment Gateway (Stripe) for these customers.  I currently charge these customers a "Convenience Fee" of 3% for using this service as I typically only take checks as payment (and the gateway charges 2.9% + $0.30 per transaction).  I do this by creating a separate line item on the invoice and adding 3% to it.  I would like to offer

      • HTML Tags added to Reports with Notes

        Recently Zoho added the ability to markup text within notes. That way, users can change font size, colors, etc. It's a great change. However, since the change, reports that include a column for "Note Content" are printing HTML tags within the report.

      • New Feature: Audit Log in Zoho Bookings

        Greetings from the Zoho Bookings team! We’re excited to introduce Audit Log, a new feature designed to help you track all key actions related to your appointments. With Audit Log, you can maintain transparency, strengthen security, and ensure accountability.

      • Bill automation in Zoho Books

        Hi I am looking for 3rd-party options for bill automation in zoho which are economical and preferably have accurate scanning. What options do I have? Zoho's native scanning is a bit pricey

      • Restrict Payment Methods

        Allow us to restrict certain payment methods specific for each customer.

      • Emails Getting Delay Error Messages - Status: 451

        Hi. Hoping someone can help. I've been using Zoho for over a year now with no problems at all. Today, the majority of my emails I send out or reply to are getting this error: This message was created automatically by mail delivery system. THIS IS A WARNING MESSAGE ONLY. YOU DO NOT NEED TO RESEND YOUR MESSAGE. The original message was received at Sun, 22 Dec 2019 18:20:19 -0800 from wazza@amninjas.com [wazza@amninjas.com] ----- The following addresses had fatal errors ----- [Status: ERROR, Address:

      • Add Domain

        Hello Zoho Support, My Zoho Sites project accidentally auto-added domains with http:// prefix and duplicated domain entries. Current domains list shows: http://www.kinhtethethao.com.vn (Primary – cannot be removed) http://www.kinhtethethao.com.vn (verification

      • Kaizen #221: Workflow & Actions Reports APIs

        Over the last few weeks, we have joined Zylker Cloud Services as they restructured their automation ecosystem using Workflow APIs and Actions APIs. Along the way, we discovered how to audit workflows, update old rules, create new ones, and manage associated

      • Next Page