AppsScriptPulse

Add-on, Library, Drive, Intermediate

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

Tags:
| | 0 Comments |

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

Library, Solutions, Intermediate

FetchApp: Open-source Google Apps Script library to enhance UrlFetchApp

Tags:
| | 0 Comments |

Google Apps Script is often used to pull data from various services via HTTP requests. However, these requests sometimes fail due to network or service issues. The default behavior of UrlFetchApp is to throw an exception, which you have to catch. Otherwise, the script execution will be interrupted. We often need more: send the request again instead of failing. There is no built-in way to do retries in Apps Script. Solution – To solve this problem and not copy-and-paste code snippets from project to project, I created FetchApp – an open-source Google Apps Script library

This looks like a useful little library which puts a nice wrapper around the UrlFetchApp service. The wrapper includes options for: retries, delays (including exponential backoff), custom callbacks, code hints and automatic logging. The supporting post includes lots of documented code snippets for using the library which should make it easier to implement. A very handy drop in replacement if you are having to communicate with more sensitive API endpoints. The code is also on Github if you want to take a look at how the library is coded.

Source: FetchApp: UrlFetchApp with Retries

Solutions, AI, Drive, Vertex AI, Intermediate

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

Tags:
| | 0 Comments |

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

Add-on, Solutions, Intermediate

Creating a usage dashboard for your Google Workspace Marketplace Add-on with LookerStudio, BigQuery and Logging Sinks

Tags:
| | 0 Comments |

Flubaroo Add-on Dashboard

At the moment, Add-ons in Google Workspace offer only basic usage analytics via the Workspace Marketplace SDK. These include install data broken out by domains and seats (for Add-ons installed by Workspace admins), and individual end-user installs. This is useful information, but doesn’t tell you much about who is actively using your Add-on, nor give you the ability to breakdown that usage by different dimensions.

The code and instructions in this repo will help you gather and visualize Add-on usage data, such as active usage of your Add-on broken out by user characteristics. It also shows you how to log specific events that correspond to use you want to track (i.e. new installs, uses of particular features, etc).

Here’s a useful solution for Google Workspace Add-on developers who would like more actionable insight into their Google Workspace Add-on usage. This isn’t an official Google solution but comes from the creator of the very popular Flubaroo add-on, Dave Abouav.

The solution includes a Google Apps Script helper snippet which enables your add-on to ‘call home’, or in this case into Cloud Logging, with basic user metrics as well as other events you would like to log. The project also details how you can route usage logs from Cloud Logging into BigQuery by creating a ‘sink’ in Google Cloud Log Router.

The final step is creating a LookerStudio dashboard to visualise the BigQuery data. As noted by Dave there is a cost to using BigQuery for long term storage and querying, which should be kept in mind. More details in the source link below.

H/T to Chanel Greco for highlighting this solution.

Source: Instructions for creating a usage dashboard for your Google Workspace Add-on

Solutions, Sheets, Intermediate

How to write Google Apps Script logs into Google Sheets

Tags:
| | 0 Comments |

In Google Apps Script, the ability to track and record actions, errors, and performance metrics is crucial for both developers and users. However, the built-in logging mechanisms often fall short regarding accessibility and ease of use. This is where Local Google Apps Script Logging comes into play, offering a streamlined and integrated approach to capturing script activities.

Google Apps Script has a couple of logging options, including the native Apps Script execution log, to setting up a Cloud Developer Console project and using Cloud Logging and Error Reporting.  There are a couple of alternative Apps Script logging solutions out there, like Peter Herrmann’s BetterLog. Here’s the latest alternative Apps Script logging solution from Dimitris Paxinos called LocalLogger.

LocalLogger has some nice features including built-in severity colour coding and customisable email notifications. Even if you don’t need a alternative logging solution the code is well structured and includes a way to mimic an Enum list. You can find all the code and a video explaining LocalLogger via the source link 👇🏻

Source: How to write Google Apps Script logs into Google Sheets

Solutions, BigQuery, Calendar, Intermediate

Ghislain Sanjuan on LinkedIn: Google Admin and the raiders of the lost calendar (combining Google Workspace BigQuery logs and Apps Script)

Tags:
| | 0 Comments |

#GoogleCalendar gave me a funny adventure !

Here’s a fun and useful take from Ghislain Sanjuan explaining how you can restore deleted calendar events using Google Apps Script. To restore the events data is used from BigQuery Workspace Logs. For those unfamiliar Google Workspace Admins on supported editions of Workspace can setup a streaming export of activity data to BigQuery. This can be incredibly useful in situations where you need to review audit and usage data and in this scenario use it to restore data.

Source: Ghislain Sanjuan on LinkedIn: Google Admin and the raiders of the lost calendar

Solutions, Drive, Sheets, Intermediate

Uploading files without authorizing scopes  with a dialog in Google Sheets using Google Apps Script

Tags:
| | 0 Comments |

Making the shared users input a value and upload a file without authorization of the scopes with a dialog on Google Spreadsheet.

It’s usually unavoidable when you are creating and sharing Apps Script projects that the user will be required to complete an authentication flow to approve access to the services you include in your script such as reading/writing to Google Sheets, Drive etc.

The process is reliant on OAuth scopes, which are identifiers that specify the level of access an application requests from a user’s Google Account data. They are essentially a way for developers to define the specific actions or data their application needs to access. When a user grants an application access to their Google Account, they are agreeing to allow the application to perform the actions or access the data specified by the scopes.

Sometimes you can restrict the ‘scope’, for example, usually for Sheets, Docs, Slides, and Forms where I need only permission for the current doc I will include the following documented comment to only require access to the doc that the script project is bound to:

/**
 * @OnlyCurrentDoc
 */

There are some limitations when defining the scopes you need. For example if you would like a user to upload a document to Drive usually you would require the very broad https://www.googleapis.com/auth/drive scope which will prompt the user to ‘view and manage all of your Drive files’.

Understandably users may become nervous approving such a scope and in some cases Google Workspace Admins may prevent authentication for this type of scope for unverified/unconfigured applications.

There are alternative approaches to allowing users to execute Apps Script projects without having to approve scopes like Google Drive. There are clearly security considerations when you do this, so always proceed with caution.

This post from Kanshi Tanaike has some examples of how users can be prompted to upload files to Google Drive without authorising Drive access. The post includes two approaches, the first using a Web App which is pre authenticated to run as the user who has deployed the Web App, the other using a service account. The source post contains all you need to know include the code.

Source: Uploading Files without Authorizing Scopes by Shared Users with Dialog on Google Spreadsheet using Google Apps Script

Official News, Chat, Intermediate

Generally available: You can now call the Chat API from Apps Script with the Advanced Chat Service

Tags:
| | 0 Comments |

The Advanced Chat service lets you use the Google Chat API in Apps Script. This API allows scripts to find, create, and modify Chat spaces, add or remove members to spaces, and read or post messages with text, cards, attachments, and reactions.

There have been a number of updates to the Google Chat API summarised in the linked release notes page. A recent announcement which might interest Google Apps Script developers is the generally available of a new  Advanced Chat Service. As noted in the official documentation:

The Advanced Chat service lets you use the Google Chat API in Apps Script. This API allows scripts to find, create, and modify Chat spaces, add or remove members to spaces, and read or post messages with text, cards, attachments, and reactions.

There are some perquisites to use the Advanced Chat service besides the usual turning it on before use, the main one being the requirement to use a standard Google Cloud console project rather than the default project created automatically.

Source: Google Chat developer platform release notes  |  Google for Developers

Solutions, Drive, Gmail, Sheets, Intermediate

Build your own Gmail-based expense tracking solution with Google Sheets and Google Apps Script

Tags:
| | 0 Comments |

Use Google Apps Script to automate email-based expense tracking. Store and track your receipts entirely through Gmail, Drive and Sheets

Here’s a nice tutorial on how to create an email-based expense tracking system using Google Apps Script. The solution allows users to submit expense reports via email, which are then automatically processed and stored in a Google Sheet with attachments stored in Google Drive.

The blog post by Joshua Mustill provides detailed instructions on how to set up the system, including how to create the Gmail labels and filters, the Google Sheet and the Apps Script code. There are some nice features in the code you might want to use in other projects including the creation of date based Google Drive folders for storing Gmail attachments.

Source: Build your own email-based expense tracking with Google Apps Script

Snippet, Solutions, Docs, Sheets, Slides, Intermediate

Retrieve comments with emoji reactions from Google Docs, Google Slides and Google Sheet using Google Apps Script

Tags:
| | 0 Comments |

This report introduces the method for retrieving the Emoji reactions from the comments in Google Docs files (Google Documents, Google Slides, and Google Spreadsheets) using Google Apps Script.

Here’s a clever workaround by Kanshi Tanaike for retrieving comments with emoji reactions in Google Docs, Slides, and Sheets using Google Apps Script. The process to achieve this is a little convoluted in that Google Docs, Slides and Sheets are exported in Microsoft equivalent formats, then re-imported into Google formats. To remove some of the pain the post includes sample code snippets for achieving this, which can easily be adapted.

Source: Retrieve Comments with Emoji Reactions from Google Documents, Google Slides, and Google Spreadsheets using Google Apps Script