Use ChatGPT in Google Apps Script

GPT you hear about it everywhere you see it everywhere luckily charging PT comes with a rest API that is publicly available which means that we can use it in Google apps script so today we're going to see how to push prompts into chat GPT and get summaries back from it the idea here is that we are going to build a solution for real estate agents let's pretend that our real estate agents when they are touring properties are taking notes in a form and the form submits their notes to a Google sheet just like we have here so they will go in and they will visit a property in a certain address and take some notes about the lot the structure bedrooms bathrooms so on and so forth and they even have a place for other notes that they could take and we want at some point to create real estate summaries for the properties so that they can post those summaries um online and so the idea is essentially to create a script that has a function that when we run the function it will go through all the lines in the Google sheet and for every new line it's going to insert a link to a Google doc and the Google Doc will contain a summary that chart GPT wrote based on the notes that the real estate agent took so for example here we're seeing notes about a large lot in a cul-de-sac privacy fans so on and so forth so if we look at the summary that chat GPT wrote I'll zoom in here we can see that chat GPT wrote us a nice paragraph This stunning property is the perfect fit for a busy professional loves to entertain located on a large lot in a quiet cul-de-sac well-structured paragraph that the real estate agent can then use so let's see how to do this in Google apps script the first thing you want to do is create an account with open AI and once you have that account created click on your profile icon and select view API keys you reach the API Keys page and here you click on create new secret key this is the key that you will generate you should not share it with any third party or anyone that you don't trust you have to keep it secret so you want to click the copy button to copy this and then store it somewhere safe in your file system then you click ok and you're ready to go here's my setup I have a Google sheet with one tab which I named agent notes and the tab contains several columns the First Column is summary this is where we're going to store links to Google Docs that will contain the summaries that chat GPT will generate for us have a date and time I have an address column which I'm going to use to name the files so I know which file is which and then I have a bunch of attributes about the property so I have notes about the lot the structure bedroom bathroom kitchen garage and other notes I want to essentially take everything from column D and Beyond and pass that to chatgpt so that it can generate a summary out of this so let's see how to do that the first thing we want to do is store the API key that we copied in our script properties so that we can refer to it in our script so click on the gear icon inside Google apps script IDE a settings and then under script properties click add script property and here you can name the key whatever you want I'm going to name it open AI key and the value will be the actual API key that you received once you paste the value just click save properties okay so the first thing I'm going to do is create a global object that I'm going to use throughout the script and the first thing I want to store in it is the ID of the folder where I want to move my Google Docs into so I have a Google drive folder and I'm just going to copy its ID from the end of the URL and store it in my code and now we can create a function to call GPD we need to get the API key from our script properties so I'll create a script props using Property Service get script properties and in the G object I'm going to set up an API key using my script props get property and I'm going to use the name that I defined for this property we can add a quick check see if in case we didn't get the API key or we forgot to set it we'll throw an error here [Music] next we need to define the features that we want to include in our prompt for chat GPT have text already for that this is just to test the function we'll use the information in our Google Sheet later but for now I'll just going to come up with my own features so I have stunning four bedroom Ranch on a corner lot new kitchen and roof perfect for a busy family lots of sunshine that will be my test features next we need the API URL which you can get from the API reference so if you go to platform.openai.com API Dash reference you can get all that information we are going to use completions and so the URL is specified here you can just copy it here instead of typing it and that's our URL now we need to define a few options that we need to pass in our fetch request is the method we're going to use post because we're going to incorporate our features in the payload of the request next is the headers we need to pass an authorization using Bearer and here we're going to reference our API key we also need to specify content type which is application Json I'm going to mute HTTP exceptions so that we can handle any errors that we get from the server and now we need to specify the payload that we want to pass to chatgpt we need to stringify it essentially it's an object with four properties first is the prompt so my prompt is write a real estate property overview using the following property features and here I'm going to embed my features next we need to specify what language model we want to use and again if you look in the documentation you will see that there are a bunch of models to use in our case we're going to use the DaVinci so we can copy the model from the documentation let's model colon text Dash DaVinci Dash 003 next is the temperature which is a value between 0 and 2. and again you can see it in a documentation basically it tells judge GPT how how creative I guess to be so I'll go with one and you can play with this and see what kind of responses you get and which ones work best for you and the last property is the max tokens essentially how many tokens to return maximum and I specify money as 150 tokens now we can make the request I'll Define the response and use URL fetch app with fetch and I'll pass the API URL and our options and we can get the content out of the response by using get contact content text let's log content see what we get a run called chargpt I need to authorize the script it's going to make a call to external service so here's our response we're getting a string that includes an object with an ID it has a text completion created chose the model that we used and here are the responses they come in in an array with a choices properly and inside the array there is a series of objects in our case just one with a text property and the actual response from chatgpt so we are seeing here if you're looking for the perfect home for a busy family this stunning four bedroom Ranch is just a property for you and so on and so forth so I'm really liking the response that I got from chat GPT so this looks great so now we can essentially create the summaries using the data that is available in our Google sheet instead of statically passing the features so we can delete the features in our function here and then get it from the function call and now we can Define our function create summaries that will iterate through the Google sheet take each row that doesn't have a summary already and pass that to the call chart GPT the first thing I want to do is move the fetching of the API key out of the culture GPT and put it inside the create summaries just because I need to do it only once I don't want to do it every time I make a call to judge Beauty so it better sit in the create summaries function so first I need to get the data out of the sheet I'll use get active to get the active spreadsheet and then get cheat by name agent notes I'll get the data out of the sheet and I'll remove the header row just because I don't need it so I'll splice the first element in the data array now we need to iterate over every Row in the array so I'll use for each and I'll get the row and its index I want to check that a summary hasn't already been created so I'm going to inspect column A and look in each cell and if the cell is empty that will be an indication that a summary hasn't been created otherwise we're going to put the link to the Google Doc in this cell so I'll use if not row 0 I want to get the name out of the address and that's what I'll use for the file name foreign column D onward so we can slice the row starting at the third position all the way to the end and I'm going to join these array elements into a string using spaces we can log the features and see what we're getting you can also log the name so I'll run create summaries and I'm saying the name is the address and then the information from my Google sheet whatever appears here I'm essentially creating a string out of it so this looks good so now I can pass the features to charge EPT I'll Define a text variable and I'll pass features to the called chat GPT and we can log the text so before we do that here we need to return it we haven't returned it yet let's first of all check that we get a good response from the server you can do that by looking at response.getresponse code and string it and check if it starts with two that will be an indication of a good response and if it's not starting with 2 that means that there was a problem on the server so we can log the response of content in this case and return null so nothing will be done with the information next we're going to parse the string and remember how the charge EPT brought back the text in a attribute called choices so I want to make sure that choices exist so if I don't have choices or the length of choices is zero I'll log Json and return null foreign otherwise we'll go into Json choices we're going to get the first member of the array and we're going to pull out the text attribute so when we're calling charge GPT with the features we want the text inside the first element of the choices array so if I run create summaries now I'm getting The Prompt for the first row this impressive property offers everything you need and the second one this delightful two-bedroom tunnels okay things look good here so I'm getting the text back as I expected it and now I need to store it in a Google doc so I'll create a new dock and I'll pass the name as the name of the file I'll get the body and I'll Pan the text as a parallel foreign and now I need to get the URL of the file so I'll get the file first by using get ID on the dock I want to move the file to my folder so I'll use file move to and here I can get I'll use get folder by the get the URL of the file and store it using my I index in the right place in the Google sheet so I'll use sh get range and the range is column A and I want to use I Plus 2. because the row numbers in Google sheet starts with one and we have another one for the header so we're actually starting at two and here we're going to set formula equal sign hyperlink in quotes file URL and here we'll say view summary and once we're done with the rows I'm just going to flush my changes in the spreadsheet like this so we're iterating over every row getting the row and its index or pulling out the name using the address column column C we're slicing the features out of the remaining columns and joining them with spaces we're calling chart GPT with the features getting back the text then we create a document get its body append a paragraph save and close the document get the file and then we use it to move the file to our folder get the URL of the file and assign it in our hyperlink formula to the cell let's give this a run I need to authorize again okay so now that we're looking in our Google sheet we're seeing links to two files if we check our folder and refresh it we see both files appearing here if we click on the link we get access to the summary that chat GPT was gracious enough to write for us so hopefully this gives you an idea how to tie Google apps script with chatgpt where you can take data that you have in a Google sheet formulated into a prompt pass it to charge EPT get back the response and store it in Google Docs I hope this helps and I'll see you in the next video

Channel: Ben Ronkin

Views: 9,843

Keywords: google workspace automation, google apps script, chatgpt, google sheets, google docs

Id: QNRk_0Djq18

Length: 24min 52sec (1492 seconds)

Published: Wed Mar 01 2023