Hello I am looking for a little help getting my bot to write form slots to a google sheet. First let me caveat that I am not a developer and I have minimal experience with python. Anyway I have created a bot with rasa 2.0 and I copied some code from the rasa demo bot ‘Sara’ on git hub. I am trying to recreate having users sign up for a newsletter by giving their name and email address.
I have a simple form that gets activated using rule policy and the slots are being filled, but I am stuck getting that info passed on to the sheet. I assume somewhere there needs to be credentials and an api key called but I have no idea where to add these. Perhaps in the gdrive_service.py? I know in the rasa for beginners udemy course there was and .env file with the credentials for Air Table but there does not seem to be an equivalent .env for the Sara bot, and that was using rasa 1.1 verses Sara that is 2.0.
The bot does respond as expected but nothing of course at the moment gets sent to google drive. I have created an api key for google sheets* in the google developers console but I am not sure where to implement or go from here. (*Or does this need to be a google drive api ??)
The only error I get is in the terminal running rasa actions:
UserWarning: Cannot validate email: make sure the validation function returns the correct output.
warnings.warn(
If anyone can point me in the right direction I would be grateful. Thanks.
Hi @jbh !
There is a Google sheets api that might help you, with some instructions for getting started with Python here. I think you’re best off writing a function that writes to the Google sheet. There is an example of how to do this with Python here. Then, you can call this function in an action similar to ActionSubmitSubscribeNewsletterForm(Action) in Sara (here). How does that sound?
@fkoerner Thanks so much for responding to my questions. I investigated your suggestions and found it helpful but it also created more questions for me. Since my python skills are lacking I was hoping to reuse and modify existing pieces of Sara and not write new functions.
With that in mind I am tweaking the actions.py and gdrive_service.py file in the actions/api/ folder of the rasa-demo. From what I can tell the gdrive_service.py is essentially the function called from the actions.py file and seems to be accessing the config.py file where the gdrive_credentials are and where the API key goes (?) Yes my backend integration skills are lacking too.
I am not sure if for my case an ‘account_sid’ and ‘auth_token’ are required but the error regarding the ‘validation function’ seems to be coming from my poor attempt to modify the FormValidationAction. I have since just removed it to simplify things and the error goes away, but still nothing gets written to my sheet.
One of the links you forwarded mentioned the need for a credentials.json file to access the api. Is this the piece I am missing? I did not see one in the Sara demo bot folders? I know it would be specific to my credentials but for some reason I thought the config.py might be the substitute.
I know I have a lot of holes in my knowledge but any additional insight would be greatly appreciated!
You’re right, in Sara gdrive_servive.py is accessing the credentials from config.py. However, you may have missed that config.py pulls these from the environment variable in this line: os.environ.get("GDRIVE_CREDENTIALS", ""). You need to set these environment values on your system.
The error regarding the validation function looks to be unrelated to me. Does it still occur if you leave in the original version from Sara?
You’re not seeing the credentials.json file because this file is made on the fly and based on the information pulled from config.py.
You also need to make sure you’ve changed the sheet names here
Thanks @fkoerner for your insight. So I conceptually understand the process, my problem now is putting together the pieces. I actually did change the sheet names in the gdrive_service.py, but my attempt at setting the environment variables correctly is not working for me. Or at least that appears to be my current problem.
I’ve tried the approach suggested in the “Rasa for Beginners” course and created a .env file with my google sheet API key (number) and tried calling it in the actions.py file, but still nothing gets written to the sheet and I do not receive any errors helping me trouble shoot further.
My problem might be on the google cloud side, I have not created additional credentials beyond the API key, (Do I need OAuth client IDs for this task?) Or it might be a simple syntax issue.
As mentioned I am not a developer by training and I am trying to get up to speed on basic python, but obviously there is more to this conversational AI puzzle than just that. I will continue to poke at this and truly appreciate all your suggestions.
Oh and I just removed the validation function for now as that is a lower priority at the moment and the error has gone away.
First off, let’s make sure you have the right credentials. You shouldn’t need an OAuth client ID as this has been deprecated AFAIK. But maybe you need to give the service email access to your sheet? The explanation here is quite thorough and walks you through the process. I would recommend you try it out, including producing and running the simple code file in step 8. If this works, you can then integrate it into your actions.py. That way you can be sure your authorization worked, and if something goes wrong during integration you’ll at least know the credentials are correct.
@fkoerner So the good news is I was able to create the authentication using a service account email and run the simple code as suggested and confirmed that it connected. The bad news is I am still unable to get the slot info from the bot written to the sheet. I have tried a few variations of editing the action.py, the gdrive_service.py and config.py (all originally from Sara bot) but nothing gets written to the sheet and no actual error is offered to help debug.
You had mentioned before that the credentials.json file gets created on the fly but how does the service_account.json that I created get used? Do I need to point to it with the environment variables? What’s the next thing should try? Thanks
@jbh that’s great, glad to hear it! If I were you I wouldn’t bother with rewriting the credentials.json file the way Sara does (at least, for now since you’re developing and running this locally). You should be able to replace:
with tempfile.NamedTemporaryFile(suffix="_credentials.json", mode="w") as f:
f.write(gdrive_credentials_json)
f.flush()
self.credentials = ServiceAccountCredentials.from_json_keyfile_name(
f.name, scopes=scopes
)
@fkoerner It worked!!! Thanks so much for helping me get through it!
I know I want to get my bot up on Rasa X in the near future and there by not running just locally, is there another way to secure the ‘credentials.json’ file or is the way Sara does it, on the fly, ultimately the way to handle it? If so where should I go to help wrap my head around the process or options?
@jbh that’s great! You’re almost there, you should be able to adjust your code to do it the way Sara does it (although you should make sure to back up your current version so you can go back if needed ).
You can read more about our recommended deployment methods here.
Depending on which deployment method you choose, you’ll need to set the environment variable GDRIVE_CREDENTIALS with the same json you’ve already set up (looks something like:
(this example pulled from the tutorial I linked earlier)
You can test this out locally:
Revert back to use the Sara code, so back to:
with tempfile.NamedTemporaryFile(suffix="_credentials.json", mode="w") as f:
f.write(gdrive_credentials_json)
f.flush()
self.credentials = ServiceAccountCredentials.from_json_keyfile_name(
f.name, scopes=scopes
)
Set the environment variable GDRIVE_CREDENTIALS on your system (how to do this depends on your OS, but Google should help with this, just search environment variables "name of your OS").
(optional sanity check) Set a breakpoint in the code above and see if the temporary file looks like the service_account.json you’ve successfully used and created.
If this works, deploying should be easy: the only difference is that the environment variable needs to be set differently depending on which deployment method you choose.
@fkoerner Yes I think it makes sense. It will probably take me a little bit of time before I get to this point, but if I get stuck I will reach out. Thanks again!
).