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 :’).
Best answer by Harsh2View original
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.
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.
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
After which you could loop over them to retrieve the appropriate sheet.
Thank you very much for pointing me in the right direction, through API I managed to fix my problem!