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

    • Kaizen #210 - Answering your Questions | Event Management System using ZDK CLI

      Hello Everyone, Welcome back to yet another post in the Kaizen Series! As you already may know, for the Kaizen #200 milestone, we asked for your feedback and many of you suggested topics for us to discuss. We have been writing on these topics over the

    • Recent Topics

    • Single Portal for Multiple Apps

      Hello, I'm just getting started with Zoho and I'm very overwhelmed. I am currently using the free trial of Zoho One, but if I can figure it out, I intend to upgrade to the paid version. Zoho One, of course, gives me  access to an entire suite of services/applications. One of the things I'd like to do is have a single place for clients (customers?) to log in and view current projects, invoices, contact information, etc. A single login for my clients. A single portal. I've come across documentation

    • 【開催報告】 大阪 ユーザー交流会 2025/11/20（木）Zoho CRM Plus 活用のアイデアとデータの“見える化”

      皆さま、こんにちは。コミュニティチームの中野です。 11/20（木）に、大阪 ユーザー交流会を開催しました。本投稿では、その様子をお届けします。 当日の登壇資料などもこちらに共有しますので、参加できなかった皆さまもご参照ください。 （Zoho 社員セッションの登壇資料については、11/28(金)に開催予定の東京回の開催報告で共有いたします） 今年2回目の開催となる大阪 ユーザー交流会では、株式会社KDDIウェブコミュニケーションズ 山田さんによる Zoho CRM Plus の事例セッションのほか、「Zoho

    • Every rating counts: Shaping customer experience

      We are back to that beautiful time of the year. It is the season to reflect, be thankful, and appreciate everything that has happened throughout the year. Thanksgiving is a time we connect with our family, friends, and relatives to strengthen relationships,

    • Paging through API results. a major gap in your documentation.

      There is no way for me. to get all of my data through a single API call. Typically REST APis have mechanisms for paging through API results. But the documentation for the API I am using: https://desk.zoho.com/DeskAPIDocument#Introduction Has no mention

    • How to remove duplicate contacts

    • Custom Field for Subscription

      Hi, I can't find a way to add a custom field (to contain a license key generated from our software) against a subscription? Is the only place to add this information in the Invoice module (as custom field for invoice)? When a customer views his subscription via the customer portal, there appears no way to display a license key for them? The invoice is not the natural place to store a license key for a particular subscription, so where else can this be stored and displayed?

    • Custom View and Custom Fields on Zoho Books

      Hi, I have some custom fields on Estimates and Invoices. I also use Custom Views so I can have a lot of information at a glance. I want to include my custom fields as columns in my custom views of estimates / invoice, but it looks like is not possible.

    • Cannot Access Subform Display Order in Deluge

      As highlighted in this community post, we still have to deal with the significant limitation of not being able to access the user-sorted order of subform rows through Deluge. This creates a major disconnect between the UI capabilities and backend automation,

    • Dropdown data depends on filters in another field.

      In my quote form I have a lookup field called Reseller that pulls from Accounts. I would like it to pull from Accounts, but only those accounts with an account field 'Type' where that is 'Reseller'. Does anyone know a way to do this? Similarly, I'd like

    • Is it possible to adjust the web browser tab title (when a ZoHo Desk ticket is opened)

      Hi All, When I open a ZoHo Desk ticket in a web browser, the tab title (text that appears at the top of the browser tab) uses the logic: *company icon picture* (xxxx) #ticket number - company name See below (highlighted in red) for reference. Company

    • Add Attachment Support to Zoho Flow Mailhook / Email Trigger Module

      Dear Zoho Support Team, We hope you are well. We would like to kindly request a feature enhancement for the Mailhook module in Zoho Flow. Currently, the email trigger in Zoho Flow provides access to the message body, subject, from address, and to address,

    • Collections Management: #7 Common Mistakes during Payment Collection

      Payment collection may appear straightforward in most cases. Still, as your customer base expands and transaction volume increases, it becomes clear that even small inefficiencies can lead to delayed payments, increased support load, or even revenue loss.

    • Recruit paid support?

      Hi all, Could anyone who has paid support package advise if it provides value for money with regards to support response times? Exploring the idea as unfortunately when we have faced issues with Recruit it has been a 7+ day timescale from reporting to

    • Unusual activity detected from this IP. Please try again after some time

      When i try to create new addresses on my account i am getting this error, it has been 24 hours now and i am still getting this error can anyone help

    • Sortie de Zoho TABLE ??

      Bonjour, Depuis bientôt 2 ans l'application zoho table est sortie en dehors de l'UE ? Depuis un an elle est annoncée en Europe Mais en vrai, c'est pour quand exactement ??

    • Rename Record Summary PDF in SendMail task

      So I've been tasked with renaming a record summary PDF to be sent as part of a sendmail task. Normally I would offer the manual solution, a user exports the PDF and uploads it to a file upload field, however this is not acceptable to the client in this

    • Limitation with Dynamic Email Attachment Capture

      I've discovered a flaw in how Zoho Creator handles email attachments when using the Email-to-Form feature, and I'm hoping the Zoho team can address this in a future update. The Issue According to the official documentation, capturing email attachments

    • Recruit API search

      Hi all, Attempting to call the search api endpoint from Postman using the word element as mentioned in api docs Search Records - APIs | Online Help - Zoho Recruit When making the call to /v2/Candidates/search?word=Saudi receive response of { "code": "MANDATORY_NOT_FOUND",

    • Text/SMS With Zoho Desk

      Hi Guys- Considering using SMS to get faster responses from customers that we are helping.  Have a bunch of questions; 1) Which provider is better ClickaTell or Screen Magic.  Screen Magic seems easier to setup, but appears to be 2x as expensive for United States.  I cannot find the sender id for Clickatell to even complete the configuration. 2) Can customer's reply to text messages?  If so are responses linked back to the zoho ticket?  If not, how are you handling this, a simple "DO NOT REPLY" as

    • Custom Field in Zoho Projects pulling into Analytics

      We have a client that we have built our their new business process using Zoho Projects, and we have build a lot of custom fields with their their Projects where they are capturing specific data points that we want to be able to track and pull data, as

    • Marketer's Space - Holiday season email marketing tips you should know

      Hello Marketers! Welcome back to another post in Marketer's Space! 'Tis the season—that time of the year everyone eagerly anticipates. While most look forward to relaxing, marketers will be super-busy from late November to early January. Mistakes can

    • Zia Competitor Alerts made easy with Zia's suggestions

      Hi everyone, In addition to the existing manually added competitors, Zia will now find your competitors for you - instantly. Earlier, you had to identify competitors through research manually, support tickets, or tradeshows—a time-consuming process that

    • Add Custom Field Inside Parts Section

      How to Add Custom Field Inside Parts Section in Workorder like Category and Sub- Category

    • Zoho CRM Community Digest October 2025 | Part 2

      Hello Everyone! From new mobile capabilities and smarter integrations to real-world workflow fixes and developer insights, all the highlights from the second half of October is covered right here. Let’s dive in. Product Updates: Zoho CRM Mobile Updates:

    • MTA - BAD IP reputation by outlook/hotmail

      Messages to Microsoft email servers are bouncing back due to poor reputation. Message: 4.7.650 The mail server [136.143.188.206] has been temporarily rate limited due to IP reputation. For e-mail delivery information see https://postmaster.live.com (S775)

    • Understanding Zoho Contracts

      Effective contract management relies on systems that are structured, organized, and reliable. Every feature, workflow, rule, and restriction in Zoho Contracts are designed the way they are to ensure consistency, compliance, and control across every stage

    • Tip of the Week #76– Automate your inbox during vacation in Zoho TeamInbox

      When you're on vacation or away from your desk, the last thing you want is for important emails to be missed or left unanswered. The good news is, you can easily set up rules in Zoho TeamInbox to assign incoming messages automatically to a teammate who's

    • Domain restriction for User Management actions in Zoho One

      Greetings, Zoho One Admins! To strengthen account security further and safeguard user management settings, we are imposing domain-based restrictions for user account-focused admin actions in Zoho One. In addition to password reset of user, organization

    • Zoho Mail iOS app update: Signature

      Hello everyone! In the latest version(3.1.7) of the Zoho Mail app update, we have brought in support to create, edit and remove signature within the app. You can create signature from the compose screen as well as from within the Settings module(inside

    • Copy paste from word document deletes random spaces

      Hello Dear Zoho Team, When copying from a word document into Notebook, often I face a problem of the program deleting random spaces between words, the document become terribly faulty, eventhough it is perfect in its original source document (and without

    • Desktop app doesn't support notecards created on Android

      Hi, Does anybody have same problem? Some of last notecards created on Android app (v. 6.6) doesn't show in desktop app (v. 3.5.5). I see these note cards but whith they appear with exclamation mark in yellow triangle (see screenshot) and when I try to

    • Approval Button in Subform

      Hi Team, I’m working on a subform-based requirement where users will submit requests, and these requests must go through approval by multiple team managers. Each line item in the subform needs to be individually approved or declined based on the user's

    • Reporting Limitation on Lead–Product Relation in Zoho CRM

      I noticed that Zoho CRM has a default Products related list under Leads. However, when I try to create a report for Lead–Product association, I’m facing some limitations. To fix this, I’m considering adding a multi-lookup field along with a custom related

    • Setting checkbox value on template in Sign from Creator

      Good day, Please help me understand how do I set a tick from a checkbox in Creator into a checkbox on a Sign template. Below is the only values on the Sign template and the code from Creator, "field_boolean_data": {}, "field_date_data": {}, "field_radio_data":

    • Zoho Projects - Unread Comment Icon

      Hi Projects Team, It would be great if there was a notification I con on the comments icon so it's easy to see which tasks have new comments. Something like a red circle with a number of unread comments would be great. Thanks for considering my feed

    • Zoho Projects - Update Feed via API

      Hi Projects Team, Please consider adding an API to allow update and retrieval of messages to the Feed. Thank you

    • Automated log-out/session end

      I'm concerned about security of our data. Is it possible to set an automatic time-out for user sessions on Zoho CRM, after a certain period of inactivity or when the session reaches a certain duration (12 hours perhaps)? 

    • Subform auto populate values

      Hi Team, I’m trying to retrieve values from Zoho People using API functions and dynamically populate them into a subform. For example, I’ve created a form with several fields that users will fill out. Based on their input, I need to fetch records from

    • What is New in CRM Functions?

      What is New in CRM Functions? Hello everyone! We're delighted to share that Functions in Zoho CRM have had a few upgrades that would happen in phases. Phase 1 An all new built-in editor for better user experience and ease of use. ETA: In a couple of days.

    • Gantt Chart - Zoho Analytics

      Are there any plans to add Gantt Charts capabilities to Zoho Analytics?

    • Next Page