By @mhawksey

GenAI for Google Workspace: Exploring the PaLM 2 API and LLM capabilities in Google Sheets with Google Apps Script — Part 1

Imagen: An photo image which has a laptop with a spreadsheet application which appears to have rays of light

I’m always on the lookout for tools and technologies that might be the next big thing and in particular what their impact could be on Google Workspace. Like many others, one area that has caught my attention is Generative AI (GenAI). GenAI is the ability of machine learning models, such as Large Language Models (LLMs), which can use vast amounts of data to recognise, predict, and generate new content such as text. In this post, I’ll show how you can start experimenting with LLMs in Google Workspace using Google Apps Script. For this first part I’ll show how you can use the PaLM 2 API to get responses from prompts made in Google MakerSuite with Google Apps Script. In the next part we’ll explore some of the capabilities of when using the PaLM 2 API with data from Google Sheets.

Google has been developing and using these approaches for many years, arguably going back to 2015 and we get to see glimpses of this in features like smart compose in Gmail and Docs. The seismic shift we’ve witnessed over the last year or so is a move away from a heavily crafted user experience, towards a more raw interface with LLMs through natural language prompts. You can see evidence of this in the Duet AI for Google Workspace, where there is a mixture of carefully engineered prompts hidden under the actions of buttons like ‘Summarise’, ‘Shorten’ and ‘Rephrase’, but also allowing the user to ask/prompt anything they like:

A Duet AI for Google Workspace prompt in Google Docs

The Power of the Prompt

Having foundation models such as LLMs has created a new skill ‘prompt design’. Prompt design, or prompt engineering, is a mixture of art and science, with a growing number of cheat sheets for different LLMs, which can help improve the responses you get from various models. Below is a slide I created for training with Duet AI for Google Workspace, which includes a link to the Learning Prompting website.

The Power of the Prompt

If you are interested in reading more about the growing importance of prompt engineering, Google have recently published a post on Prompt engineering and you: How to prepare for the work of the future.

Experimenting with Prompt Engineering in MakerSuite

Google has a number of LLMs that are generally available to developers, including foundation models trained for text, chat and code which are accessible as part of the PaLM 2 API. To help developers explore the capabilities of these models, Google has created the MakerSuite site:

MakerSuite is a fast, easy way to start building generative AI apps. It provides an efficient UI for prompting some of Google’s latest models and easily translates prompts into production-ready code you can integrate into your applications.

MakerSuite has just gone generally available for a number of countries and to coincide with this announcement the Google Developers Blog has started a series on ‘Make with MakerSuite’.

Using MakerSuite with Google Apps Script

As mentioned in the introduction I’m interested in the emergent capabilities of Generative AI, in particular LLMs, and the opportunities associated with integrating in the Google Workspace platform. Having got early access to MakerSuite <cough vpn/> to test and feedback on the platform, one of the first things I wanted to do was make a Google Apps Script helper class, GenerativeLanguageApp, to make it easier to test prompts designed in MakerSuite.

One of the nice features of the MakerSuite prompt design process is to click a button to generate a code snippet you can use in your own dev environment. To make the iterative process easier I’ve coded GenerativeLanguageApp so that once you’ve created/saved an API key, you can drop these code snippets into your Google Apps Script project:

Setting up GenerativeLanguageApp

  1. Create a new Google Apps Script project (
  2. Add a new script file named GenerativeLanguage and copy/paste GenerativeLanguage.js
  3. Add a new script file named ENV and copy/paste the ENV.js
  4. In the 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

Using GenerativeLanguageApp with MakerSuite

  1. In MakerSuite create a new prompt (or use one of the sample prompts)
  2. Click ‘< > Get code’ and click on the JavaScript tab and copy the code
  3. In your Google Apps Script project open and paste the code into myFunction()
  4. Remove or comment out the lines which have the require calls
  5. Replace "YOUR API KEY" with process.env.API_KEY

Save the script and Run > myFunction.

If everything has worked you should have a response recorded in the Execution log similar to the one below:

Example execution log

You can continue to experiment with prompt text and setting in the code in the Apps Script editor.


This post has covered how you can quickly copy MakerSuite code examples to run them in Google Apps Script. In the next part we’ll explore some of the capabilities of when using the PaLM 2 API with data from Google Sheets.

Exit mobile version