About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Apps Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

Best answer

Google Sheet API Request New Row For Each Message Chatbot

AlexDLM
Userlevel 1

Hi everyone,

 

I’ve been trying to create a chatbot through Zapier, and it’s been quite the adventure to say the least.

 

Messages come from and to gohighlevel. The Zap(s) I’m creating are triggered when a new message comes through from a contact. What happens then is a new row in Google Sheets gets created for the message in column A. Afterwards the rows get output as a line item to be more easily read by OpenAI.

 

OpenAI reads the conversation stored in the google sheet and responds to the new message based on the context it just read.

 

All good here.

 

Here’s where the issue comes. This all happens in one sheet, every message from every contact gets stored in one single sheet, and obviously it becomes messy, OpenAI won’t understand anything anymore, and neither will I. What would be optimal is if a message comes from a new contact it creates a new google sheet and works from there, if not it should refer to a specific spreadsheet. 

 

If I put a create new spreadsheet action, every time a message comes through a new sheet gets created, and each sheet only contains one message, which is pointless.

 

If I don’t every message from every contact gets stored in one sheet.

 

What would be great is if the google sheet app had a way to identify a google sheet through a custom field from a different app. Unfortunately I have asked for support, and the action is yet to exist, but it probably will in the future.

 

But I can’t wait for that, so they suggested that I try finding a fix with the API requests. First of all I’d like to know if any of you have done something like this. I’ve read the API documentation, but I’m not sure it’s doable. What I’m afraid is: how does the sheet get identified? Through an ID of course, and I can work with that making it a custom field. But again, will an ID in the form of a custom field from a different app work?

 

If you read this whole novel thanks for your time, and I hope someone can give me some kind of guidance :’).

 

Sincerely,

Alex

icon

Best answer by Harsh2 17 May 2023, 17:38

View original

4 replies

Harsh2

Hi @AlexDLM,

Could you please let me know as to why the "Find Or Create Worksheet" action (under GoogleSheets) is not doing the job for you?


Ideally you could then search for a Worksheet by title (In your case the title would be the contact) and simply keep appending each contact's data to their own respective sheet. 

AlexDLM
Userlevel 1

Hey @Harsh2 ,

The reason “Find Or Create Worksheet” isn’t working for me is because to find a worksheet (or a sheet) you need to use the ID or its title, and to do it “dynamically”, meaning I don’t put in the ID manually each time, I need to use a custom field from a previous action.

 

The webhook action catches the message, and with it the ID of the contact. A separate Zap creates a new sheet (or I can set it up as a worksheet, same concept) and it names it with the ID of the contact. In the Zap where I’m having issues to find that sheet I simply need to look for (“find”) that sheet by looking for that contact ID. It would work perfectly if only the google sheet app allowed to use a custom field from a different action to find that. For that matter I actually tried using the formatter, so the custom field from the webhook simply becomes a series of letters and number, but it doesn’t work that way either. The custom field needs to be from a google sheet action.

 

If you use the “Find file” in the google drive app, and to do that you use a custom field from a different app, it will work anyways. Unfortunately it is not the same for google sheet.

 

 

Harsh2

Hi @AlexDLM 

 

In that case you would need to use the GoogleSheets API directly via the "API Request"

 

I would need to think on this some more, but hopefully the following helps you get started.

 

You would need to use the following GoogleSheets API endpoint to retrieve all the sheets

 

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get

 

After which you could loop over them to retrieve the appropriate sheet.

AlexDLM
Userlevel 1

Thank you very much for pointing me in the right direction, through API I managed to fix my problem!

 

Cheers!

 

Alex

Reply


Terms & Conditions