This post describes how I designed and ran an audience survey with over 1,700 responses, using Google Forms, Sheets, Apps Script, and ChatGPT. I’ll show you the entire process from end-to-end, including how I:
Created a survey with Google Forms
Used Apps Script to automatically say thank you to 1,700 respondents
Analyzed the response data in Google Sheets
Used AI to help me understand the qualitative data
Presented the results in Google Docs
It’s rather fitting that the 1,000th Pulse post features content by the one and only Ben Collins! Back in late 2019, when I was thinking about creating a new community site for Google Workspace developers, Ben’s encouragement was the spark that ignited AppsScriptPulse.
And today’s post by Ben is a nice example of Apps Script’s power to automate repetitive tasks. As part of this he shows how to craft personalised “thank you” emails for Google Form survey response with Google Apps Script. Ben’s insights go beyond ‘thank-you’s as he outlines how he administers and analyses customer surveys, highlighting his design choices for Google Forms and data analysis using built-in Google Sheets functions.
To take things a step further, Ben also highlights how he used ChatGPT to categorize qualitative survey responses. With Google’s recent announcement of their new AI model, Gemini, which outperforms ChatGPT in a number of academic benchmarks, it would be interesting to see how these two platforms compare for this type of analysis.
Raising a glass (or an espresso :) to Ben and this 1,000-post milestone!
Imagen: An photo image which has a laptop with a spreadsheet application which appears to have rays of light
This is the second part exploring the GenAI capabilities in Google Sheets. In this part learn how you can make an Enhanced Smart Fill for Google Sheets
Google recently announced the latest feature for Duet AI for Google Workspace with Enhanced Smart Fill, which uses GenAI in Google Sheets to generate content based on data and the patterns entered by the user.
Continuing a previous post exploring the PaLM 2 API and LLM capabilities in Google Sheets, this post looks provides a Google Sheet template for experimenting with LLM prompts and spreadsheet data, including how you could make a ‘Enhanced Smart Fill’-like star review generator.
The post includes everything you need to get started, with you only having to make your own MakerSuite API key.
Imagen: photo looking over the shoulder of a robot looking at a screen with chat messages and hand writing notes
This tutorial shows how to make a Google Chat app that responds to incidents in real time. When responding to an incident, the app creates and populates a Chat space, facilitates incident resolution with messages, slash commands, and dialogs, and uses AI to summarize the incident response in a Google Docs document.
Paraphrasing noted Google Workspace Developer Expert, Romain Vialard, GenAI has made Google Chat apps a tangible prospect. This tutorial from the Google Developers site is a great example of how you can use Google’s Vertex AI with Google Chat. The tutorial will help you create a Google Apps Script powered Chat app that is able to summaries the messages in a Google Chat space.
There is a lot to take away from this example, but here are some of the headlines:
Setting a Google Cloud Project to use the new Google Chat Advanced Service for Apps Script
Setup and code for making calls to Google’s Vertex AI PaLM API (LLM) from Google Apps Script
Using the responses from Vertex AI to generate new assets.
There is a lot more you can do from this starting point, but hopefully it gives you a great starting point.
Building a Support AI Assistant for Google Workspace using Apps Script, Gen AI, and Google Chat.
This post from Stéphane Giron highlights one approach for improving responses from LLMs by integrating Google Custom Search Engine responses into the prompt. In this example Stéphane used Google Apps Script to power the AI Assistant, integrating with Google Chat for the user interface and Cloud Functions to reformat data.
This post is another example of the ‘power of the prompt’ and how LLM prompting strategies are a very effective way to utilise LLMs without having to ground or fine tune. If you are interested in understanding more here is a useful notebook produced by Michael W. Sherman which illustrates two powerful LLM prompting strategies: Chain of Thought and ReAct (Reasoning + Acting).
This video shows how to use the Palm API with Google Apps Script to extract data from Google Analytics 4 accounts. This can be useful for a variety of purposes, such as creating custom reports, automating data analysis, and building new data-driven applications.
Following on from the last post in Pulse where we looked at using Google PaLM API and MakerSuite in Google Apps Script, here’s another example from GDE Linda Lawton. As the video in the post shows Linda has been able to engineer prompts that allow you to use natural language to extract reports from Google Analytics. This shows the emergent capabilities of LLMs as well as some clever prompt engineering. The source post contains more detail, but here is an example:
var text = "The current date is '"+ date + "'. Create a JSON object which contains five parameter's dimension, metrics, start_date, end_date and property_id. The dimension and metric parameter's will be comma separated strings they can be empty if there is no valid text for it. The value of the dimension parameter should be a comma separated string of these dimensions names 'country, eventName, city, audienceName' and the value of the metric parameter should be a comma separated string of these metric names 'activeUsers, eventCount, screenPageViews', the property_id field will also be a string it will be a large number, start date and end date must be in the following format YYYY-MM-DD, which can be found in the given this text '" + prompt + "'. If no start date is found use set it to seven days ago and if no end date is found set it to today."
A couple of highlights worth noting:
Context – The current date is included programmatically to give the LLM a reference point
Reinforcement – ‘start date and end date must be in the following format YYYY-MM-DD’
Exceptions – ‘If no start date is found use set it to seven days ago and if no end date is found set it to today’
Discover the magic of combining Palm API’s extraordinary capabilities with the limitless potential of Google Apps Script. In this blog we will be taking a look at how we use the PaLM API and Google Apps Script inside of a Google Sheet. We will be passing prompts from a Google Sheet and getting back a response.
Learn how to integrate Google Bard responses inside of Google Sheets using the PaLM API and a little bit of Google Apps Script. Using Google’s MakerSuite it is easy to create an API key which you can use with a custom function in Google Sheets. Whilst the solution focuses on creating a custom function which would automatically refresh, using it programmatically to store responses could be a quick way to collaboratively experimenting with LLM text prompts.
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.
When we announced MakerSuite earlier this year, we were delighted to see people from all over the world sign up for the waitlist. With MakerSuite we want to help anyone become an AI maker and easily create innovative AI applications with Google’s large generative models. We’re excited to see how it’s being used.
Today, we’re expanding access to MakerSuite to cover 179 countries and territories, including anyone with a Google Workspace account. This means that more developers than ever can sign up to create AI applications with our latest language model, PaLM 2.
We’ve recently featured a couple of posts on Pulse mostly from Aryan Irani on getting started Google GenAI tools in Google Apps Script. As part of these Google MakerSuite, a tool that lets developers start prototyping with Google’s large language models quickly and easily, is used as part of the API calls to PaLM. MakerSuite is still in private preview, but the good news in the linked announcement that the waitlist has been expanded to 179 countries. Given how Google have rolled out other GenAI tools, in particular Bard, I’m not surprised that EU countries are still not included, but find it strange at time of writing the United Kingdom is still not on the list. Despite this the announcement is worth a read to find about some other new features including automatic text prompt tips and data import/export to Google Sheets and by CSV.
Want to write better prompts? Now, you can write a text prompt and click “Prompt Suggestion” to get ideas and suggestions to get better responses – Image credit: Google
Last month, OpenAI announced Function calling, “a new way to more reliably connect GPT’s capabilities with external tools and APIs”. Super useful ❤️ and we decided to integrate that with Google Apps Script.
Following on from yesterday’s Pulse post which highlighted Ben Collins reflections on ‘What can AI do for you as a Google Sheets user? Is the hype justified?’, here is a contribution from another Apps Script expert, Romain Vialard, announcing a ChatGPT library for Google Apps Script.
The source post highlights a number of examples, including the standard ‘prompt’ calling. Where this post gets very interesting is integration with Open AI’s function calling features:
In an API call, you can describe functions to gpt-3.5-turbo-0613 and gpt-4-0613, and have the model intelligently choose to output a JSON object containing arguments to call those functions. The Chat Completions API does not call the function; instead, the model generates JSON that you can use to call the function in your code.
To illustrate this below is a screenshot of a very slightly extended example, which has a Google Sheet with the headings email, name and tip topic. The functions are described to ChatGPT and in the case of sendMessage() the parameters it requires. The ChatGPTApp library handles the functions and in the case of the getContactsList() uses it to pass the Google Sheet data for ChatGPT to format a JSON response to correctly call the sendMessage() function.
I found it took me a while to start understanding what is going on and would recommend experimenting with the library and sample code to get a sense what is possible.
See how AI tools work with Google Sheets to boost your productivity. Covers ChatGPT, Google Bard, and AI add-ons.
A very informative post from Ben Collins, discussing how AI can be used to automate tasks, identify patterns, and make predictions in the context of Google Sheets. Ben provides several examples of how Generative AI can be used to improve the way you can interact with data in spreadsheets from helping with formula to generating and improving data.
As Ben points out in the post users have benefited from AI in Google Sheets for a number of years with features like Explore. Perhaps the biggest recent change is exposing Large Language Models as a service, with users able to directly access the ‘prompt’ to generate output.
Discover the magic of combining Palm API’s extraordinary capabilities with the limitless potential of Google Apps Script. In this blog we will be taking a look at how we use the PaLM API and Google Apps Script. We will be passing basic prompts and getting the appropriate responses using Google Apps Script.
Yesterday’s Pulse contribution from Aryan Irani there was an overview on getting started with Google’s Gen AI PaLM 2 Large Language Model accessed using MakerSuite. In a follow-up post Aryan continues dives deeper into how the PaLM API can be called from Google Apps Script. The post includes instructions and sample code to help you get started. Aryan will be continuing exploring and sharing what can be done with the PaLM API and Google Workspace tools such as Google Sheets, Google Docs and more in future tutorials.
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.
