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,
- Handling Manual Entries of Data in Subforms
- Scenario
- Solution
- Add a button in Create Page(Canvas)
- Create a Function to fetch data from Sheet
- Create a Client Script to get Sheet Details, fetch and populate data
- Summary
- 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)
- SheetData = zoho.sheet.getRecords(sheetid,"Sheet1");
- d = list();
- for each record in SheetData.get("records")
- {
- d.add(record);
- }
- info d;
- 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.
- var a = ZDK.Page.getField("Product_list").getValue();
- var casesheetid = ZDK.Client.getInput([{ type: 'text', label: 'Enter the Sheet ID' }], 'Sheet details', 'OK', 'Cancel');
- console.log("casesheetid " + casesheetid);
- if (casesheetid == null) {
- ZDK.Client.showAlert("Enter the *Case Sheet ID - Import* to import data");
- }
- let csvdata = []; // list to store csv data
- let imported_list = [
- {
- "Product_Name": {
- "id": "",
- "name": ""
- },
- "Quantity":"",
- "Unit_Price": "",
- "Amount": ""
- }
- ]
- try {
- ZDK.Client.showLoader({type: 'page', template:'vertical-bar', message: 'Loading ...'});
- resp = ZDK.Apps.CRM.Functions.execute("getData", casesheetid); // invoke function that extracts data from Sheet
- }
- catch {
- ZDK.Client.showAlert("Unexpected error occured" + resp);
- }
- var k = resp._details.userMessage;
- k = "[" + k + "]";
- let jsonArray = JSON.parse(k);//Convert to a valid JSON array format
- jsonArray.forEach((pr, i) => {
- csvdata.push(pr);
- });
- if (csvdata.length) {
- log("csvdata length" + csvdata.length);
- csvdata.forEach(row => {
- log("Product_Name" + row.Product_Name);
- log("Case Version" + row.Version);
- log("Case " + row.Case);
- imported_list.push({ Product_Name: { id: "", name: row.Product }, Quantity: row.Quantity, Unit_Price: row.Unit_Price, Amount: row.Amount});
- });
- }
- imported_list.shift(); // remove the dummy row
- imported_list.forEach((row, i) => {
- apiResponse = ZDK.Apps.CRM.Products.searchByCriteria(`(Product_Name:equals:${row.Product_Name.name})`);
- if (apiResponse.length == 0) {
- // If there is no matching record, ask user if a new record has to be created or not.
- var count = i + 1;
- ZDK.Client.showAlert("*S.No* " + count + " - '" + row.Product_Name.name + "'" + " is an *invalid Product Name*", "Invalid Product Name", "Okay");
- }
- apiResponse.forEach((rec) => {
- if (!(rec.id == null)) {
- row.Product_Name.id = rec.id;
- }
- });
- });
- ZDK.Page.getField('Product_list').setValue(imported_list);
- ZDK.Client.hideLoader();
- In this code, several ZDKs are used for various purposes. Click on the ZDK hyperlinks to learn more.
- User Input: The code uses ZDK.Client.getInput() to fetch a "Sheet ID" from the user. This input is crucial for further operations.
- 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().
- Function Invocation: The code invokes a CRM function named getData using ZDK.Apps.CRM.Functions.execute(), passing the sheet ID as a parameter.
- Loader Display: To indicate that a process is running, a loader is displayed using ZDK.Client.showLoader().
- 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.
- 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.
5. Related Links
- Client Script Overview
- Creating a Client Script
- Client Script Events
- Client Script IDE
- Use cases of Client Script
- Troubleshooting Client Script
Previous Post: Kaizen 179: Choosing between ZDK CLI and Web UI : When to Use Each | Kaizen Collection: Home
Access your files securely from anywhere
New to Zoho Recruit?
Zoho Developer Community
New to Zoho LandingPage?
Zoho LandingPage Resources
New to Zoho Survey?
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Bigin?
Topic Participants
Maadhari M
Sticky Posts
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 achievedKaizen #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 theKaizen #197: Frequently Asked Questions on GraphQL APIs
🎊 Nearing 200th Kaizen Post – We want to hear from you! 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 #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.Celebrating 200 posts of Kaizen! Share your ideas for the milestone post
Hello Developers, We launched the Kaizen series in 2019 to share helpful content to support your Zoho CRM development journey. Staying true to its spirit—Kaizen Series: Continuous Improvement for Developer Experience—we've shared everything from FAQs
New to Zoho TeamInbox?
Zoho TeamInbox Resources
Zoho CRM Plus Resources
Zoho Books Resources
Zoho Subscriptions Resources
Zoho Projects Resources
Zoho Sprints Resources
Qntrl Resources
Zoho Creator Resources
Zoho CRM Resources
Design. Discuss. Deliver.
Zoho Show Resources
Get Started. Write Away!
Writer is a powerful online word processor, designed for collaborative work.
Zoho CRM コンテンツ
-
オンラインヘルプ
-
Webセミナー
-
機能活用動画
-
よくある質問
-
Ebook
-
-
Zoho Campaigns
- Zoho サービスのWebセミナー
その他のサービス コンテンツ
Nederlandse Hulpbronnen
ご検討中の方
Recent Topics
Payment issue with Mail Lite plan – personal NIF not accepted as payment info
Hello, I have already contacted Zoho Support by email regarding this, but since I haven’t received any reply yet, I’m sharing it here as well to see if the community can help. I’m facing a payment issue for my Mail Lite plan. I have a personal accountCustomer payment alerts in Zoho Cliq
For businesses that depend on cash flow, payment updates are essential for operational decision-making and go beyond simple accounting entries. The sales team needs to be notified when invoices are cleared so that upcoming orders can be released. In contrast,{"code":1038,"message":"JSON is not well formed"}
Today this began failing: sales_order_data = zoho.books.createRecord("salesorders",books_organization_ID,order_data); with this error message. {"code":1038,"message":"JSON is not well formed"} This code has been running for two years. Here is the inputZoho Editor
Zoho PDf Editor is not working I am clicking on EDIT PDf then it again bringing me back to the same page. again and again.Figma in Zoho Creator
Hi Team, I’m creating a form using Figma and would like to know how to add workflows like scheduling, custom validation, and other logic to it. Can anyone help me understand how to set this up for a Figma-based Creator UI form?How to Delete Personal Account Linked with My Mobile Number in past or by someone else
How to Delete Account Created with My Mobile Number in past or by someone else This is creating issues in making or sync with my credentials mobile and email address..Enable / show scroll bar when Mega Menu is opened
Hey there I am using the mega menu add-on and experience a "flicker" whenever the mega menu opens. The reason is, that the scrollbar, which has a width of a few pixels, stops showing when the mega menu opens. As the scrollbar disappears the whole pageRestore lost Invoice!
Some time ago I tried to Upgrade from Invoice to Books. I not upgraded and staid n Invoice. Now i tried again and first i deleted the old trial of books. But now all is gone, PLEASE HELP!! i have no backup and i have to have at least 7 years data retention by law.Zoho Desk Down
Not loadinglookup and integrated forms
I might be misunderstanding things but I wanted to integrate our zoho crm contacts into creator. I imagined that when I used the integration it would mirror into creator. It did brilliant. BUT We have a ticket form in creator that we want to use a lookupPartially receive PO without partial Bill?
Most of our inventory is pre-paid. Let's say we purchase 30 pieces of 3 different items for a total of 90 pieces. It is common for our supplier to send us the items as they are ready. So we will receive 30 pieces at a time. How can I partially receive2 users editing the same record - loose changes
Hello, I'm very new to Zoho so apology if this has been addressed somewhere i can't find. I have noticed the following: If we have 2 users put an inventory item in edit mode at the same time: say user1 click on edit and user2 while user1 is still in edit,How to get the Dashboard page to be the first page when you open the app
So when it opens on a tablet or phone it opens on the welcome page, thanks.How I set default email addresses for Sales Orders and Invoices
I have customers that have different departments that handle Sales Orders and Invoices. How can i set a default email for Sales Orders that's different than the default email for Invoices? Is there a way I can automate this using the Contact Persons DepartmentsAdding hyperlinks in CRM emails time automatically
It may just be me, but when I am writing an email to a lead, I find inserting a hyperlink very time consuming. Granted, I can use templates but there are a ton of scenarios where I might want to put a link in to an website that wouldnt require me to go though the effort of creating a template. Ideally, the crm would identify that I that a string of text is a URL and insert the hyperlink automatically, just like microsoft outlook or gmail. Has anyone else had this same experience and found a wayNotes Attachments
Two things it would be nice to have the attachment size the same as the attachments sections and it would be nice to be able to attach links like you can in the attachments section. Thank youZoho 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.Formula fields not refreshing until page is reloaded
I need help/advice about the formula fields and how I can refresh the information in real-time. We have two formula fields on our deals page which show calculated prices: One formula is in a subform which calculates the subform total + 1 other field amountHow can I setup Zoho MCP with Chat GPT
I can set up custom connections with Chat GPT but I cat an error when I try to set it up. The error is: "This MCP server can't be used by ChatGPT to search information because it doesn't implement our specification: search action not found" Thoughts?Export Invoices to XML file
Namaste! ZOHO suite of Apps is awesome and we as Partner, would like to use and implement the app´s from the Financial suite like ZOHO Invoice, but, in Portugal, we can only use certified Invoice Software and for this reason, we need to develop/customize on top of ZOHO Invoice to create an XML file with specific information and after this, go to the government and certified the software. As soon as we have for example, ZOHO CRM integrated with ZOHO Invoice up and running, our business opportunitiesAPI ZOHO CRM Picket list with wrong values
I am using Zoho API v.8. with python to create records in a custom module named "Veranstaltung" in this custom module I've got a picket list called "Email_Template" with 28 Values. I've added 8 new values yesterday, but if I try to use on of those valuesGroup Emails
I have synced Zoho CRM to Campaigns but there are certain email not synced. showing it is Group Emails, but this email ids belongs to different individuals. please provide a solution as i nedd to sync the same.Enable Password Import option in ulaa browser
Dear Ulaa Team, I noticed that the Ulaa Password Manager currently offers an option to export passwords, but not to import them. This limitation poses a challenge for users like me who have stored numerous credentials in browsers like Chrome. Manually"Is Zoho CRM customer" vs "Is linked with Zoho CRM"
Recently while building a Flow, I was setting up a Decision action following a Zoho Invoice Fetch record action. There were 2 choices that I had not seen as something I could manually action in Zoho Invoice: "Is Zoho CRM customer" and "Is linked withClient Script | Update #13 - Introducing ZRC: Simplified HTTP request library
Hello Developers! Are you tired of juggling different methods to make API calls? Are you confused with multiple syntaxes and version restrictions? Have you ever wished for one simple way to make all API calls in CRM? We heard you :) Here comes ZRC (ZohoSelection Filed for Data Export section
Hi FSM Team, I hope you are all doing well. I would like to share an idea for future development based on my experience. Currently, in FSM, we can only download up to 5,000 records at a time. If the development team could add a selection option to chooseText wrap column headers in reports?
Is it possible to auto wrap column headers so that a longer multi-word header displays as two lines when the column is narrower than the width of the header title?What if I dont see contacts on the left side list
My CRM does not show the contacts tab. In order to create list this is needed and I cant find it.Comments Vs. Replies
I'm curious as to the difference between a "Reply" and a "Comment" on a ticket. It appears that "Replies" are what's used to determine response time SLA's and there are also used to automatically re-open tickets. I'm just trying to understand the key differences so I can educate both our clientele and our back-end users on which function/feature to use to better improve the ticket lifecycle. If anyone has any insight it would be appreciated. Thanks!Transitioning to API Credits in Zoho Desk
At Zoho Desk, we’re always looking for ways to help keep your business operations running smoothly. This includes empowering teams that rely on APIs for essential integrations, functions and extensions. We’ve reimagined how API usage is measured to giveAdd Custom Reports To Dashboard or Home Tab
Hi there, I think it would be great to be able to add our custom reports to the Home Tab or Dashboards. Thanks! ChadResetting auto-number on new year
Hi everyone! We have an auto-number with prefix "D{YYYY}-", it generates numbers like D2025-1, D2025-2, etc... How can we have it auto-reset at the beginning of the next year, so that it goes to D2026-1? Thanks!Microsoft Phone Link
Does anyone know if you can use Microsoft Phone Link to make calls through Zoho?Voip Phone system that integrates with Zoho
Just checking to see if anyone could tell me what phone system they are using with Zoho that is on the list of systems that integrate with Zoho. I use Vonage and have been with them for quite a few years but their service has really gone down hill andRemoving Related Modules Lookup Fields Assignment / Relationship
Issue: When creating a related list, I accidently selected module itself creating a circle reference. See attached. Situation: I wish to relating a custom module called "Phone Calls" to Leads and Contacts. Outcome: 1) I either want to remove the this[Product Update] TimeSheets module is now renamed as Time Logs in Zoho Projects.
Dear Zoho Analytics customers, As part of the ongoing enhancements in Zoho Projects, the Timesheets module has been renamed to Time Logs. However, the module name will continue to be displayed as Timesheets in Zoho Analytics until the relevant APIs areKaizen #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 theCan you prevent closing Ulaa window when the last tab is closed (inadvertently)?
Most browsers have started to bring this feature in to prevent closing their windows when the last tab is closed (inadvertently). I hope Ulaa should get this in too.Seriously - Create multiple contacts for leads, (With Company as lead) Zoho CRM
In Zoho CRM, considering a comapny as a lead, you need us to allow addition of more than one contact. Currently the Lead Section is missing "Add contact" feature which is available in "Accounts". When you know that a particular lead can have multiplecan I link a contacts to multiple accounts
can I link a contacts to multiple accountsNext Page