A generic Apps Script memoization function can be written to cache any function.
We’ve featured a couple of Apps Script optimisation techniques in the past. This example from Justin Poehnelt uses a technique found in many coding syntaxes of ‘memoization‘:
In computing, memoization or memoisation is an optimization technique used primarily to speed up computer programs by storing the results of expensive function calls to pure functions and returning the cached result when the same inputs occur again
For more background on when and why you might use ‘memorization’ you can read about Memorization [sic] in JavaScript. In the case of Google Apps Script developers have opportunities to integrate the built-in Cache Service and Properties Service to memorize function results in the context of the script, document or the user.
In the example provided in the source post by Justin it defaults to CacheService.getScriptCache() to store the memoized results but it would be easy to change this to CacheService.getUserCache() or CacheService.getDocumentCache(). With a little modification you could also include Properties Service.
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.
Learn how to use Smart Chips in Google Sheets to take full control of your data. Smart chips bring extra information to your Sheets.
Unleash the hidden power of Google Sheets with Smart Chips! Here’s a nice primer for our next episode of Totally Unscripted from Google Sheets magician, Ben Collins. You’ll have to tune in to the show to get the developer angle on Smart Chips, this post instead focusing on out-of-the-box features of Smart Chips in Google Sheets. Ben’s post is still incredible useful and in particular it was very interesting to read about data extraction from Smart Chips including the dot syntax when using Google Sheets formula/functions.
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:
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.
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.
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.
