Be part of our AppSheet Hackathon Series in 2024! Join us at the Google office in London for this in-person event in February. Find out more.
My employer CTS/Appsbroker is hosting a free AppSheet Hackathon in Google Office in London on 08 February 2024. This in-person event is the first of four we are planning this year designed to bring both new and existing AppSheet users together to learn and share.
For new users there will be a guided ‘no-code along’ to help you get started, followed by a hackathon with on-hand support. You can find out more including registration by following the link (if you are able to attend my tip would be bring a work colleague as this will help you bottom out your business use cases). I’ll be attending the event so if you are able to attend please come and say ‘hi’.
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.
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.
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.
In this report, I would like to introduce the sample scripts for using Microsoft Docs files with Document service, Spreadsheet service, and Slides Service of Google Apps Script.
Users have had the ability to edit MS Word, Excel and PowerPoint files directly in Google Drive for a number of years, Google making this the default behaviour in 2020. There aren’t currently any APIs or Apps Script services that enable you to edit these particular document types. This however hasn’t stopped Kanshi Tanaike for developing and sharing the MicrosoftDocsApp library which makes it possible to edit MS Word, Excel and PowerPoint files using the same methods as you would for Google Docs, Sheets and Slides.
The library is able to do this by converting the Microsoft files into the Google equivalent, before using the MS export options in Google Drive to replace the original files. This does create some limitations highlighted in the post. In particular, the original documents are overwritten and not directly edited which means any incompatible feature as part of the Microsoft to Google conversion will be lost. Regardless of these it’s a clever approach and might be useful for users who have to keep feet in both the Google and Microsoft worlds.
Use the Apps Script Lock Service to control Form submissions and prevent data loss
Lock Service code snippet
The following Google Apps Script is a one example of how the Lock Service can be used to prevent concurrent running of code. Here we have a Google Form that can be submitted by users at any point, the code then takes some of those details and appends them to another Google Sheet row. In normal circumstances this will happen relatively quickly and without clashes, but what if multiple people submit the Form at the same time!?
[Editor note: An alternative approach to tryLock() is waitLock(). The only different with a waitLock() is it will throw an exception after the set number of milliseconds. An example of waitLock() with onFormSubmit is included in the reference documentation]
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
Avoid downloading private service account keys by using impersonation in Apps Script to obtain access tokens.
For Google Workspace Admins you can gain super powers (and super responsibility) using Google Cloud project service accounts. A common scenario is using service accounts with domain-wide delegation to make API calls impersonating Google Workspace users. With this you can do things like set a user’s Gmail settings including signatures, authenticate as a user in Google Chat and much more.
A quick way to use a service account is to download a JSON key. The challenge then given the potential capabilities of service accounts is securely storing the JSON key. A alternative approach, which is explained in this post by Justin Poehnelt, is using Apps Script to create and fetch a short-lived credential for your service account.
Short-lived credentials are highly recommended for applications requiring robust security and precise access control for service accounts, reducing the attack surface and the risks with accidentally exposing static secrets.
You can find out more include code snippets/setup in Justin’s post which also links to the support documentation.
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.
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.
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.
