This is the second part in my series exploring the GenAI capabilities of the PaLM 2 API in Google Sheets. In the first part you can read how to set up Google MakerSuite to use Large Language Models (LLMs) with Google Apps Script. In this post I’ll show you how the GenerativeLanguageApp helper class introduced in part 1 can be used to explore the capabilities of GenAI with data in Google Sheets.
The capabilities of LLMs are still emergent, having only relatively recently been offered ‘as a service’ by Google and other providers. There are various use cases for LLMs but broadly they can be used to recognise, predict and generate human language. In the context of spreadsheets, in particular, Google Sheets there is an opportunity to explore how LLMs can help you with your data.
Google already includes intelligent features such as Smart Fill, which “detects patterns between columns and suggests data for remaining cells using Google’s Knowledge Graph”. As part of Duet AI for Workspace Google recently announced Enhanced Smart Fill:
Enhanced Smart Fill can recognize and fill patterns like: sentiment classification, combining and generating text, extracting specific text, formatting addresses, structuring phone numbers and emails, condensing text and more.”
Google has clearly invested time and engineering to integrate the Enhanced Smart Fill feature. If you are interested in experimenting with the capabilities of Google’s LLMs with Google Sheet data here are some examples which use the GenerativeLanguageApp Apps Script helper introduced in part 1. The examples include:
- Extracting star ratings from reviews
- Extracting ingredients from recipe descriptions
- Generating marketing blurb from product descriptions
- Conditionally returning data from a Google Sheet
Taking the star rating example you can see it is possible with a suitable prompt to get a star rating similar to the Enhanced Smart Fill feature in Duet AI for Workspace. As the underlying PaLM 2 API being used to generate this response is based on a consumption model based on character input and output. For example, one of the star prompts and responses costs approximately $0.000185. Here is more information about Pricing for Generative AI on Vertex AI | Google Cloud.
Running your own prompt experiments
To run your own experiments you will need to set up the bound Apps Script project with an API key generated in MakerSuite. To do this:
- Copy of PaLM Prompt Engineer — Demo
- In the copied Google Sheet click Extensions > Apps Script
- In the editor Project Settings scroll to Script Properties and Add a script property named API_KEY with an API key value generated in MakerSuite and click Save script properties
To generate text responses the PROMPTS tab requires a prompt in column A and at least a model name, temperature (randomness) and candidate_count (number of outputs). To generate text responses click PaLM Pilot > Run prompts and any rows without output_0 will have a response populated.
Constructing prompts in Google Sheets
Exploring the shared Google Sheet it’s hopefully possible to work out how the prompts are designed by combining data in other tabs using Sheet formula. One exception is the examples using {{}} notation. Prompts with this markup will merge data from another tab named with the placeholder text e.g. {{MailingList}} returns a 2D array of the ‘MailingList’ tab.
Under the hood the Apps Script isn’t doing much other than pushing values into GenerativeLanguage Class. For the sheet replacement there is the following function that gets the sheet values and returns a JSON string:
/**
* Adds sheet data to a prompt using token replacement
* @param {String} prompt that includes a sheet name token
* @return {String} prompt with merged data
*/
const addSheetData_ = (prompt) => {
const doc = SpreadsheetApp.getActiveSpreadsheet();
// token replacement
const new_prompt = prompt.replace(/{{[^{}]+}}/g, sheetName => {
const data = doc.getSheetByName(sheetName.replace(/[{}]+/g, "")).getDataRange().getValues();
return JSON.stringify(data)
});
return new_prompt
}
Summary
Hopefully this post gives you a starting point for exploring the PaLM 2 API capabilities, particularly when working with data in Google Sheets. As the examples demonstrate it is possible to construct prompts which are able to be interpreted by LLMs and data incorporated in the response. It also hopefully shows that functionality similar to Enhanced Smart Fill can be created for specific use cases in Google Workspace using Google Cloud solutions for a very different price point and value proposition. If you are interested in putting some of the ideas presented in this post into production please get in touch.
