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

    • Kaizen #226: Using ZRC in Client Script

      Hello everyone! Welcome to another week of Kaizen. In today's post, lets see what is ZRC (Zoho Request Client) and how we can use ZRC methods in Client Script to get inputs from a Salesperson and update the Lead status with a single button click. In this

    • 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

    • Recent Topics

    • Customer/Vendor Portal session duration - can it be extended?

      Hi all, We'd like to know how long the login session lasts for the Customer/Vendor Portal in Zoho Books, and whether there's any way to extend it (either through settings or via support/API). Right now this is causing a pretty poor experience for our

    • is it possible to adjust the date field to show Monday as a first day

      Hi, Is it possible to adjust somewhere the date field, so the first day of the week is Monday (instead of Sunday)? Thank you! Ferenc

    • Last Name Required

      Is there a way in CRM with the last name field to make it not mandatory? We are taking leads from a web form created in ZoHo CRM Form builder Form fields for this specific form are: First Name E-Mail Question/Comment Previously when using another form

    • Cannot receive emails

      Sent one days ago still no feedbacks, cannot call customer services number

    • Rating system button not translated to Spanish + "Done"

      I've run into a issue with the new system for rating support interactions when the chat is ended: Missing Spanish translation: The button is still showing in English as "Done," even though the interface language is set to Spanish. It should display something

    • Deprecation Notice: OpenAI Assistants API will be shut down on August 26, 2026

      I recieved this email from openAI what does it means for us that are using the integration and what should we do? Earlier this year, we shared our plan to deprecate the Assistants API once the Responses API reached feature parity. With the launch of Conversations,

    • Zoho Cliq not working on airplanes

      Hi, My team and I have been having this constant issue of cliq not working when connected to an airplane's wifi. Is there a reason for this? We have tried on different Airlines and it doesn't work on any of them. We need assistance here since we are constantly

    • Completed Calls don't associate with Scheduled Calls

      I schedule calls at specific times so that they are easily viewable on my phone. I open a given call on my phone and use the call icon at the bottom of the screen. I complete the call and select "yes" when asked if I want to log the call. I enter the

    • Need Parallel Transitions for Zoho Desk Blueprint

      Zoho CRM's Blueprint already supports Parallel/Multiple Transitions, letting multiple transitions run simultaneously between two states (refer to https://help.zoho.com/portal/en/kb/crm/process-management/blueprint/articles/parallel-and-multiple-transitions-configuration-and-usage#Using_Parallel_Transitions)

    • Zoho Forms integration Google Sheets

      Hi, quick one. I wonder if you can help. I have a Zoho Form integrated to a Google Sheet. The fields are showing across the top, but no records are there. I have checked in Zoho Forms to see if there have been any failed integrations, but there are none.

    • ZoHo Mail & MCP connectors

      Is ZoHo working an MCP connector for mail?? I find it very useful in Gmail to have Claude summarize messages for me. Thanks Jim P.S. Sorry if this is the incorrect forum. Mods please adjust as necessary.

    • Introducing the Required Skills field for Job Openings

      We're excited to announce a new enhancement to the Job Openings module - the Required Skills field, formerly known as the Skillset field. This enhancement brings improved functionality and a fresh UI to help you define your job requirements effortlessly.

    • ZDialer app sign in replys: Account doesn't exist

      I couldn't find Zoho Voice for a category. Problem: I have an active Zoho account with Zoho Voice and Zoho CRM. I can successfully sign into both services from my computer using my email address. However, the ZDialer mobile app redirects to accounts.zoho.com

    • Checking validity of fields when converting Leads

      Hello, When converting a lead to a contact (and Company), we want to check beforehand if some fields are filled. Ideally a script or action should be triggered to check the fields and then continue if all is fine, or display a message and stop the process. Reasons: Leads are created with few data at the beginning (often name and email), and with time the profile is augmented. Until it reaches some minimal data, this leads shouldn't be convertable as Contacts. Contacts are people we are making business

    • Is it possible to have conditional pages?

      We have a Document, which consists of multiple different subdocuments. We want to have the subdocuments as pages inside a doc and only show them in specific cases. Is it possible to have conditional pages inside of a mail merge based on CRM data?

    • Reports not adding up

      I have a subform that we add lines for costs. These are added up in the aggregate (see image 1). When I pulled these into reports, I link the aggregate subform cost, but it keeps adding each subform line, instead of just the PO Subject and the numbers

    • 【西日本初開催】「AI and DX Summit 2026」のご案内

      ユーザーの皆さま、こんにちは！ 西日本初開催となるZoho ユーザー / 検討中の方々向けイベントのご紹介です。 AI・DX大型カンファレンス「AI and DX Summit 2026」を、2026年7月16日（木）に開催します。 会場は、ウォルドーフ・アストリア大阪。 グラングリーン大阪直結のラグジュアリーな空間で、AIとDXの最新トレンド、実践事例、 展示、ネットワーキングが集結する、特別な1日をお届けします。 👉イベントページを見る ━━━━━━━━━━━━━━━ AIとDXの“今”を、体感。

    • creating an alias

      your instructions for creating an alias are wrong. there is no add alias in my mail account. also i dont have a control panel link just a settings link how do i really make an alias

    • Set Mandatory Lookup Fields in Ticket Layout

      I added a custom module called 'Site' in the desk. I also added a lookup field 'Site' in the ticket layout. Now, I want the 'Site' field to be mandatory in the blueprint transition. How can I do that?

    • viewing deactivated users on a calendar

      I was just on help chat Me: Ok, Can i verify that I understand correctly? If i fire someone, I need to deactivate them in zoho asap so they don't have access to company data (like customers). When I do that, there is no way to see them on the CRM calendar

    • Non-depreciating fixed asset

      Hi! There are non-depreciable fixed assets (e.g. land). It would be very useful to be able to create a new type of fixed asset (within the fixed assets module) with a ‘No depreciation’ depreciation method. There is always the option of recording land

    • Reconciliation: don't auto-select transactions outside the entered Period

      When initiating a reconciliation for a defined Period (say April 1 to April 30), Zoho Books auto-checks transactions whose Statement Detail dates fall after the period end date. With "Show based on grouped bank statements" enabled (the default), the screen

    • Settling Credit Card Payments

      Hello All, Been using Zoho books recently. The banks have been configured to add credit cards as a liability account in the chart of accounts. we have incurred payments and have recorded these expenses as paid thru credit card However when it comes to

    • Integrations of freelancing websites

      It is a customized effort which we require to be implemented on our Zoho recruit. We want that Upwork and Freelancer and other such freelancing websites get integrated into our Zoho Recruit, so that, any communication which we do with the candidates from those websites gets directly recorded on our Zoho recruit and we don't have to move back and forth by opening any other application other than Zoho. If this can be done, we think of managing the entire communication through Zoho recruit and use it

    • Create Package From A Picklist

      Dear Zoho, Can it be made possible to create a package from a picklist? The reason our company makes a picklist is for that to become a package Our sales orders have 600-1000 items I hope that makes it clear that it's hard to delete 990 items when we

    • Ability to Set Client Name During Portal Invitation

      Hi Zoho Team, We would like to suggest an important enhancement to the Zoho Creator Client Portal functionality. Zoho Creator recently introduced the option to set a client’s display name in the Client Portal settings, which is very helpful for creating

    • Once the photo is clicked, the image is not making a reliable match against profile image on the organization's Zoho People database.

      Please advise how to improve reliability of the kiosk image match for clocking in/out. We are trialing this however not having success after the first image match. I'm guessing these matched because the profile image was taken the same day and the employee

    • Announcing new features in Trident for Mac (1.38.0)

      Hello everyone! We’re excited to introduce the latest updates to Trident, which are designed to take workplace communication to the next level. Let’s dive into the details. Access message drafts from anywhere. Have you ever typed out a detailed response

    • Direct Access and Better Search for Zoho Quartz Recordings

      Hi Zoho Team, We would like to request a few enhancements to improve how Zoho Quartz recordings are accessed and managed after being submitted to Zoho Support. Current Limitation: After submitting a Quartz recording, the related Zoho Support ticket displays

    • Celebrating the businesses behind Bigin: Customer Awards 2026

      Hello Biginners, We're excited to announce the very first Bigin Customer Awards! If Bigin has played a role in your organization's journey, we'd love to hear about it. Share your story for a chance to be recognized among the best Bigin users across industries.

    • What's New in Zoho Analytics - June 2026

      Hello Users! This month is about meeting your data wherever it lives: reaching it from AI assistants, bringing it in from more sources, and keeping it flowing with less manual effort. Here's what's new across Ask Zia and AI, data import, integrations,

    • Quick response appreciated - Vertical line on line graph

      Hi, I need to present a line graph showing historical and future data points. What I'd like to do is add a vertical line to denote the current date - to clearly show what is past and what is future. How can I achieve this please? Thank you Marc

    • Zoho forms with iframe and Javascript embedding not working

      I have integrated zoho forms with iframe and Javascript embedding in my Ruby on Rails application.There is problem form action are not working on clcik on just Chrome browser mobile view ,it's working on Firefox and others.So, what is the solution of

    • Agentes Zia en Zoho CRM: ¿Qué son los agentes de IA?

      Los agentes de IA son entidades digitales autónomas impulsadas por inteligencia artificial que pueden actuar de forma similar a un empleado humano. Los chatbots convencionales y la IA generativa pueden proporcionar información o redactar contenido, y

    • [Free webinar] Creator Tech Connect - Zoho Creator product updates, July 2026

      Hello everyone, We’re excited to invite you to another edition of the Creator Tech Connect webinar. About Creator Tech Connect The Creator Tech Connect series is a free monthly webinar comprised of pure technical sessions, where we dive deep into the

    • What's New in Zoho POS - June 2026

      Hello everyone, Welcome to Zoho POS’s monthly update, where we share our latest feature updates, enhancements, events, and more. Let’s take a look at how June went. Zoho POS for macOS We are excited to announce that we extended our front end billing application

    • Different languages for users

      Hello, Do you plan to enable individual users to select their languages for interface? Currently language can be changed for everyone - it looks like a settings for a whole portal, which is not good when you are working internationally. Best regards,

    • People 5.0 widget and API questions

      While creating Widget for People 5 I found couple issues that I can’t find answer on my own: 1) How to get leave requests according to this API https://www.zoho.com/people/api/get-records-v2.html. I tried: requestData = { "url": "https://people.zoho.eu/api/v2/leavetracker/leaves/records",

    • How to Fix the Corrupted Outlook 2019 .pst file on Windows safely?

      There are multiple reasons to get corrupted PST files (due to a power failure, system crash, or forced shutdown) and several other reasons. If You are using this ScanePST.EXE Microsoft inbuilt recovery tool, it only supports the minor corruption issue

    • What's New in the Trips Experience

      We've redesigned the Trips module to make trip management simpler and more intuitive. Here's a quick look at what's changed. Simplified Trip Creation and Itinerary Management Flexible Trip Creation: You can now create and save a trip without adding itineraries

    • Next Page