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.
For the next live broadcast of Totally Unscripted we will be exploring how Vertex AI and other GenAI solutions can be used to extend the capabilities of Google Workspace with targeted solutions that integrate seamlessly with Google Workspace Add-ons, Editor Add-ons, Chat apps, Drive apps, and more!
In this episode we will be joined by a panel of expert guests: Donato Meli (Workspace Go-To-Market at Google), Mohammad Al-Ansari (Developer Advocate (Partnerships) at Google), and Allen Firstenberg (Project Guru at Objective Consulting, Inc).
As always the show is broadcast live and you can ask questions via the YouTube chat, but also interested to hear in advance any of your burning questions for our panel using the comments section below.
We will be LIVE on 24 January, 2024 at 1200 PT / 1500 ET / 2000 GMT
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.
Welcome to the second edition of the Google Workspace Developer News! Find out what’s new on the Google Workspace Platform.
I like to think I keep a close eye on Google Workspace development, but with so much happening it’s easy to miss what’s new. Here’s a really comprehensive recap put together by Chanel Greco from the Google Workspace DevRel team. As well as covering the recent release notes Chanel spotlights some additional new resources and changes worth finding out about. The episode covers:
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.
WE ARE BACK!!! Totally Unscripted returns for more Google Workspace Developer community chat about new features, popular solution trends, best practices, the Google Workspace Marketplace and everything else related to Google Workspace development.
To kick off the return of Totally Unscripted for Season 5, in this episode the TU crew will take a look back at some of the more significant updates and additions involving the Google Workspace Platform “Since they’ve been gone!”
As always the show is broadcast live and you can ask questions via chat, but also interested to hear in advance any of your questions using the comments section below.
