I tried to see if an AI that’s good at writing could also make smart moves in a game. I chose Battleship and set it up in Google Sheets to play against Gemini, the AI. The result was mixed. On one hand, yes, Gemini could play the game. It followed the basic rules and even managed to sink some of my ships. This was a big deal, especially since it took me a ridiculous number of days of coding to get there, and I nearly gave up at one point.
Dmitry Kostyuk has shared a blog post detailing his experiment pitting the Gemini API against himself in a game of Battleship. As explained by Dmitry while Gemini could follow the rules and even sink some ships, it needed help to avoid basic mistakes, revealing that AI still has room to grow in the realm of strategic games.
Dmitry built the game in Google Sheets and the source code is linked from the post. To guide Gemini, Kostyuk crafted detailed prompts outlining the game’s mechanics and decision-making logic. However, he encountered challenges due to Gemini’s limitations in providing strategic responses. Despite these hurdles, the project yielded valuable insights into prompting techniques for AI decision-making.
In this blog we are going to find out who exactly has access to my Google Drive files, be it a Google Sheet, Google Doc, Form and more. To do this we are going to be using the DriveApp and Google Apps Script.
Recently Aryan Irani shared this post which shows how you can get the file permissions on a Google Drive file using DriveApp. This uses the DriveApp methods for .getEditors()and .getViewers(), which left me wondering about commenters???
The answer is file commenters are included in the .getViewers() response and as pointed out by TheMaster you can filter out commenters with .getAccess().
Another approach is to use the Advanced Drive Service:
The advanced Drive service lets you use the Google Drive API in Apps Script. Much like Apps Script’s built-in Drive service, this API allows scripts to create, find, and modify files and folders in Google Drive. In most cases, the built-in service is easier to use, but this advanced service provides a few extra features, including access to custom file properties as well as revisions for files and folders.
Learning about the Advanced Drive Service can be useful as it open up more opportunities to interact with Google Drive content and can also help you get file properties.
In the case of permissions there is a dedicated Permissions Resource that allows access to all the file permissions. For example, if I wanted to see what accounts had access to a file in MyDrive you can use:
Using the Advanced Drive Service does require a step up in understanding how to call the Drive API and the response you get but once you begin understanding it can come with huge benefits with more efficient code.
The Slides Advisor project is an open source, Google Workspace Add-On that uses artificial intelligence (AI) technology to review and give feedback on your presentations, whenever you need it. The project uses Google’s Gemini API image and text processing features to analyze your Google Slides against a set of guidelines and lets you know how you are doing against those guidelines. Even better, you can customize those guidelines to follow your organization’s recommendations, or remind you to improve your presentations based on rules you define for yourself.
We’ve all been there: scrambling to cram too much information onto a single slide. But what if you had an AI assistant to give your presentations a once-over?
The Slides Advisor is a free, open-source Google Workspace Add-on for Google Slides that uses the power of AI to analyze your presentations. It checks your slides against customisable guidelines, helping you ensure they’re clear, concise, and visually appealing.
Everything you need to get started is covered in the source post by Joe Fernandez and Steve Bazyl. One of the highlights for me is seeing how you can setup a service account to access the Gemini AI API with Google Apps Script. The post also covers how you can modify the prompt you use to get different responses from Gemini.
Probation tracker tool to easily manage upcoming review meetings. Have automated reminder emails sent to Line Managers.
Manage probation review dates in a spreadsheet and be sent reminder emails
Key Functionality
The daily check is performed between 7am – 8am every day and for any emails that are sent a Note will be attached to the cell with its timestamp for record purposes and it will be coloured in light-green.
You can continue to append further rows for new staff starters after the initial Sheet setup or you may wish to delete/move rows for those that have completed their probation period.
You can adjust dates, emails addresses, etc at any point in time.
If you enter something into the ‘Probation Passed’ column then that given row will be skipped in future checks, to help save time if you continue to add to this Sheet.
You do not have to enter a review date for every single column, thus allowing flexibility between differing roles, just leave it blank.
There is a Log sheet to help capture any errors and it will attempt to email the account that creates the daily check (trigger) to alert them.
Editor: A reminder that Phil Bainbridge will be joining us on Totally Unscripted at the slightly earlier time for non-US viewers of 7pmUTC 20 March, 2024. This ‘Google Sheets probation tracker and reminder tool’ is a great example of the types of solutions Phil creates as part of his role at the University of York. Watch live at 1200 PT / 1500 ET / 1900 GMT
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
Google Apps Script automates tasks (even offline) and builds web apps using spreadsheets as databases. This report presents a basic dining reservation system to illustrate key aspects of web app development with Apps Script, HTML, and Javascript.
One of my first Google Apps Script projects was an event booking system. It was a great way to learn about integrating with the various services like Google Calendar. Roll forward 14 years and it is a topic that Kanshi Tanaike has revisited with a dining reservation system built on Google Sheets with a web app frontend.
All the code is published on GitHub and even if you don’t need a reservation system a great opportunity to see how one can be coded and there could be lots of juicy little snippets that you can use in your own projects.
This tutorial shows how to make a Google Chat app that a team can use to manage projects in real time. The Chat app uses Vertex AI to help teams write user stories (which represent features of a software system from the point of view of a user for the team to develop) and persists the stories in a Firestore database.
A recent episode of Totally Unscripted delved into “going beyond basic bots”, highlighting a couple of Google Chat app tutorials from the Google Developer documentation. One example, the “project management” Chat App, is worth mentioning in a Pulse post.
While this example uses Google Cloud Functions instead of Google Apps Script, as discussed in the episode, both approaches share many similarities. Deploying the project management app involves several steps, but I believe it’s a worthwhile investment to learn how to combine different solutions for building Google Workspace integrations. For developers seeking to expand their Google Workspace Add-on capabilities, this example serves as a valuable reference.
If you’re interested in using Firestore for data management but prefer Google Apps Script, Justin Poehnelt’s post on “Using Firestore in Apps Script.” is a great resource. This post provides a basic Firestore wrapper and links to other relevant Apps Script/Firestore libraries.
Are you looking for an efficient way to get an overview of all shared drives and their access permissions within your organization? Whether you’re navigating a company reorganization or implementing security procedures, accessing this information can be challenging.
This post from Niek Waarbroek highlights the important of auditing Google Shared Drive permissions. This can be a challenge to do using the Google Workspace Admin Console, especially if you have lots of Shared Drives.
To help Niek has shared a Google Sheet with Apps Script code that automatically generates a list of all shared drives and their associated root level permissions.
Shared Drive auditing is bit of a niche subject, but I encourage you to have a look at the post and script as it has some nice features which could be applicable to other projects. For example, there is a gaspTimeManager to make sure the script doesn’t go beyond the script execution limit.
Search the body of a Google Doc for a specific string/pattern and insert an image in place of it.
In this example the code is designed to sit behind the Google Doc so it is bound to it. There are 4 pieces of information to complete in order to setup the script:
searchText – this is the unique string/pattern in the Doc that you want to replace with an image e.g. “<<keyword>>“
imageURL – this is the direct link to the image in Google Drive that you wish to use in the Doc.
size – a numerical value representing the number of pixels for the image’s width/height.
hyperlinkURL – if you want the image to be clickable then provide a link for 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
Using Google Apps Script scoped authentication to interact with Google Cloud resources without a service account
A key feature of Google Apps Script is its integration into Google Cloud. The default behaviour when any Apps Script project is created is that an associated Google Cloud project is created and configured. This default project is not accessible to the user and for most scripts, the user doesn’t need to worry about any of the configurations such as enabling APIs and configuring authentication settings.
Other key aspects are identity and authentication. The default behaviour for scripts is usually to run as the account executing the script, Apps Script automatically determining what authorisation is required for different Google services based on an automatic scan of your code or from what scopes have been set explicitly in the Apps Script manifest file.
The last piece in the puzzle is the .getOAuthToken() method which is part of the ScriptApp Service:
Gets the OAuth 2.0 access token for the effective user. … The token returned by this method only includes scopes that the script currently needs. Scopes that were previously authorized but are no longer used by the script are not included in the returned token. If additional OAuth scopes are needed beyond what the script itself requires, they can be specified in the script’s manifest file.
What this means is in script projects we can borrow an access token to use other services that the effective user has access to and have been declared in the script project scopes. For example, if my Google account [email protected] has been added to another Google Cloud project with the Google Cloud Storage service enabled, I can use Apps Script to generate a token to use the Cloud Storage service in that project.
To help illustrate this, here are two examples for interacting with Google Cloud Storage buckets to upload and download files to Google Drive.
Apps Script class that provides equivalent methods offered by the built-in DriveApp, but that does not require use of full ‘/drive’ OAuth scope (which is a “Restricted” scope”). Instead, uses only these Recommended (non-sensitive) and/or Sensitive scopes
When you use OAuth 2.0 to get permission from a Google Account to access their data, you use strings called scopes to specify the type of data you want to access on their behalf. For Google Workspace Add-on developers wanting to distribute your app in the Google Workspace Marketplace one consideration is only using the scopes required for your app. In the case of Google Drive a number of the scopes are classified by Google as “restricted”. To use “restricted” scopes there is an enhanced verification process, which requires Cloud Application Security Assessment (CASA) by an independent security assessor, which come at a cost and can be time consuming.
ReDriveApp is a new Apps Script community library published by Dave Abouav which makes it easier for developers to use reduced recommended scopes for integrating with Google Drive. As explained by Dave:
The built-in DriveApp service is an easy and powerful way to interact with Google Drive in your Google Apps Script projects, which is why so many developers make use of it. It’s one downside is that it forces your project to use the full ‘/drive’ OAuth scope, which is a “Restricted” scope.
In many cases though, projects don’t really need the full /drive scope for common tasks, and the Recommended /drive.file scope is sufficient. This allows your project to create new files, and open existing files if authorized by the end-user via the Drive Picker. Using it also avoids the aforementioned security review, and is less alarming to users when authorizing your app.
ReDriveApp is still work-in-progress and not an official Google project. There are a number of methods that still need to be implemented, but the project is open source and can be contributed to on GitHub. If you are planning or have already developed a Marketplace Add-on that has stalled due to enhanced verification for restricted scopes it could be worth looking at and contributing to this library.
