AppsScriptPulse

I challenged Gemini to a game of battleship in Google Sheets. Here’s what happened 🚢

 

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.

Source: I Challenged Gemini to a Game of Battleship. Here’s What Happened.

Find out who has access to your Google Drive files using Google Apps Script

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:

// Requires Drive Advanced Service v3
const fileP = Drive.Permissions.list(fileId, {
  fields: "*" // all fields
});

fileP.permissions.map(perm => {
  console.log(`${perm.role} - ${perm.emailAddress}`)
});

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.

Source: Find out who has access to your Google Drive Files using Google Apps Script

Guide to completing Casa Tier 2 Security Assessment for Google Apps Script (and how to scan your Google Apps Script project for CASA)

If you want to publish your Google Apps Script project on the Google Workspace Marketplace, or if you already have an add-on or app on this marketplace and are using authorization scopes which are now restricted, you will have to go through a TIER 2 CASA security assessment.

Previously in Pulse we have shared ReDriveApp: A new Google Apps Script library to replace DriveApp and restricted scopes. If your Workspace Add-on still requires restricted scopes then you are going to have to think about the next steps and options. One option is going through the enhanced verification process, which requires a Cloud Application Security Assessment (CASA).

If you would like an overview of the process from a developer’s perspective you are in luck as Kelig Lefeuvre (Product Engineer at Scriptit & Folgo) has shared a guide specifically with Apps Script developers in mind. The article includes a number of useful tips and information about the review process which you won’t find in the official documentation.

As part of the CASA process Kelig recommends using the option to  you can submit bypass the Fortify scan and submit your own results. those results Kelig has also provided a second article with a step-by-step guide on ‘how to scan your Google Apps Script project for CASA’.

Source: Guide to Completing Casa Tier 2 Security Assessment for Google Apps Script & How to scan your Google Apps Script project for CASA

How to audit Google Shared Drive permissions in Google Sheets with Apps Script

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.

Source: How to audit shared drive permissions in Google Drive

Managing Google Cloud Storage files with Google Apps Script without using a service account

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.

Source: Managing Google Cloud Storage files with Google Apps Script without using a service account

Post and retrieve messages on Bluesky Social with Google Apps Script

Use Google Apps Script to automate actions on Bluesky Social using their API.

I’ve largely given up on X (formerly Twitter) and there are now numerous alternatives including Bluesky Social. If you would like to automate posting and getting messages from Bluesky Stéphane Giron provides a guide on how to communicate with Bluesky Social API using Google Apps Script. It includes detailed steps on how to obtain an app password, authenticate with Bluesky, retrieve messages for a user, and post a new message. The post also includes code snippets for each step, making it easy if you want to modify this integration to your own needs.

Source: Post and Get messages with Bluesky Social API and Google Apps Script

Create a Google Chat Bot on your own data with Vertex AI Search and Google Apps Script

Create a Chatbot answering user questions based on your documents. RAG implementation with multiturn using Vertex AI Search and Apps Script

More GenAI, this time from Stéphane Giron looking at how Apps Script can be used to provide the glue for a Google Chat app powered by Vertex AI Search. In this example you can see how unstructured data like PDF documents can easily be uploaded to a Cloud Storage folder, which then become the knowledge base for the Chat app. The post includes a Google Apps Script snippet for sending messages to the Vertex AI Search API as well as instructions on how to create the chatbot, including how to import data into Vertex AI Search and how to integrate the chatbot into Google Chat.

The post is a great summary of what is possible when combining Google Chat and Vertex AI Search. If you are interested in finding out more about what is possible using Vertex AI Search with follow-ups Google provide a comprehensive guide.  

Source: Create a Google Chat Bot on your own data with Vertex AI Search and Google Apps Script

GenAI for Google Workspace: Exploring Gemini API Function Calling with Google Apps Script — Part 3

 

Using Google Gemini API with Google Sheets to create personalized mail merges. An exploration in generative AI ‘function calling’

Google latest generative AI solution, Gemini, includes the capability to declare functions that the LLM can use in it’s response. The response includes the name of the function and the parameters the script needs to run the function.

The function isn’t executed by the LLM, but run with your code, which creates really interesting opportunities for Google Apps Script solutions. In particular, given user identity and authorisation is an integral part of Apps Script and how it integrates with other Google services it means solutions like personalised mail merges can be created in a couple of lines of code.

Follow the source link to find out more about function calling and exploring Gemini’s capabilities with data in Google Sheets.

Source: GenAI for Google Workspace: Exploring Gemini API Function Calling with Google Apps Script — Part 3

Google Sheets [🔧Fixed]. Prevent users from deleting formulas with Google Apps Script

You’ve made a formula, but someone deleted it. It may be annoying to restore your formula. You are not always able to protect a range with formulas, as protection will also forbid users from sorting range, adding new rows, hiding rows, etc.

Let’s create our formula protection with the help of a few formulas and app script code.

Google Sheets are fantastic for collaboration, the downside however can be that other people can break stuff. The Protected Sheets and Ranges can help with this, but there might be times when you need an alternative solution. If you find yourself in this situation Max Makhrov has come to the rescue with a solution to rewrite formula if they are accidentally deleted. You can find out more in the source post link. As a bonus Max includes a named Google Sheets function, FormulasMap, which can be used to export functions in a range to another sheet.

Image credit:
Max Makhrov

 

Source: Google Sheets [🔧Fixed]. Prevent users from deleting formulas

GenAI for Google Workspace: Exploring the PaLM 2 API and LLM capabilities in Google Sheets — Part 2

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.

Source: GenAI for Google Workspace: Exploring the PaLM 2 API and LLM capabilities in Google Sheets — Part 2