Use ChatGPT in Google Apps Script
Вставка
- Опубліковано 5 вер 2024
- Learn how to pull data out of Google Sheet, convert it into a prompt, send the prompt to the ChatGPT API, get a response back, and store the response in a Google Doc - all while using Google Apps Script.
Blog post: benronkin.com/...
Nice! One of the best AppsScript demos. Nice job!
Thanks! 😃
Hello, why you use app script ? not just an add on ?
Thank you so much! This was so valuable.
Mind blowing. Thankyou
I appreciate the feedback; thank you!
When you mean removing header and data splice at 14.49, is that the "Summary"? Trying to understand since my file has like 5 rows I do not need, so not sure if you removed first column, or first row or both?
No, it's the first row that contains the headers, which we don't need to send to ChatGPT. So in your case you'll do .splice(0,6).
Subscribed, value added content 💯
Thank you. I really appreciate it.
Have you ever gotten this error?
error: {
"message": "Incorrect API key provided: undefined.
I have triple-checked that my API key is correct and I even created new keys to test it, yet I continue getting this error message.
Could you produce a tutorial video demonstrating how to automate the creation of PDF salary slips for employees using Google Sheets and Apps Script? I lack coding skills, so could the script be designed to instruct ChatGPT to generate the slips? Additionally, it would be beneficial if employees could conveniently access their monthly salary online. Thank you 🙏😊
Can you create a program like calling openai inside google data studio (data looker) and analyse the search console traffic for seo purposes? I think we will need appscript for that but I don’t understand how I will send data of search console from google data looker to openai to get response
You could use the example of my google apps script to save the openai responses in a google sheet, and then you can import the sheet into data studio for analysis.
@@benronkin I got your point but how I will send search console data to the API so I will get desired response from it. I want to send data from google data studio (looker)
@@indianguy6683 I see your point. Give me an example of a ChatGPT prompt that will include search console data.
@@benronkin ok I have done some research on it. Search console provides data in json format so the prompt will be like - I am sharing google search console data with you and need to analyse it for seo purposes and provide me best suggestions in easy to understand way. {{ paste GSC data in json format here }}
I have tested this prompt in chatGPT it works. Someone can write more customised prompt.
@@indianguy6683 Ok. I'll investigate and create a video tutorial on this topic. I hope to publish it tomorrow on the following day.
How do you push it o to the AAppsScript IDE? Have you got some kind of keyboard shortcuts for clasp?
You can use npx clasp push -w which will automatically push to the IDE any time you save your file changes.
Great work!!!👍
Please how do I implement access control to the app script and keep some users from accessing the script details.
Thanks
You can share the Apps Script file with specific people, just like you can do with Google Docs, Sheets, etc.
This is amazing and just what I am looking for. Is there access to the code where I can enter my own credentials to make the magic happen on my end?
You can copy the code from my blog onto your own Google Workspace environment: benronkin.com/blog/use-chatgpt-api-with-google-apps-script.html
@@benronkin Ive got the code in the editor, API key in the correct place, folder ID in the correct place...and it isnt returning anything. Do I have to have the colum heading match the "features"? I have differnt data than you show here.
@@user-tu4jb6wx2c When you log the API response what do you see?
great work , really mazing
I'm happy that you found it useful. Let me know what other tutorials you'd like to see.
Hey, thanks for the class, loved the way you approached it, great didatics!
I did the whole process but, when I hit run, instead of receiving the text generated in the console.log I get an Error of invalid API Key, even though I double checked the API key and the property details and it is accurate. Note that the error I'm getting is not the one we've thrown on the code, which would be for a 'missing API key', but I guess it's something related to the scriptProps, any ideas on what should I try?
Is the Error coming from the OpenAI server? If so then you probably are not using the token they give you, or you are not passing it correctly in the Authorization header. Can you check?
@@benronkin it went well, in the end the problem was a missing semicolon lol
How do I turn this into an add on for the extension marketplace
It's a quite involved process. I'll add that tutorial idea to my list. Thanks.
Could chatgpt write most of the appscript code?
In its current form, I don't believe so. I tried to write a few code snippets with it, and there were a lot of issues. For one, it suffers from "hallucinations", where it "invents" methods that don't exist on certain objects, or it ignores specific instructions. Another issue is that to develop large code bases, you would need to give very specific and very detailed instructions, like pseudo-code, which limits the benefits of the AI.
I think that ChatGPT is good for doing preliminary research on a programming domain that you don't have a lot of experience in. It can offer a starting point, and then you need to use your dev experience to make the code functional and robust. Just my two cents.
I would love to see a video on how to create and use a form that submits to a google sheet. Anyone?
Do you mean something like this: ua-cam.com/video/XZ1Utqu-ivU/v-deo.html
Ben - Great Stuff. I implemented all of it and it was working really well. Then I encountered an issue of the App Starting and Completing in a second without it delivering an output. I can't figure out why it's not performing now when it was yesterday. Changed API and went over it. Any tips? Love the videos and for a nub like me so helpful.
Thank you. Have you tried to run it in Debug mode, going line-by-line and seeing what you're getting?
@@benronkin Thanks Ben - yes have done this. It still not executing consistently though
@@davidnorris8456 Check the response from OpenAI. Maybe it's an API quota issue.
Boa noite amigo, coloque a tradução👍
Thanks