Let’s see whether Google’s AI Studio can teach me how to build a pivot table in Google Sheets. It’s wild how fast this technology is progressing and this gives us a glimpse into the near future when we’ll all have personal AI assistants helping us work more efficiently.
In a recent YouTube video, Ben Collins, a prominent figure in the Google Sheets community, explored the capabilities of Google’s AI Studio by using it to guide him through the process of creating a pivot table. This experiment provides a compelling glimpse into the future of how we might interact with software, suggesting that AI could soon enable conversational, real-time interactions that go beyond the traditional user-driven model.
Ben began by logging into Google AI Studio and initiating a real-time screen share of his Google Sheet, which contained real estate data. He then engaged the AI assistant, powered by Gemini, to help him create a pivot table to analyze this data. The initial request was straightforward: to see the sum of sales prices broken down by property type. The AI assistant demonstrated an understanding of this request and provided step-by-step instructions. The AI correctly instructed Ben to start by selecting any cell within his data and then navigating to the Insert menu to select “Pivot table”.
The AI assistant did stumble initially, incorrectly stating that the pivot table option could be found under the Data menu. This highlights an important point: while impressive, AI assistants are not yet infallible. As Ben pointed out in the video, that could be a stumbling block if someone didn’t know to look under the Insert menu. Ben’s familiarity with Google Sheets allowed him to identify and correct the AI’s misstep, and continue with the tutorial. This shows that even with sophisticated AI tools, a foundational understanding of the software is still essential.
Once the pivot table was created, the AI guided Ben through adding “property type” to the rows and “sales price” to the values section. It also prompted Ben to ensure the summarization of sales price was set to “sum” instead of “count” or another option. This highlights the AI’s ability to understand the nuances of data analysis in Google Sheets and guide users to the correct settings. This is a key insight, because the AI isn’t just providing instructions but it is also understanding the data context.
Ben’s experiment provides a vision of a future where AI agents become sophisticated collaborators within Google Sheets. These agents would not only provide step-by-step instructions, but could also actively carry out tasks, such as reformatting tables or creating charts and graphs based on conversational prompts. Imagine, for example, saying “reformat this table to be more visually appealing” or “create a chart showing sales trends over time” and having the AI make those changes automatically. This would move beyond current user workflows which depend on menu clicks, or even hand-written Apps Scripts, and would allow users to focus on high-level goals and analysis, rather than the mechanics of the software.
It is clear from the video that Google AI is an important area to watch for the future of Google Workspace. However, even with AI integration, it is still important to understand the tools you are using to ensure the advice you are receiving is correct. This is an important point, as it shows that AI should be seen as a helpful assistant, not as a replacement for user understanding. If you are interested in reading more about this I recently published an article onĀ Empowering Enterprise Productivity While Preserving Critical Thinking.
Join this community of over 53,600 data analysts and Google Sheets aficionados and get an actionable Google Sheets tip every Monday
With a weekly subscription of over 50K we probably don’t need to flag a ‘Monday morning espresso’ of Google Sheets tips from Ben Collins as you are probably already subscribed. However, given Ben recently published the 300th issue it’s a milestone worth celebrating and a chance to say ‘thank you’ to Ben for this invaluable publication.
As eloquently demonstrated on a recent episode of Totally Unscripted, Ben is the Google Sheets “King of Functions”. Ben’s expertise isn’t limited to solving Google Sheet challenges, he’s also a master at explaining his thinking and helping other people learn. If you haven’t subscribed to Ben’s newsletter follow the source link (it’s free!) and check out Ben’s website for loads of great tips and resources.
Learn how to use Smart Chips in Google Sheets to take full control of your data. Smart chips bring extra information to your Sheets.
Unleash the hidden power of Google Sheets with Smart Chips! Here’s a nice primer for our next episode of Totally Unscripted from Google Sheets magician, Ben Collins. You’ll have to tune in to the show to get the developer angle on Smart Chips, this post instead focusing on out-of-the-box features of Smart Chips in Google Sheets. Ben’s post is still incredible useful and in particular it was very interesting to read about data extraction from Smart Chips including the dot syntax when using Google Sheets formula/functions.
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!
Today we’ll see how to link a chart title to a cell, so that the chart title automatically reflects whatever value is in the cell
Today’s Pulse snippet comes courtesy of Ben Collins’ excellent Google Sheets Tips Newsletter, Tip 272. It uses a very basic onEdit() trigger to update a chart title based on a cell value. Ben has a great way of highlighting solutions without getting readers lost in complexity. Hopefully this example highlights the how easily you can modify Google Sheet charts using Apps Script.
If you are an Apps Script novice and looking for an easy way to learn what else is possible my own tip is to start the macro recorder, modify an embedded Google Sheets chart and then look at the resulting macro code in the Script Editor.
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.
Get the lowdown on the 11 new analytical functions introduced to Google Sheets in 2023, including LET and array manipulation functions.
For developers Google Sheets is becoming an increasingly rich playground. Features like the SQL like QUERY function have enabled users to do more with their data. More recently Google have released a range of new functions which I’m sure many developers and power users will be able to relate to. New functions like EPOCHTODATE, LET as well as other recent additions like MAP and REDUCE will be familiar concepts to many developers. In this post from Ben Collins the latest Google Sheet functions are reviewed.
In August 2022, Google announced named functions and 9 other new functions in Google Sheets. Named Functions let you save and name your own custom formulas, built with regular Sheets functions, and then re-use them in other Google Sheet files. Itās a HUGE step toward making formulas reusable. Learn about them all here!
Ben Collins is incredibly talented at communicating technical information in a non-technical way. This post is a great example where Ben covers the latest Google announcement on 10 new Google Sheets functions. The two standout ones are Named Functions which lets you make your own reusable function of functions, and something I’m sure many MS Excel users would appreciate, XLOOKUP.
Learn how to track link clicks using Google Sheets and Apps Script to create a simple, lightweight tracking system
You live and learn! This is a regular occurrence if you are a subscriber to the work of Ben Collins (benlcollins.com). No exception with this recent post highlighting the ping attribute which can be used in <a> HTML links. This attribute has been around for a long time, but I’m sure many people like have never come across it. In the source post from Ben you can learn how to setup an Apps Script web app which will let you record link clicks in a Google Sheet.
A note of caution is whilst ping is valid HTML not all browsers choose to use it or enable it by default, something Mozilla have decided to do in Firefox. You can head over to Mozilla mdn documentation for the browser compatibility table and here is also an interesting post on how Google tracks with the ping-attribute.
See four different API Authentication methods presented in Apps Script, including authentication in query string, headers, and OAuth2.
I got fed up digging around in my Drive folder for old scripts to refresh my memory on the syntax, so I created this reference.
Itās not a comprehensive post on how to connect to APIs, instead, itās a short summary of common protocols for easy reference.(If youāre new to APIs, start with my Apps Script API tutorial for beginners.)
We are currently spoilt for choice with Google Apps Script community contributions. This is a great post from Ben Collins for Google Apps Script beginners highlighting different patterns used to interact with third party websites with APIs.
An API is essentially an interface that can be used by a computer programme to retrieve or interact with another application.
