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.
Exponential Backoff, a process by which when something fails you try again a set number of times whilst increasing the delay between each attempt.
Sample Apps Script code for Exponential Backoff
The following Google Apps Script is designed to explore Exponential Backoff – a process by which when something fails you try again a set number of times whilst increasing the delay between each attempt, up to a certain point.
I needed this for a tool I built which adds Guests to a Calendar event from a Google Form submission. Whilst I was usingĀ ScriptLockĀ to prevent simultaneous submissions, the code ran so fast that it wouldĀ infrequentlyĀ trip the Calendar API with the following error message “API call to calendar.events.patch failed with error: Rate Limit Exceeded”.
By infrequently I mean a reported issue only once in 3,500 submissions over the course of 12 months. Enough however to take the opportunity to learn about Exponential Backoff and to squash that single instance.
Just a note that this isĀ one wayĀ to implement it.
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
Loop through a Google Sheet cell of comma-space separated email addresses and check their format is valid, otherwise display a user popup.
The following Google Apps Script is designed to loop through a Google Sheet cell of email addresses that have been separated by a comma and space. It then uses a regular expression (regex) to confirm the email address meets the correct formatting criteria.
This code was developed as a way of implementing additional checks when asking users to be precise in how exactly they enter multiple email addresses. So if they were to forget theĀ spaceĀ for instance it could alert them, before the rest of the code risked failing as whatever task it was designed to do.
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
Join the experts on October 6th to learn Apps Script tips and tricks to combine APIs, automation, configuration as code, and more to improve your productivity using Google Workspace. Whether you’re a seasoned developer or just getting started with low-code development, this event will provide you with the insights and information you need to start building custom solutions with Google Apps Script.
Recently on Pulse we highlighted the ‘Getting the most out of Google Workspace with Apps Script, tips and tricks’ session as part of Google Cloud Next ’23. This wasn’t one of the livestreamed sessions, but the good news is the session is being rerun online on October 06, 2023 @ 9am PT / 4pm UTC, hosted by Charles Maxson and Kara Ireland from the Google Workspace team.
As well as using the source link to register for the event there is a form link where you can submit your questions in advance. If you can’t make the live session registering will also mean you’ll get notified about the recording. Looking forward to seeing other community members in the YouTube Live chat!
In this video you will find out how you can publish your app to the Google Workspace Marketplace.
Publishing a Google Workspace Add-on or Chat App can be a bit daunting. We’ve feature a couple of ‘how-to’ guides, which go into the detailed steps of the process, and my favourite has to be Alice Keelerās top tips for getting your Google Workspace Add-on published. If you are still unsure about the Google Workspace Marketplace publication options and information required this video from Chanel Greco gives an overview of the entire process from start to finish.
Google Workspace Admins is an IT admin driven series of live and pre-recorded technical talks covering any and all topics that a Google Workspace Admin would find useful. Weāll cover topics that would benefit the Tier 1 IT help desk admin on up to the super admin and the CIO. Signup to get invited to all future events.
I’ve been a Google Workspace Admin for over a decade, mostly working on small domains. More recently my role requires me to support larger domains, often with enhanced security needs and the requirement to generally do everything on a bigger scale. This is where tools like GAM and Google Apps Script become essential to help with the heavy lifting.
Broadening the range of Google Workspace domains I support has also required me to broaden the channels and communities where I can learn and keep up-to-date. Recently, I was pointed to the Google Workspace Admins YouTube channel. Whilst this channel is relatively new it’s bursting with a wide range of technical focused solutions and discussions. Follow the link to subscribe to the channel and there is a form to get invited to all future events.
Welcome to the Google Workspace Developers YouTube Channel, where developers of all types can learn about building solutions with Google Workspace. Explore the full range of the Google Workspace Platform from Apps Script to Chat Apps to Workspace APIs and more. Leverage Google Workspace’s products such as Gmail, Docs, Sheets, Slides, Drive, and many more to customize, integrate, or extend these products with our developer tools. Visit our website ā developers.google.com/workspace
Google has just launched a new YouTube channel dedicated to Google Workspace developers. Google Apps Script community members might recognise the first featured video series on the ‘Anatomy of Google Chat apps’, which is presented by Chanel Greco who recently joined the Google Workspace Developer Relations team. As well as featuring a curated collection of previous Google content on topics from no to low code solutions the channel also includes content from the wider community.
Forms History is a Google Workspace Add-on that allows you to monitor your version history and store copies in Google Forms
Not just talking the talk, but also walking the walk. Shortly before taking a new role at CTS, the largest dedicated Google Cloud Partner in Europe, I published Forms History an add-on to enable revision history functionality in Google Forms.
Fellow Google Developers Expert, Scott Donald, has picked this up in the latest episode of GWAOw! his dedicated to Google Workspace Add-on review show. You can follow the source link for the YouTube clip and more. As part of the episode Scott rightly highlights the creative work of Alice Keeler, which hasn’t just included artwork but Alice has also provided a huge amount of support in promo and usability.
Crop Google Sheet Data to the data range or a selection in 2 button click with Crop Sheet. Full Walkthrough
Does this Google Workspace Add-on developer story sound familiar? You are able quickly write the code that executes your add-on functionality, but then you spend days buried in HTML or Card Service making the UI. The Crop Sheet add-on by Eric Koleda highlighted in this latest GWAOw! episode is a great example of what is possible just by using custom menus. For what it is worth the source code (all 73 lines including comments!!!) is on GitHub. Follow the source link for the video demo from Scott Donald and links including the GitHub repo.
In this video you will learn how to access an API with Google Apps Script.
Chanel Greco has created this video tutorial which walks through some different ways you can interact with other sites with Google Apps Script to get data into Google Sheets. For the tutorial Chanel uses the Weather API and by coincidence Luxman Ravindrakumar has also shared a similar tutorial on Medium explaining how to use the OpenWeather API. So if you prefer learning by watching or reading you’ve no excuses :). Check the sources links below for both tutorials.
