AppsScriptPulse

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

🔒Preventing Cross-Site Request Forgery (CSRF) in Google Apps Script Dialogs and Sidebars 

 

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.

Source: Preventing Cross-Site Request Forgery (CSRF) in Google Apps Script Dialogs and Sidebars – Yagisanatode

Memoization in Apps Script with Cache Service

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.

To help you see how memoization works here’s a gist for both pure JavaScript and Apps Script techniques which you can copy and run in the Apps Script Editor. The results hopefully speak for themselves:

First run (cold start) and second run (warm start) with better performance for cached results first call

Source: Memoization in Apps Script | Justin Poehnelt

How to Write to a JDBC Database with Google Apps Script: My Adventure with a Pesky Character Limit

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 😉

Source: How to Write to a JDBC Database with Google Apps Script: My Adventure with a Pesky Character Limit

[Official] Apps Script project history – New Feature overview!

We are excited to announce the general availability of project history for Apps Script! Find out how you can get started using this feature.

In Pulse we’ve previously featured the announcement that the new project history was rolling out to Google Apps Script. This feature should have finished following out and this video from the Google Workspace team covers how you can use project history in the Apps Script IDE. As well as the video there is also an update to the Google Apps Script Versions documentation page.

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.

Source: Apps Script project history – New Feature!

5 steps to deploying Google Workspace Editors Add-ons to the Google Workspace Marketplace

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.

Source: 5 STEPS to Deploy Google Sheets External Editors add-on

A Google Workspace Developer’s notes on publishing a Google Workspace Add-on to the Marketplace

 

This guide will walk you through creating a public Google Workspace Add-on, and launching it in the Google Workspace Marketplace for as free as possible. …

I’d never developed or published an add-on before. As I was looking into it, I realized that, while it is not super complicated, it is not readily obvious — especially for anyone just getting into add-on development. Google does have a guide on developing and publishing an add-on but it leaves a lot of unanswered questions.

So I thought I would put together what I learned in this guide — a playbook for anyone else who wants to develop their own add-on.

Following on from a recent Pulse post on How to publish to the Google Workspace Marketplace published by the Google Workspace team, here’s a developer’s take on the process from start to finish. As mentioned in the post the official support resources should be your start point, but these notes spotlight some of the nuances required to publish an add-on and the appendix includes some tips on naming your add-on and where/how to host required documentation including your add-on privacy policy.

Source: Developing a GAS Powered Google Workspace Add-on And Launching It To The Marketplace

Performance report for CacheService versus SpreadsheetApp for read/write in Google Apps Script

Image credit: Ignacio Lopezosa Serrano

For read-heavy applications that don’t involve components external to Apps Script accessing the cache and don’t exceed the CacheService limits, use CacheService. For write-heavy applications or for when some external parts require access to the same cache, use Spreadsheet App.

An interesting report from Ignacio Lopezosa Serrano on the relative performance of CacheService and SpreadsheetApp for reading/writing data with Google Apps Script. Some surprising results and something I think to be kept in mind is how these tests compare to ‘real world’ conditions. As also pointed out in the post there are some  service limitations of CacheService to keep in mind particularly around storage size limits.

Source: The use of Caches in Google Apps Script

Three pitfalls to avoid when using the onEdit trigger in Google Apps Script

Illustration by ahmiruddinhidayat111198 on freepik.com https://www.freepik.com/author/fahmiruddinhidayat111198

  1. Making a Single Function Do Everything
  2. Expecting onEdit to Catch All Changes by Default

Source: Three Pitfalls to Avoid When Using the onEdit Trigger in Google Apps Script

Fix Google Apps Script file order problems with Exports 

How to fix Apps Script file loading order and defintion visibility problems with an Exports object.

It’s good practice to keep class and namespace definitions in separate files and avoid defining functions or variables in the global space. However, App Script doesn’t give you control over the order in which it loads files. If you reference a class or a namespace from one script file, it may not yet be defined. This is where an Exports object comes in.

As your script projects get larger and you start splitting out across script files you may find you need a little more structure. Class and namespace definitions are a good way to structure your code. Even when you do this you can still encounter problems with parts of your script trying to run before they are fully loaded.

This was a particular issue when the V8 runtime launched in 2020. This was fixed in June 2022, but it can still be an issue depending on how declarations are made in your code. To find out more about why this happens and how to fix it this post by Bruce Mcpherson shows how an Exports object can be used to structure your code.

Source: Fix Apps Script file order problems with Exports – Desktop Liberation