AppsScriptPulse

Community News

Totally Unscripted: “The Answer is Always a Spreadsheet” featuring Ben Collins 06 March 2024 at 1200 PT / 1500 ET / 2000 GMT

Tags:
| | 0 Comments |

Whether you are managing projects, calculating budgets, forecasting sales, or just tracking your own to-do lists, there is almost always a spreadsheet involved.

In this episode of Totally Unscripted, we get back to basics and discuss the versatility and pure power (and joy) of spreadsheets! Joining us live is guest Ben Collins, who is a renowned spreadsheet educator, Google Developer Expert and Google Sheets master, and someone who has earned the honorary title of “King of Functions”. Ben will talk and answer your questions about trends, best practices, and of course demonstrate a few of his favourite functions that will confirm that “The Answer is Always a Spreadsheet” still rings true!

As always the show is broadcast live and you can ask questions via the YouTube chat, but also feel free to get your questions in early using the comments.

Show Live/Recording link – https://www.youtube.com/watch?v=HxoTBH4OTtY

Solutions, Chat, Advanced

“going beyond basic bots” – Tutorial: managing projects with Google Chat, Vertex AI, and Firestore  

Tags:
| | 0 Comments |

Image credit: CC-BY Google

This tutorial shows how to make a Google Chat app that a team can use to manage projects in real time. The Chat app uses Vertex AI to help teams write user stories (which represent features of a software system from the point of view of a user for the team to develop) and persists the stories in a Firestore database.

A recent episode of Totally Unscripted delved into “going beyond basic bots”, highlighting a couple of Google Chat app tutorials from the Google Developer documentation. One example, the “project management” Chat App, is worth mentioning in a Pulse post.

While this example uses Google Cloud Functions instead of Google Apps Script, as discussed in the episode, both approaches share many similarities. Deploying the project management app involves several steps, but I believe it’s a worthwhile investment to learn how to combine different solutions for building Google Workspace integrations. For developers seeking to expand their Google Workspace Add-on capabilities, this example serves as a valuable reference.

If you’re interested in using Firestore for data management but prefer Google Apps Script, Justin Poehnelt’s post on “Using Firestore in Apps Script.” is a great resource. This post provides a basic Firestore wrapper and links to other relevant Apps Script/Firestore libraries.

Source: Manage projects with Google Chat, Vertex AI, and Firestore  |  Google for Developers

Official News

Google Apps Script is down including Google Workspace Editor Add-ons!!! Updated 2024-02-26 17:28GMT

Tags:
| | 0 Comments |

Script editor error message

Update 2024-02-26 17:28GMT – Latest update from Google “We believe the issue is mostly resolved for impacted services and our engineers are continuing to work on preventing similar issues in the future.”

Update 2024-02-26 15:00GMT – Still reports of issues for some users – “Our engineering team continues to investigate the issue. We will provide an update by Monday, 2024-02-26 08:00 US/Pacific with current details.” Updates are being posted to Google Cloud Support Portal

Update 2024-02-26 13:00GMT – Service being restored – many users are reporting all Apps Script services as being restored.

I’m sure the majority of Google Apps Script developers don’t need me to tell them that Google Apps Script is currently down. The outage is impacting multiple areas including the availability of the Script Editor, script executions and Google Workspace Editor Add-ons.

Issues were first reported in the public issue tracker at Feb 26, 2024 08:27AM – the ticket has been marked as P0. Please use the star/’Me too!’ button on the ticket to subscribe to updates (please avoid commenting on the ticket to make it easier for us all to track updates from Google).

Solutions, Drive, Sheets, Intermediate

How to audit Google Shared Drive permissions in Google Sheets with Apps Script

Tags:
| | 0 Comments |

Are you looking for an efficient way to get an overview of all shared drives and their access permissions within your organization? Whether you’re navigating a company reorganization or implementing security procedures, accessing this information can be challenging.

This post from Niek Waarbroek highlights the important of auditing Google Shared Drive permissions. This can be a challenge to do using the Google Workspace Admin Console, especially if you have lots of Shared Drives.

To help Niek has shared a Google Sheet with Apps Script code that automatically generates a list of all shared drives and their associated root level permissions.

Shared Drive auditing is bit of a niche subject, but I encourage you to have a look at the post and script as it has some nice features which could be applicable to other projects. For example, there is a gaspTimeManager to make sure the script doesn’t go beyond the script execution limit.

Source: How to audit shared drive permissions in Google Drive

Add-on, Gotchas, Intermediate

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

Tags:
| | 0 Comments |

 

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

Solutions, Cloud Storage, Drive, Intermediate

Managing Google Cloud Storage files with Google Apps Script without using a service account

Tags:
| | 0 Comments |

Using Google Apps Script scoped authentication to interact with Google Cloud resources without a service account

A key feature of Google Apps Script is its integration into Google Cloud. The default behaviour when any Apps Script project is created is that an associated Google Cloud project is created and configured. This default project is not accessible to the user and for most scripts, the user doesn’t need to worry about any of the configurations such as enabling APIs and configuring authentication settings.

Other key aspects are identity and authentication. The default behaviour for scripts is usually to run as the account executing the script, Apps Script automatically determining what authorisation is required for different Google services based on an automatic scan of your code or from what scopes have been set explicitly in the Apps Script manifest file.

The last piece in the puzzle is the .getOAuthToken() method which is part of the ScriptApp Service:

Gets the OAuth 2.0 access token for the effective user. … The token returned by this method only includes scopes that the script currently needs. Scopes that were previously authorized but are no longer used by the script are not included in the returned token. If additional OAuth scopes are needed beyond what the script itself requires, they can be specified in the script’s manifest file.

What this means is in script projects we can borrow an access token to use other services that the effective user has access to and have been declared in the script project scopes. For example, if my Google account [email protected] has been added to another Google Cloud project with the Google Cloud Storage service enabled, I can use Apps Script to generate a token to use the Cloud Storage service in that project.

To help illustrate this, here are two examples for interacting with Google Cloud Storage buckets to upload and download files to Google Drive.

Source: Managing Google Cloud Storage files with Google Apps Script without using a service account

Community News

Totally Unscripted: Beyond Basic Bots – Building Intelligent Assistants with Google Chat and Vertex AI 21 February, 2024 at 1200 PT / 1500 ET / 2000 GMT

Tags:
| | 0 Comments |

Totally Unscripted returns on 21 February 2024 at at 1200 PT / 1500 ET / 2000 GMT with an episode exploring how you can unleash the power of intelligent assistants and apps in Google Chat.

Say goodbye to simple chatbots and hello to intelligent assistants and apps! On 21 February 2024 at at 1200 PT / 1500 ET / 2000 GMT on Totally Unscripted, we dive into the world of building advanced enterprise apps and assistants using interactive dialogs and Vertex AI in Google Chat.

As part of this, we will be exploring how you can harness the capabilities of the Google Chat Platform to promote collaborative work within Chat spaces, taking advantage of Google’s advanced machine learning models to create assistants that can help with complex tasks, such as summarizing conversations and generating complete task descriptions from simple titles.

Get ready to go beyond the basics and learn how to build sophisticated assistants that help you get more work done, collaborate with your team, and revolutionize the way you work with Google Chat and Google Workspace.

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.

Show Live/Recording link – https://www.youtube.com/watch?v=UGwj4dVTe4A

Official News

Google Workspace Developer News: Import data into Google Chat, more events supported for Events API, and more

Tags:
| | 0 Comments |

Welcome to the third edition of the Google Workspace Developer News! Find out what’s new on the Google Workspace Platform.

Another great summary of Google Workspace Developer News put together by Chanel Greco from the Google Workspace DevRel team. The update includes information on:

Google Chat Enhancements

  • Import data from other messaging platforms, including historical memberships.
  • Send private messages in spaces with multiple people.
  • Add interactive buttons to messages.

Google Workspace Events API Updates

  • Subscriptions to Google Chat users now supported.
  • Lifecycle events sent when subscriptions expire.
  • Get() method added to Operations resource.

Google Workspace Add-ons

  • Link previews now supported in Google Sheets and Slides.
  • Third-party resource creation from the @ menu in Google Docs.

Other Updates

  • Drive file and folder storage limits increased to 500 million items per user.
  • Google Drive API v3 now provides information on installed apps.
  • Reports API event payload filtered to specified event name.
  • Chrome phasing out third-party cookies for enhanced privacy.

For a longer discussion and demos for link previews and third-party resources check out Totally Unscripted 5.3: Google Workspace Development: Now with a Side of Smart Chips!

Source: Import data into Google Chat, more events supported for Events API, and more

 

Add-on, Library, Drive, Intermediate

ReDriveApp: A new Google Apps Script library to replace DriveApp and restricted scopes 

Tags:
| | 0 Comments |

Apps Script class that provides equivalent methods offered by the built-in DriveApp, but that does not require use of full ‘/drive’ OAuth scope (which is a “Restricted” scope”). Instead, uses only these Recommended (non-sensitive) and/or Sensitive scopes

When you use OAuth 2.0 to get permission from a Google Account to access their data, you use strings called scopes to specify the type of data you want to access on their behalf. For Google Workspace Add-on developers wanting to distribute your app in the Google Workspace Marketplace one consideration is only using the scopes required for your app. In the case of Google Drive a number of the scopes are classified by Google as “restricted”. To use “restricted” scopes there is an enhanced verification process, which requires Cloud Application Security Assessment (CASA) by an independent security assessor, which come at a cost and can be time consuming.

ReDriveApp is a new Apps Script community library published by Dave Abouav which makes it easier for developers to use reduced recommended scopes for integrating with Google Drive. As explained by Dave:

The built-in DriveApp service is an easy and powerful way to interact with Google Drive in your Google Apps Script projects, which is why so many developers make use of it. It’s one downside is that it forces your project to use the full ‘/drive’ OAuth scope, which is a “Restricted” scope.

In many cases though, projects don’t really need the full /drive scope for common tasks, and the Recommended /drive.file scope is sufficient. This allows your project to create new files, and open existing files if authorized by the end-user via the Drive Picker. Using it also avoids the aforementioned security review, and is less alarming to users when authorizing your app.

ReDriveApp is still work-in-progress and not an official Google project. There are a number of methods that still need to be implemented, but the project is open source and can be contributed to on GitHub. If you are planning or have already developed a Marketplace Add-on that has stalled due to enhanced verification for restricted scopes it could be worth looking at and contributing to this library.

Source: GitHub – ReDriveApp

Gotchas, Solutions, Advanced

Memoization in Apps Script with Cache Service

Tags:
| | 0 Comments |

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