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 Tools 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

  • 16 May 2023
  • 7 replies
  • 145 views
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
This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

7 replies

Harsh2
Userlevel 1
Badge +1

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
Userlevel 1
Badge +1

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

Soham Agarwal

Hi Alex!

 

Could you let me know how you made the API request work. Ive been trying the “API Request (beta)” for days but it sais “raw request failed”. Any idea?

AlexDLM
Userlevel 1

Hi Soham,

 

My advice is to first of all look at the documentation zapier gives us about APIs. It truly helped me better understand what I was trying to do. https://zapier.com/resources/guides/apis

 

Afterwards it really depends on what you’re trying to achieve. Based on that you should look at the documentation google gives us about each different action you want to take, and it even gives you a box to 1. Learn how to build the code you need 2. See in real time if it’s working on your sheet 3. How to fill each field.

 

It takes a couple hours to get a good understanding, but currently it’s the only way to achieve something like I wanted to achieve. You can find some links to the documentation in the messages above, hope this helps!

 

Alex

 

 

Soham Agarwal

Hey!

Could you send a screenshot of the api request (beta) section that worked for you? Thanks for the other advice as well! 

Terms & Conditions