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.



      Zoho Campaigns Resources


        • Desk Community Learning Series


        • Digest


        • Functions


        • Meetups


        • Kbase


        • Resources


        • Glossary


        • Desk Marketplace


        • MVP Corner


        • Word of the Day


        • Ask the Experts


          Zoho CRM Plus Resources

            Zoho Books Resources


              Zoho Subscriptions Resources

                Zoho Projects Resources


                  Zoho Sprints Resources


                    Zoho Orchestly Resources


                      Zoho Creator Resources


                        Zoho WorkDrive Resources



                          Zoho CRM Resources

                          • CRM Community Learning Series

                            CRM Community Learning Series


                          • Tips

                            Tips

                          • Functions

                            Functions

                          • Meetups

                            Meetups

                          • Kbase

                            Kbase

                          • Resources

                            Resources

                          • Digest

                            Digest

                          • CRM Marketplace

                            CRM Marketplace

                          • MVP Corner

                            MVP Corner




                            Zoho Writer Writer

                            Get Started. Write Away!

                            Writer is a powerful online word processor, designed for collaborative work.

                              Zoho CRM コンテンツ




                                ご検討中の方