




















































Dive deeper into the world of AI innovation and stay ahead of the AI curve! Subscribe to our AI_Distilled newsletter for the latest insights. Don't miss out – sign up today!
In this blog, we will be taking a look at building a language converter inside of a Google Sheet using Google Bard. We are going to achieve this using the PaLM API and Google Apps Script.
We are going to use a custom function inside which we pass the origin language of the sentence, followed by the target language and the sentence you want to convert. In return, you will get the converted sentence using Google Bard.
For this blog, I will be using a very simple Google Sheet that contains the following columns:
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.
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:
3. To get the API key go ahead and click on Get API key on the left side of the page.
4. On clicking the 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 the new project.
On clicking Create API Key, in a few seconds, you will be able to copy the API key.
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(sentence,origin_language,target_lanugage) {
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"
};
Here we create a new variable called url inside which we combine the 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”.
var prompt = {
'text': "Convert this sentence"+ sentence + "from"+origin_language + "to"+target_lanugage
}
var requestBody = {
"prompt": prompt
}
Now we come to the most important part of the code which is declaring the prompt. For this blog, we will be designing the prompt in such a way that we get back only the converted sentence. This prompt will accept the variables from the Google Sheet and in return will give the converted sentence.
Now that we have the prompt ready, we go ahead and 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, it's 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;
Logger.log(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 then 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.
It's 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, let's go back to the Google Sheet, use the custom function, and pass the prompt inside it.
Here I have passed the original sentence, followed by the Origin Language and the Target Language.
On successful execution, we can see that Google Bard has successfully converted the sentences using the PaLM API and Google Apps Script.
This is just another interesting example of how we can Integrate Google Bard into Google Workspace 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 that acts as a Language converter. You can get the code from the GitHub link below.
Google-Apps-Script/Bard_Lang.js at master · aryanirani123/Google-Apps-Script
Collection of Google Apps Script Automation scripts written and compiled by Aryan Irani. …github.com
Feel free to reach out if you have any issues/feedback at aryanirani123@gmail.com.
Aryan Irani is a Google Developer Expert for Google Workspace. He is a writer and content creator who has been working in the Google Workspace domain for three years. He has extensive experience in the area, having published 100 technical articles on Google Apps Script, Google Workspace Tools, and Google APIs.