Plug Sample #5 - Read and write values/data in a Google sheet with Zobot

We all know google sheets are one of the widely used spreadsheet software. It helps us to make data-driven decisions. When connecting the Zobot with sheets, the bot can display values/data from a spreadsheet, like the available stock details (Item price, quantity). Create a new record, such as feedback, comments or even update the existing fields based on your requirement. In this post, we will focus on using plugs to achieve the below google sheets actions with REST API.

Fetching records from Google sheets
Creating records in Google sheets

As we're aware, plugs in the Codeless bot builder are used to create custom actions or third-party integrations using deluge. You can create a plug and add it as a block in the bot builder, like rest blocks. Now let's learn how to create this plug step by step.

Step 1 - Creating a connection with Google service.

In your SalesIQ Dashboard, navigate to Settings > Developers > Plugs > Click on Add.
Provide your plug a name, and description, select the Platform as SalesIQ Scripts, and finally, click on Connection to your left bottom. You will be redirected to the connection interface.

Click on Create connection at the top right corner. Under Default connection, select Google service.

Provide your connection name, connection link name, and choose the scopes below.

Click on Create And Connect. Upon successful authentication, Zoho SalesIQ will be connected with the Google service.

Note: The Connection Link Name is used to scripts to invoke URL tasks.

Step 2 - Create the plug using Connection:

First let's look at fetching records from Google sheets. Any data that is available in sheets can be fetched. For example, if you're running an e-commerce business and maintaining all the stock information such as item name, quantity, price etc. on sheet, those details can be fetched here and displayed to the visitor.

Copy, paste the below code and changes the sheet ID, name, row range, and connection name.

//Add a output parameter named "allproducts" and select "Option list" as datatype
google_spreadsheet_id = "1y92rI_ITWLxpXOQMtyO8W9_7P6HwygxfneeVxdoRd-E";
sheet_name = "Fetching+records";
//specify the correct range like "a1:b11"
url = "https://sheets.googleapis.com/v4/spreadsheets/" + google_spreadsheet_id + "/values/" + sheet_name + "!a2:b11?majorDimension=ROWS";
//googlesheets - Connection link name made using the connection interface (Step 1)
apiresponse = invokeurl
[
url :url
type :GET
connection:"googlesheets"
];
values = apiresponse.get("values");
productList = List();
//Adding all the product and ID in an option list | Ref - https://www.zoho.com/salesiq/help/developer-section/plugs-datatype-optionlist.html
for each entry in values
{
id = entry.get("0");
product = entry.get("1");
productList.add({"id":id,"text":product});
info productList;
}
response = Map();
//parasing the plug output
// allproducts - Name of output parameter; productList - Value to be associated to "allproducts"
response.put("allproducts",productList);
return response;

Then, click save, preview the plug and publish it. For your reference, we have made the sheet public.

Note: API invoked in the plug spreadsheets.values.get API.

The above reference shows the spreadsheet ID and the sheet name.

Now, let's look at creating records in Google sheets. Any type of data can be pushed to sheets, such as feedback, comments, etc.

Copy, paste the below code and change the sheet ID, name, and connection name.

//Add two input parameters named "visitorID","feedback" and select the datatype as "String"
if(session.containsKey("visitorID"))
{
id = session.get("visitorID").get("value");
}
if(session.containsKey("feedback"))
{
feedback = session.get("feedback").get("value");
}
google_spreadsheet_id = "1y92rI_ITWLxpXOQMtyO8W9_7P6HwygxfneeVxdoRd-E";
sheet_name = "Create+records";
//specify the correct range like "A3:B3"
url = "https://sheets.googleapis.com/v4/spreadsheets/" + google_spreadsheet_id + "/values/" + sheet_name + "!A3:B3:append?valueInputOption=USER_ENTERED";
params = {"majorDimension":"ROWS","values":{{id,feedback}}};
apiresponse = invokeurl
[
url :url
type :post
parameters:params.toString()
headers:{"Content-Type":"application/json"}
connection:"googlesheets"
];
info apiresponse;
response = Map();
return response;

Then, click Save, preview the plug and Publish it. For your reference, we have made the sheet public.

Note: API invoked in the plug spreadsheets.values.append

Step 3 - Adding the plug to the Codeless bot builder

Navigate to Settings > Bot > Add, provide the necessary information, and select Codeless Bot Builder as a bot platform or open an existing bot.
Select the Plugs under Action Block and select the required plug (Only published plugs will be listed here)
Provide the plug inputs and outputs.
Click Save.

In the above video, we've used a plug, fetched some products and displayed them as options to the visitor.

Heads up:

For all the plug inputs and outputs, use either dynamic texts or the values saved in the bot context/visitor fields from previous blocks in the bot's flow.
General Data Protection Regulation (GDPR) by European Union (EU) terms and conditions applies.

Related links:

To know more about the features of Zobot, kindly visit our Resources Section.

Cheers,

Sasidar Thandapani

New to Zoho LandingPage?

Access Zoho LandingPage

Zoho LandingPage Resources

New to Zoho Survey?

Access Zoho Survey

Latest Feature Updates

Explore our Pricing & Plans

Zoho Survey Resources

Android Mobile Surveys

Insurvey Blogs

Insight Blogs

Tips & Tricks

New to Zoho CRM Plus?

Deliver unforgettable customer experiences

ACCESS ZOHO CRM PLUS

New to Zoho CRM Plus?

Deliver unforgettable customer experiences

ACCESS ZOHO CRM PLUS

New to Zoho Marketing Plus?

Everything you need to run your marketing

ACCESS ZOHO MARKETING PLUS

New to Zoho Marketing Plus?

Everything you need to run your marketing

ACCESS ZOHO MARKETING PLUS

New to Bigin?

Signup Now

Access Bigin

Latest Feature Updates

Zoho Desk Resources

Desk Community Learning Series
Digest
Functions
Meetups
Kbase
Resources
Glossary
Desk Marketplace
MVP Corner
Word of the Day

Tout forum

France ZUGs

Zoho SalesIQ Resources

Topic Participants
Sasidar Thandapani
admin

Sticky Posts
OpenAI Alert - Plug Sample #6: Reach maximum potential with SalesIQ Zobot and ChatGPT Integration
In recent times, AI-powered tools have seen a remarkable surge in usage due to their exceptional capacity to enhance overall performance across diverse industries and sectors. One such tool is ChatGPT which businesses have started to use predominantly
Plug Sample #5 - Read and write values/data in a Google sheet with Zobot
We all know google sheets are one of the widely used spreadsheet software. It helps us to make data-driven decisions. When connecting the Zobot with sheets, the bot can display values/data from a spreadsheet, like the available stock details (Item price,
Speak Your Customers' Language: SalesIQ's chatbots now support 30 languages 🤖
We're unveiling some major upgrades to our chatbot that are set to revolutionize your experience! Now SalesIQ support 30 languages for both Zobot and Answer bot. By speaking your customers' language, you can enhance engagement, improve customer satisfaction,
Attention Deluge Developers: Important Update Regarding "Send Email Deluge Task"
Hi Deluge Users, We hope this message finds you well. We would like to inform you about a recent update on the Send emails deluge task. If you are using this deluge task (SalesIQ Scripts) for your Zobot, widgets, or form controllers to send emails, please
Hide Zoho SalesIQ chat widget outside operating regions to optimize engagements
Have you ever found yourself in a situation where you're receiving chat requests from locations where your business isn't operational? Having to set routing rules or manually delete chats from your logs can be tedious and time-consuming, hampering your