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.
Quickly append new permissions to existing files within Google Drive. Insert email addresses using a comma-space format with optional notifications.
Quickly append new file permissions via a Google Sheet
The following Google Apps Script tool allows you to quickly append new permissions to existing files within Google/Shared drive. This is quite a niche tool so it is anticipated you would already have a Google Sheet of file IDs/URLs, though there is the option to select an existing Drive folder of files and automatically bring them into this tool.
By inserting email addresses using a comma-and-space format you can optionally select to send a notification email to the new user(s)
You can continue to append further rows to the Google Sheet as existing ones will be skipped once the ‘Status’ column has automatically been marked as ‘Completed’. Alternatively you can clear the Sheet each time for a fresh start.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Enhance your Google Sheets skills with this Visual Vocabulary template. Learn how to choose the right charts, create effective visualizations, and design professional-looking dashboards. This comprehensive guide includes 31 common chart types, tips for data organization, and step-by-step instructions for using the SUBTOTAL formula to create interactive “Show/Hide” rows. Perfect for data enthusiasts and spreadsheet users of all levels!
Friend and fellow GDE, Ben Collins, has recently announced a new ‘Sheets Insiders’ membership program where he’ll be sharing some exclusive members-only newsletters, new templates, deep-dive tutorials, and more for both Google Sheets and Apps Script.
If you would like to ‘try before you buy’ Ben has shared the first issue of the Sheets Insiders membership program, featuring a Visual Vocabulary template for Google Sheets. The template is designed to help you in chart selection and the accompanying videos include lots of great tips to help you cleanly format your spreadsheets.
Future Sheets Insiders issues will delve into chart tricks, dropdown menus, interactive elements, AI and Sheets integration, formula challenges, and Apps Script content. The newsletter content will be archived in the Sheets Insiders Content Library for future reference. Click through to read more about Sheets Insiders and get your copy of the Google Sheets Visual Vocabulary Template.
Workspace admins with the AppSheet service privilege now have visibility into the activities of their users, apps and licenses via the new AppSheet Admin Console
The Google AppSheet team recently announced the public preview of a new AppSheet Admin Console. The new Console gives Workspaces Admins greater visibility of AppSheet usage within their domain, including AppSheet Core users. The new Console is a big step forward in providing admins a centralized platform to monitor and manage AppSheet usage, licensing, and governance.
Key features include:
Insights into popular apps and top creators
Detailed app ownership and usage data
Comprehensive view of app users across the organization
Easy access to app sharing settings, performance, audit history, and version history
Exportable lists of accounts, users, apps, and licenses
User role management within the organization
Verification of AppSheet licenses (purchased, assigned, and used)
Access to usage monitoring data, even without an enterprise license
The linked announcement page also highlights some planned new features for the new Console include: historical app usage, individual app copy or transfer, self-serve provisioning, external user licensing, and a default organization model for new Workspace customers who purchase AppSheet Enterprise.
A key addition to the new Console is the AppSheet licensing summary. There are some important considerations Admins should start thinking about around AppSheet licenses, in particular, the creation or sharing of AppSheet apps with Enterprise features to unlicenced or Core users.
Previously under the legacy Team licensing model it was possible for an AppSheet Core user to create and share apps with AppSheet Enterprise features. As Google moves to an assigned model Admins will need to reconcile the required licenses for their domain. The good news is Google has said
“In the short team, no users will be blocked. Admins will be given several months advanced notice before stricter license enforcement.”
I recently explored the transformative power of Generative AI (GenAI) and how it’s reshaping the business landscape in a thought leadership piece for my employer, Appsbroker | CTS. Drawing from my experience in Gemini for Workspace pilots and custom GenAI solutions using the Gemini API in Vertex AI, I highlighted how AppSheet, a no/low-code platform, can be a game-changer.
AppSheet simplifies the integration of GenAI capabilities, enabling rapid prototyping and tailored solutions that deliver real-world results, whilst still benefiting from integrations into Google Workspace. I believe it’s a cost-effective and impactful way to harness GenAI’s potential without blowing the budget. In the article, I share some examples and the benefits of using AppSheet for GenAI innovation.
Curious to learn more? Join me at the Google Workspace Developer Summit in Berlin on September 17th, where I’ll be discussing AppSheet integrations, Gemini Function Calling, and more.
This comprehensive guide walks you through connecting AppSheet to Google Cloud SQL, step-by-step. Unlock the power of a scalable and secure database to build powerful, data-driven mobile applications. Learn everything you need to know, from setting up Google Cloud SQL server, setting up the MySQL database as the datasource for AppSheet Apps, the connection to leveraging Cloud SQL for seamless data management in your AppSheet projects. If you are an AppSheet app creator or developer, this tutorial will likely be the best practice for you during the process of working with Google Cloud SQL
Google AppSheet GDE, Vo Tu Duc, has published a comprehensive guide on connecting AppSheet to Google Cloud SQL. Google provides a very useful support page on AppSheet data sources and performance, which highlights some scenarios where you might need to consider Cloud SQL.
For example, large-scale AppSheet apps using Google Sheets as a datasource with significant data access during specific times of the day, could encounter concurrency or quota issues imposed by the Sheets API. Using Cloud SQL you can configure your own instance configured to match your AppSheet app requirements avoiding any quota limits.
The guide provides very detailed step-by-step instructions for setting up Google Cloud SQL, creating a MySQL database, and connecting it to AppSheet. This includes lots of annotated screenshots, making it easy to follow even for those who are new to Google Cloud, MySQL, and SQL queries. Whilst the guide assumes you are creating a new app if you copy an existing app to an SQL database and then, if needed, upgrade the existing app.
One final thing to note is Cloud SQL isn’t part of the standard data integrations offered with AppSheet Starter and Core editions. As a result, all your app users will require AppSheet Enterprise licenses. Here is a summary of features that are supported with each subscription.
If your automations are not being triggered by Google Form submissions, Pablo Felip’s tip is it could be because your Google Workspace Admin has turned on AppSheet Core Security.
In this TU Special, we dive into the exciting new features coming to Google AppSheet and take a first look at the new Google Forms integration. For this special our co-host, Martin Hawksey is joined by Steve Franks (Head of Workspace Engineering, CTS Appsbroker). The discussion/demo was originally presented as an internal tech talk conducted at CTS Appsbroker – warning the episode contains some ‘passionate’ language.
Initial key takeaways which we discuss are:
Seamless Data Collection: Google Forms now acts as a first-class data source for AppSheet, eliminating the need for workarounds and additional add-ons. Data collected through forms automatically flows into AppSheet, creating a smooth and efficient workflow.
External Participation: The integration allows external parties to interact with your apps by simply filling out a Google Form. This opens up new possibilities for data collection and process involvement.
Powerful Automations: AppSheet’s automation capabilities can be triggered by form submissions, enabling you to create sophisticated workflows. The example discussed involves sending an email with an embedded AppSheet view upon form completion, allowing for approvals or further actions within the email itself.
Simplified Development: The integration makes building apps even easier, especially for those already familiar with Google Forms. The need for complex configurations or AppScripts is minimized, empowering users to focus on the business logic and desired outcomes.
Versatility: AppSheet has increased functionality, opening up a wider range of possibilities for app development and automation for Google Workspace customers. It can be used to create full-fledged apps with user interfaces or simply act as a behind-the-scenes automation engine.
Overall, the integration of Google Forms and AppSheet should significantly help empower users to build powerful solutions without coding expertise. Read more about “Build AppSheet automations using Google Forms” on the Google Workspace Updates blog.
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.
