AppsScriptPulse

Google Apps Script gets a public knowledge base of Github projects with Gassypedia

All the Google Apps Script related github activity is now on Google Bigquery and Google Looker. Here’s how it was done.

Bruce Mcpherson has recently revisited his work on the visual Google Apps Script explorer tool, scrviz. Bruce has been working diligently to address limitations with the Github API and overcome quota restrictions. This new iteration still allows users to explore Google Apps Script projects and creators, but this time through a shared Looker Studio dashboard. The underlying dataset has also been made available on BigQuery if you would like to do your own analysis.

Follow the source link to find out more and explore the data!

Source: Gassypedia – public Google Apps Script on Github – Desktop Liberation

Answer questions based on Google Chat conversations with a Gemini API powered Chat app

This tutorial shows how to make a Google Chat app that answers questions based on conversations in Chat spaces with generative AI powered by Vertex AI with Gemini. The Chat app uses the Google Workspace Events API plus Pub/Sub to recognize and answer questions posted in Chat spaces in real time, even when it isn’t mentioned.

Here is another great tutorial from the Google Chat DevRel team, this time showcasing how the Google Workspace Events API and some new Google Chat UI elements can be used to turn a Chat space into a Gemini Pro powered knowledge base.

The sample solution will let you consume your Chat space message history into a Firestore database. The Chat app is an intelligent agent that can then monitor for new questions and make suggestions using Gemini to generate content based on the previous messages.

There is quite a bit of setup required as part of this tutorial, but it provides a solid foundation for quickly scaling this to your needs.  A demonstration and explanation of the sample app was given as part of the Google Chat apps and APIs: Build connected workflows for the hybrid workplace session at Google Cloud Next and a recording might be available soon!

Source: Answer questions based on Chat conversations with a Gemini AI Chat app  |  Google Chat  |  Google for Developers

How to access secrets from Google Secret Manager using Google Apps Script

This detailed guide explains how you access your passwords, API keys and other sensitive data stored in the Google Secret Manager with Google Apps Script.

API keys and other credentials are your digital passports to access valuable data and services from various online platforms. When you’re developing Google Apps Script projects that connect to using keys, it’s of paramount importance to handle these keys with care and prevent them from falling into the wrong hands. Mishandled API keys can lead to unauthorized access, data breaches, and potentially even financial consequences.

One way to store API keys in Google Apps Script is the built-in Properties Service. This service offers a mechanism to store key-value pairs attached to your Apps Script project. While the Properties Service offers a convenient way to store sensitive data, it comes with limitations:

  • Not True Encryption: The Properties Service stores data in a readable format. Anyone with sufficient access to your Apps Script project might be able to view the API keys.
  • Limited Access Control: It’s challenging to implement fine-grained access restrictions over who can see the stored keys within the Properties Service.

For a more secure and scalable approach you might consider using Google Cloud’s Secret Manager. As described by Amit Agarwal in this source post:

Google Secret Manager is a cloud service where you can store sensitive data such as passwords, database credentials, encryption keys or any other confidential information that you don’t want to hardcode in your application’s source code. You can also set up an expiration time for the secret and the Google Secret Manager will automatically delete the secret after the specified time.

You can read more about using the Google Cloud Secret Manager in Amit post, which also includes the setup and Apps Script code snippets to make it work. For some general best practices on key management, Google provide this support article.

Source: How to Access Secrets from Google Secret Manager using Apps Script – Digital Inspiration

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

Boost your presentations with AI: A Google Apps Script tutorial for a Google Slides reviewer assistant

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.

Source: Build an AI Slides Reviewer with Gemini  |  Google AI for Developers

Essential Apps Script: a Practical Guide from the University of York

Welcome to the Essential Apps Script course. This guide is designed so you can work through an introductory course in Google Apps Script, designed so you do not need any knowledge of coding to get started.

As part of our latest episode of Totally Unscripted we chatted to Phil Bainbridge about the work he does at the University of York supporting and creating Google Apps Script projects.

One of the resources the University has developed is the Essential Apps Script course which is designed to lead beginners through the first steps in learning Google Apps Script. The guide is shared under a Creative Commons CC-BY-NC-SA which allows anyone to reuse course materials for non-commercial use if shared with the same licence and attributing the University of York.

This course is a perfect starting point for both beginners and those tasked with helping others learn Google Apps Script. Its step-by-step approach and beginner-friendly language make it easy for anyone with no prior coding experience to grasp the fundamentals. For those supporting others, the course provides a clear and structured resource to guide colleagues through the process, saving valuable time and ensuring everyone is on the same page from the beginning.

Source: Subject Guides: Essential Apps Script: a Practical Guide: Essential Apps Script

Creating dining reservation system with Google Apps Script

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.

Source: Creating Dining Reservation System 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

“going beyond basic bots” – Tutorial: managing projects with Google Chat, Vertex AI, and Firestore  

Image credit: CC-BY Google

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.

Source: Manage projects with Google Chat, Vertex AI, and Firestore  |  Google for Developers