Let Bard Answer Your Questions from Google Sheets with the PaLM API and Google Apps Script

Welcome!

In this blog we will be taking a look at how we can integrate Google Bard inside of your Google Sheets using the PaLM API and Google Apps Script. We will be using a custom function to call the PaLM API and pass a prompt and get back a response.

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

This blog comes under the course named, Getting started with PaLM 2 that talks about Google Bard, MakerSuite, PaLM API and more. Check out the playlist given below and related blogs.

Getting started with PaLM 2 — Introduction

Extend the capabilities of PaLM API with Google Apps Script

Sample Google Sheet

For this blog I will be using a very simple Google Sheet that contains a prompt and a response column. Inside the prompt column I will pass a prompt and the response column will contain the response generated from Bard using the PaLM API and Google Apps Script.

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.

Step1: Get the API key

Currently, PaLM API hasn’t been released for public use but to access it before everybody does, you can apply for the waitlist by clicking here. If you want to know more about the process of applying for MakerSuite and PaLM API, you can check the YouTube tutorial below.

Once you have access, to get the API key, we have to go to MakerSuite and go to the Get API key section. To get the API key, follow these steps:

1. Go to MakerSuite or click here.
2. On opening the MakerSuite you will see something like this

3. To get the API key go ahead and click on Get API key on the left side of the page.

4. On clicking Get API key, you will see something like this where you can create your API key.

5. To create the API key go ahead and click on Create API key in new project.

On clicking Create API Key, in a few seconds you will be able to copy the API key.

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 lets code.

Now that we have the Google Sheet and the API key ready, lets go ahead and write the Google Apps Script to integrate the custom function inside the Google Sheet.

function BARD(prompt) {
  var apiKey = "your_api_key";
  var apiUrl = "https://generativelanguage.googleapis.com/v1beta2/models/text-bison-001:generateText";

We start out by opening a new function BARD() inside which we will declare the API key that we just copied. After declaring the API key we go ahead and declare the API endpoint that is provided in the PaLM API documentation. You can check out the documentation by checking out the link given below.

We are going to be receiving the prompt from the Google Sheet from the BARD function that we just created.

Generative Language API | PaLM API | Generative AI for Developers

var url = apiUrl + "?key=" + apiKey;
var headers = {
   "Content-Type": "application/json"
};

var requestBody = {
   "prompt": {
   "text": prompt
   }
};

Here we create a new variable called url inside which we combine hte API URL and the API key, resulting in a complete URL that includes the API key as a parameter. The headers specify the type of data that will be sent in the request which in this case is “application/json”.

We now come to the most important part of the code that is declaring the prompt. For this blog the prompt will be directly received from the Google Sheet, that will be stored inside the prompt variable.

Now that we have the prompt ready, we create an object that will contain this prompt that will be sent in the request to the API.

  var options = {
    "method": "POST",
    "headers": headers,
    "payload": JSON.stringify(requestBody)
  };

Now that we have everything ready, its time to define the parameters for the HTTP request that will be sent to the PaLM API endpoint. We start out by declaring the method parameter which is set to POST which indicates that the request will be sending data to the API.

The headers parameter contains the header object that we declared a while back. Finally, the payload parameter is used to specify the data that will be sent in the request.

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

var response = UrlFetchApp.fetch(url, options);
var data = JSON.parse(response.getContentText());
var output = data.candidates[0].output;
return output;

In this case we just have to pass the url and options variable inside the UrlFetchApp.fetch function. Now that we have sent a request to the PaLM API endpoint we get a response back. In order to get an exact response we are going to be parsing the data.

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 the passed to the final variable output, inside which we get the first response out of multiple other drafts that Bard generates for us. On getting the first response, we return the output back to the Google Sheet.

Our code is complete and good to go.

Step3: Check the output

Its time to 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 BARD() function.

On running the code, lets go back to the Google Sheet and use the custom function and pass the prompt inside it.

We start out by opening up the custom function and passing the prompt inside the custom function.

On successful execution you can see that the custom function has worked perfectly. It has given you a perfect response inside of your Google Sheet.

Here you can see it has generated responses for all the prompts we passed.

Conclusion

This is just another interesting example of how we can Integrate Google Bard into Google Workspace tools using Google Apps Script and the PaLM API. I hope you have understood how to use the PaLM API and Google Apps Script to create a custom function inside your Google Sheet to integrate bard. You can get the code from the GitHub link given below.

Google-Apps-Script/PaLM.js at master · aryanirani123/Google-Apps-Script

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