Ping results to Google Cloud regions and short code snippet demonstrating how to measure latency from Google Apps Script.
As Google Apps Script is a software as a service, writing efficient code is crucial, especially for complex solutions. It’s also important to consider marginal gains, such as finding small ways to improve your script’s responsiveness.
If you’re using Google Cloud services like Vertex AI, Cloud Functions, or others, you’ll also want to consider the latency between your Apps Script code and the Google Cloud data region. Justin Poehnelt recently revisited the work of Ivan Kutil, providing a way to measure Google Cloud region latency from your Apps Script project.
Interestingly, when I ran Justin’s sample code, I found that the region with the lowest median latency (us-east1) was approximately 11 times faster than the region with the highest (asia-south2). You can make a copy of this sheet to view my results and run the test for yourself.
It has finally happened! Bot Bender 2.0 is now available for installation from the Google Workspace Marketplace. In this post, I will walk you through the steps needed to get to the marketplace.
We’ve shared a couple of journeys to Google Workspace Marketplace publication but this is the first I’ve seen for publishing a Google Chat App. For people who have gone through the process it’s probably a familiar story of back and forth with the Marketplace review team.
In this post Anton Shevchuk shares how his Chat App received multiple rejects due to issues with the location of the support/privacy pages. The moral of the story is to be persistent, and don’t hesitate to push back if you believe the reviewers are mistaken. The post from Anton also outlines the other steps involved, including connecting the Google Workspace Marketplace SDK and configuring app visibility settings.
In February 2024, Google made a change in Google Sheets to enhance data security. Now, users must provide explicit consent before Sheets can connect to external data sources. This change aims to prevent unintentional data leaks and improve overall data loss prevention within Google Sheets.
Impact on Users:
When manually adding or editing certain IMPORT functions (IMPORTHTML, IMPORTXML, IMPORTFEED, IMPORTDATA) and the IMAGE function in a Google Sheet, a warning banner will appear blocking sending a receiving data until the user gives consent. To enable this functionality, users need to click “Allow access.”
Impact on Developers:
As highlighted in the first source post by Justin Poehnelt, this change caused issues for developers who programmatically create Google Sheets and automate tasks, such as generating PDF documents as the IMPORT and IMAGE functions were blocked.
Solutions for Developers:
Fortunately, Google introduced an update to the Google Sheets API to address this challenge. Developers can now enable external data access programmatically by setting the importFunctionsExternalUrlAccessAllowed property in the Google Sheet metadata. This allows specific IMPORT and IMAGE functions to work without manual user consent.
For the IMPORTRANGE function, a similar API property is currently unavailable. However, an unofficial workaround exists that involves calling an undocumented endpoint using Google Apps Script as detailed in the second source post by Kanshi Tanaike.
Future Expectations:
As Google continues to prioritize data security, there will likely be further enhancements to data loss prevention controls within Google Workspace. Hopefully, future updates will also include official API controls for IMPORTRANGE and other functions to facilitate legitimate data access for developers while maintaining data security.
Caution when using onFormSubmit triggers to avoid mass simultaneous executions when un/linking a Response Sheet.
The following post is a caution around onFormSubmit triggers after I recently discovered a way that somebody had inadvertently managed to execute my code simultaneously over 253 times. Now unless a Google Form is submitted by 253 individuals all at the same time … then this is baffling.
When should an onFormSubmit trigger run?
At the point when a Google Form has been completed and the ‘Submit’ button pressed.
How else can the trigger be activated?
Well … it turns out when you unlink and then link back a Google Response Sheet containing the onFormSubmit trigger, it will activate it for every single Form response collected up to that point in time …
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
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.
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’.
If you are looking to publish a Google Workspace Editor Add-on, you may need to consider upping your security with an anti-CSRF token.
Scott Donald has shared some really useful information about protecting your Google Workspace and Apps Script data from malicious websites using anti-CSRF tokens. Google is currently in the process of reviewing Google Workspace Add-ons which means for many restricted scopes in Google Drive developers need to complete CASA Tier 2 security assessments, which require anti-CSRF token.
For more context CSRF attacks are a type of cyberattack that can allow hackers to trick your browser into sending unwanted requests from your account, such as making purchases or changing your settings. This can put your data and privacy at risk.
Anti-CSRF tokens are a simple but effective way to prevent these attacks. They work by generating a unique ID for each user session, which is then included in all requests sent to Google Workspace and Apps Script. This way, the server can verify that the request is legitimate before processing it.
Implementing anti-CSRF protection is relatively easy and this tutorial from Scott explains have you can implement it in your Google Workspace Editor Add-on.
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.
I recently faced a frustrating issue when writing data to a CloudSQL database with the JDBC class in Apps Script. I kept getting the following error:
Exception: Argument too large: SQL
I also observed that it only happened when my SQL query reached a certain length. I considered breaking it down into multiple queries, but I was still puzzled 🤔 because I was only sending a few dozen kilobytes of data.
Now, the thing is, the official documentation could be more helpful; even though the solution is there, it needs to be better explained. So, I turned to StackOverflow. There was a discussion on this exact topic, but to my surprise, I was still waiting for an answer. Until, well, I wrote it 😉
The key takeaway for Google Apps Script users is unlike Google Docs which can automatically save a version history of a document, Apps Script project history requires the user to use deployments (Deploy > Manage deployments) to create a version should you want to see get a highlights or code changes between the current and previous versions. This will be less of an issue if you are using Google Apps Script to deploy web apps, Workspace Add-ons or libraries, which already use deployments, but for other situations like container bound scripts you might want to get into the habit of using the Deploy button to create a version.
This guide is not documentation, but a condensed reminder of what you must do.
Max Makhrov has put together this very succinct guide to publishing Google Workspace Editor Add-ons to the Google Workspace Marketplace. As explained by Max the guide isn’t a step-by-step tutorial, but instead provides the key steps you should remember to do. There are some great tips in this post, particularly around what to include in the recorded screencast required by the Google oAuth verification team.
