AppsScriptPulse

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

Replace text in a Google Doc with an image from Google Drive with Google Apps Script

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:

  1. searchText – this is the unique string/pattern in the Doc that you want to replace with an image e.g. “<<keyword>>
  2. imageURL – this is the direct link to the image in Google Drive that you wish to use in the Doc.
  3. size – a numerical value representing the number of pixels for the image’s width/height.
  4. hyperlinkURL – if you want the image to be clickable then provide a link for it.

Source: The Gift of Script: Replace text in a Google Doc with an image

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

ReDriveApp: A new Google Apps Script library to replace DriveApp and restricted scopes 

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.

Source: GitHub – ReDriveApp

Changes to drive.google.com/uc URLs which break embedding images/files from Google Drive in your websites and AppSheet apps

Google Drive broke the ability to embed images with the /uc path. Here’s how to embed images from Google Drive in your website.

Previously Google Workspace developers could use a /uc path for embedding images and other files hosted in Google Drive. For example, images could be embedded in websites and emails using markup like:

<img src="https://drive.google.com/uc?export=view&id=1234567890abcdef" />

To my knowledge this was never an officially documented endpoint, but widely known about and shared within the community. Back in October 2023 Google announced Upcoming changes to third-party cookie requirements in Google Drive, which has started rolling out from the beginning of 2024.

The impact of this change is it breaks /uc embeds and these have started returning 403 errors. There is no reference to the /uc endpoint in the original announcement, which is why I think many people are only just finding out about the impact now. Google’s official guidance is to move to <iframe> with Google Drive publish and preview links. This post from Justin Poehnelt discusses the options and limitations for images.

There has also just been another announcement in the AppSheet community that Drive download URLs for embeddable content to be updated by May 1st, 2024. This is an extended window for AppSheet users and where possible Google will be emailing affected app owners where /uc urls have been detected. This announcement includes a link to a help page with tips for finding /uc occurrences in your AppSheet apps.

Source: Embed images from Google Drive in your website | Justin Poehnelt

How to automatically rename files in Google Drive with Apps Script and the Vertex AI Gemini API

Quickly rename files in Google Drive with Apps Script and Google Gemini AI. The script will automatically rename the files with a descriptive name based on the image content.

Amit Agarwal is celebrating 20 years of ‘Digital Inspiration’, one of my go to resources for Google Workspace tech tips, tutorials and how-to guides. This recent post from Amit is a great example of the types of solutions he regularly shares. This time Amit is looking at how the Generative AI capabilities of the Gemini API can be used to help rename images in Google Drive. To achieve this there Amit shares handy Apps Script snippets for finding images in a Google Drive folder, grabbing the thumbnail images as Base64 encoded strings before calling the Gemini Pro Vision API to get suggested filenames with the prompt:

Analyze the image content and propose a concise, descriptive filename in 5-15 words without providing any explanation or additional text. Use spaces instead of underscores.

For this solution an API key for Gemini Pro Vision is generated in Google AI Studio (formerly MakerSuite). There are still geographic restrictions on access to Google AI Studio, but given Apps Script runs on Google servers once you have a API key you can continue to use it without having to proxy a location.

Image credit: Amit Agarwal

Adding image capabilities to the GeminiApp Apps Script library has been on my TODO. Now I’ve got an example to play with it should be easier to do .. thank you Amit Agarwal.

Source: How to Automatically Rename Files in Google Drive with Apps Script and AI – Digital Inspiration

Semantic poetry with Google Apps Script – Using Semantic Retrievers and Attributed Question and Answer (AQA) in Google Workspace 

Image credit: Zzapps.nl

Image credit: Zzapps.nl

Imagine this: you recall a document about an intriguing subject but can’t pin down a specific term. It’s a common scenario where traditional search methods in Gmail or Google Docs often fall short, relying heavily on exact terms. Enter the realm of ‘semantic’ search, powered by advanced language models. ‘Semantic’ isn’t just a fancy word; it’s about understanding the meaning and context behind your words. Instead of a frustrating keyword hunt, these models interpret your descriptions, no matter how vague, to find that needle in the digital haystack.

“Code is Poetry” is the tagline popularised by the open-source blogging platform WordPress. In this post from Riël Notermans at Zzapps.nl ‘code is FOR poetry’. It’s well worth spending the time unpick what is happening in this tutorial. To help understand the implications I would also recommend watching this short video on the Google Workspace Developers channel where Riël explains how the technique can be used for other applications like knowledge bases.

Even if you are not interested in generating poetry it’s an opportunity to see how the Vertex AI Generative Language API can be used in Google Workspace, in this instance to generate text for a Google Doc using a corpus of data from your Google Drive. Follow the source link for the code and setup instructions.

Source: Semantic Poetry with Google Apps Script – Zzapps

Automatically creating descriptions of files in Google Drive using Gemini Pro API and Google Apps Script

Gemini LLM, now a Vertex AI/Studio API, unlocks easy document summarization and image analysis via Google Apps Script. This report details an example script for automatically creating the description of the files on Google Drive and highlights seamless integration options with API keys.

In this blog post, Kanshi Tanaike shows how you can automatically create descriptions for files on Google Drive using the Gemini Pro API with Google Apps Script. The post includes a step-by-step guide on how to set up and use the Gemini Pro API by generating a key is Google AI Studio (formerly Maker Suite). There are geographic restrictions on Google AI Studio, but you can call Gemini Pro from a Google Cloud project with a little more setup (a previous post sharing Tutorial: Respond to incidents with Google Chat, Vertex AI, and Apps Script outlines a general approach for connecting Apps Script to Google Vertex AI services.)

The post includes a couple of examples showing how Gemini can be used to provide responses to both text and visual prompts. Gemini Pro is still in public preview and as Kanshi Tanaike highlights rate limiting will likely prevent putting these solutions into production just now. At this point hopefully there is enough to start experimenting with Google Workspace integrations to Gemini.

Source: Automatically Creating Descriptions of Files on Google Drive using Gemini Pro API with Google Apps Script

Adding charts to Google Workspace Add-on sidebar with Google Apps Script

Creating dynamic charts in the sidebar of your Google Workspace Add-on can be an effective approach to getting across a lot of meaning in a somewhat confined space. In this tutorial, we will use Google’s Chart API to generate a live chart, first from some static data and then live from an external data source like a Google Sheet. All with the help of a little Google Apps Script magic.

Here’s another great tutorial from Scott Donald which provides a detailed tutorial for including charts in a Google Workspace Add-on using the Card Service. The tutorial covers how you can embed dynamic chart data from a Google Sheet. Whilst targeted at Google Workspace Add-ons (Gmail and Drive) you should be able to easily modify this for cards used in Google Chat. The source tutorial includes a video giving an overview of the solution and if you want to take this project further you can signup for Scott’s Create and Publish Google Workspace Add-ons with Google Apps Script: Master Class.

Source: Adding Charts to Google Workspace Add-on Sidebar Apps with Apps Script – Yagisanatode

Google Apps Script now includes Google Drive API v3 support in the Advanced Services

If you closely follow the Google Workspace Apps Script Samples repo on GitHub (who doesn’t :) you might have spotted a recent commit which updates the Advanced Drive Services to v3.

The v3 of the Google Drive API has actually been around for a number of years launched on December 14, 2015. One of the reasons Google introduced version 3 was it came with performance improvements.

My impression is that the gap between the v2 and v3 has narrowed over the years with minor release updates, but there are still some areas where you can get more from the v3 API. One example reported by Kanshi Tanaike is to query Google Drive files by createdTime.

Google provide a Drive API v2 & v3 comparison guide, which highlights the main differences between the two versions of the API. To use v3 in your Apps Script project when you enable the Advanced Service for Drive you can select the version number.

Source: Drive API v2 & v3 comparison guide  |  Google Drive  |  Google for Developers