Automating Blog Summarisation with Google Apps Script, ChatGPT, and Open AI API

Aryan Irani
8 min readMay 17, 2023

Welcome!

Are you someone who loves reading blogs but finds it difficult to make time for them? Or, are you a blogger looking to make your content more accessible to readers? You’re in luck! I discovered an incredible combination of tools that can help automate the summarisation of blog posts. In this blog, I will show you how to use ChatGPT and Google Apps Script to generate summaries for your favourite blogs.

I started working on this project because I found myself spending too much time reading blogs and trying to distill the most important information from them. I wanted a quick way to determine if a post was worth reading in full, without sacrificing the quality of the content. That’s where Google Apps Script and ChatGPT come in! By using these tools together, we can now generate concise and informative summaries that make reading blogs a breeze.

So whether you’re a blogger looking to improve the accessibility of your content or a reader looking to save time while still staying informed, this tutorial is for you! Let’s dive in and explore the amazing capabilities of ChatGPT and Google Apps Script.

Sample Google Sheet

For this blog I will be using a very simple Google Sheet that contains the links for the blogs and the Summary column where the summary for each of the blogs will be pasted.

If you want to work with the Google Sheet, click here. Once you make a copy of the Google Sheet you have to go ahead and change the API key in the Google Apps Script code.

If you want a video version of this blog, you can check out the YouTube tutorial below.

Step1: Get API keys

To obtain an OpenAI API key, start by logging into your OpenAI account on their website. Once you’re logged in, navigate to the API Keys tab on your Dashboard page.

Click on the Create new Secret Key button. Finally, click on the “Generate API Key” button to generate your new key, and then copy it to your clipboard. Be sure to store it in a secure place as it will not be visible again.

With your API key in hand, you can now use it to authenticate your requests to the OpenAI API and integrate ChatGPT with Google Sheets. Simply add the key to your Google Apps Script code and use the appropriate functions to make requests to the API. You’re now ready to start using the power of AI to enhance your spreadsheets!

Step2: Write the Automation Script

While you are in the Google Sheet, let’s open up the Script Editor to write some Google Apps Script. To open the Script Editor, follow these steps:

  1. Click on Extensions and open the Script Editor.

2. This brings up the Script Editor as shown below.

We have reached the script editor let’s code.

Now that we have the Google Sheet setup and the API key ready, let’s go ahead and write our Google Apps Script to get the summary for the blogs into Google Sheets.

//Replace with your API key
var API_key = "your_api_key"

We are going to start by declaring the API key that we just generated. You have to replace this with the API key that you generated.

function BlogSummary(){

var Model_ID = "text-davinci-002";
var maxtokens = 200;
var temperature = 0.7;

Here I have started by declaring a new function called BlogSummary() inside which we begin by declaring the model that we are going to be using. To know more about the models the Open AI API has check out the link below.

After declaring the model, we declare the number of tokens to generate in the response for the blog link that we pass. After declaring the max tokens, we move towards declaring the temperature to control the randomness of the response provided based on the prompt. To understand these properties better, check out the link below.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");
var urls = sheet.getRange("A2:A6").getValues();
Logger.log(urls)

After declaring some parameters we move towards getting the links from the sheet, which we will be passing into the prompt. To do that we begin by getting spreadsheet using getActiveSpreadsheet() function. After getting the spreadsheet we go ahead and get the specific sheet by using the getSheetByName() function.

Currently, we are focusing on summarising only five blogs, so we have set a limit within the getRange() function. Once we have obtained the specified range, we will use the getValues() function to retrieve the links to the blogs. After getting the values, we do a Logger.log() to check if we have got the links perfectly.

Here you can see that we have successfully got all the links from the Google Sheet.

  for (var i = 0;i<urls.length;i++){

var url = urls[i][0]
Logger.log(url);

Now that we have got all the links for the blog in one variable, its time to get the individual links in order to pass them as a prompt. To do that we open a for loop inside which we iterate through the urls variable. To check if we have got the links correctly, we run a simple Logger.log and print the individual url.

Here you can see we have successfully printed all the links individually.

  var payload = {

'model': Model_ID,
'prompt': 'Please generate a 50 word summary for the following blog post:\n' +url,
'temperature': temperature,
'max_tokens': maxtokens,
"presence_penalty": 0.5,
"frequency_penalty": 0.5

}

This part of the code sets the parameters for the Open AI API request. We start out by declaring the desired model that we declared previously. After declaring the model, we go ahead and declare the prompt that we are going to use. In the prompt we add the url for each of the blog.

Once we declare the prompt, we declare the temperature to control the randomness of the response based on the prompt. After this we declare the number of tokens to generate the response.

var options = {

"method": "post",
"headers": {

"Content-Type": "application/json",
"Authorization" : "Bearer " + API_key
},
"payload": JSON.stringify(payload)
};

The options variable is used to define the parameters for the HTTP request that will be sent to the Open AI API. We start out by declaring the method parameter to post which indicates that request will be sending data to the API.

The headers specifies the type of data that will be sent in the request, which in this case in “application/json” which is a commonly used format for sending structured data over the internet. Additionally, the “Authorization” header is set to include the API key we generated earlier.

Finally, the “payload” parameter is used to specify data will be sent in the request. In this case it is a JSON object that contains the Model ID, prompt, temperature and the max tokens for the API request.

  var response = UrlFetchApp.fetch("https://api.openai.com/v1/completions",options);

These options are now passed as an argument to the “UrlFetchApp.fetch” function which sends the request to the Open AI endpoint, and returns the response that contains the API generated text.

In this case the API endpoint is https://api.openai.com/v1/completions" which is the endpoint for the ChatGPT API provided by OpenAI.

The options variable that we defined is passed as an argument to the fetch function to provide additionally parameters for the requests. The fetch functions then sends the request to the API endpoint and waits for a response from the server. The response from the server will contain the AI generated text that will be stored in the response variable.

  var summary = JSON.parse(response.getContentText());
var final_summary = summary.choices[0].text.trim();

Now that we have sent a request to the Open AI endpoint we get a response back. We are now going to parse this response.

The getContentText() function is used to extract the text content from the response object. Since the response is in JSON format, we use the JSON.parse function to convert the JSON string into an object.

The parsed data is then stored in the final_summary variable, which contains an array of choices that t he AI model has generated for us based on the prompt.

Since we are only interested in the first choice of the array, we use summary.choices[0] to access that choice. We then use the .text to extract the text from the choice and finally use .trim function to remove any leading or trailing whitespace from the generated text.

    sheet.getRange(i+2,2).setValue(final_summary);

Now that we have the summary ready and good to go, it’s time to set the summaries next to each of the links in the Google Sheet. We start of by getting the range using the getRange() function followed by setting the summary generated using the setValue() function.

Our code is complete and good to go.

Step3: Check the output

Its time check the output and see if the code is working according to what we expected. To do that go ahead and save your code and run the BlogSummary function.

On running the code you should get an output like this in the Execution Log.

On running the code, when you go back to the Google Sheet you will see that the summaries of each of the blogs is pasted next to each of the blog links.

Here you can see that all the summaries for the blogs have been pasted in the Google Sheet successfully.

Conclusion

In conclusion, leveraging Google Apps Script and the Open AI API enables effortless generation of blog summaries. The integration automates the process by extracting summaries for each blog from a Google Sheet and pasting them back into the Google Sheet. This efficient solution saves time and enhances content creation, empowering bloggers to deliver valuable information more effectively. You can get the code from the GitHub link given below.

Feel free to reach out if you have any issues/feedback at aryanirani123@gmail.com.

--

--