Shared cached data between multiple scripts with this enhanced Apps Script Cacheservice library
Bruce Mcpherson has shared a new Apps Script library, bmCacheSharer, which enables sharing of cache data between multiple Google Workspace projects. The library was originally created to solve the problem of sharing configuration data stored in a Google Sheet with multiple script projects.
The library includes a number of nice features to overcome some of the limitations of the Apps Script CacheService, such as the 100k maximum item size and the 6-hour expiration limit by incorporating features like compression, key digestion, and automatic refresh.
It also provides options for sharing cached values by using community keys. The library is designed to be flexible, allowing users to either use its built-in cache service or provide their own. Additionally, it offers a memory cache for faster access within the same script instance. The document includes code examples and explanations to illustrate the library’s usage and benefits.
Google Workspace Add-on for Google Drive, which uses AI to recommend new names for the selected Doc in Google Drive by passing the body of the document within the AI prompt for context.
Tired of staring at “Untitled document” in Google Drive? At Google Cloud Next ’24 and I/O 2024, the Google Workspace DevRel team showcased new Apps Script samples utilizing the Gemini API for AI-powered functionality.
One such sample, “Name with Intelligence” by Charles Maxson, helps developers overcome this common hurdle. This Google Drive add-on leverages the Gemini API to suggest relevant titles for your documents, saving you valuable time and effort.
This innovative Google Drive add-on tackles the common struggle of naming untitled documents. It utilizes the Gemini API to analyse the content of your Google Doc and suggest relevant titles based on its understanding of the text. This not only saves you time brainstorming titles, but also ensures your documents are clearly named for easy searchability later.
For those interested in my GeminiApp library, you can also find the sample here.
Take Your Development Further
This is just a glimpse of what’s possible with the Gemini API and Apps Script. With a little creativity, you can develop your own AI-powered Google Workspace add-ons to streamline your workflows and boost your productivity.
I encourage you to experiment and create innovative solutions that enhance your Google Workspace experience!
A comparison of key-value store options in Google Apps Script, including PropertiesService, CacheService, Firestore, and Sheet Developer Metadata.
Justin Poehnelt has shared a comprehensive look at key-value store options in Google Apps Script. Key-value stores are useful for storing data like user preferences or frequently accessed data, which can also be shared between script executions.
As part of Justin’s post as well as looking at the built-in store options, PropertiesService and CacheService, there is also a look at scenarios where you might want to consider alternatives like Firestore.
The choice of key-value store depends on factors like expiration needs, number of items, value size, access control requirements, latency sensitivity, and cost considerations. Justin’s post provides a thorough analysis of each factor, including latency comparisons.
Head over to the source link to get all the details!
Use Google Apps Script to create a custom menu that will work inside Google Sheets, Google Docs, Slides and Google Forms.
Tired of writing separate code for custom menus in Docs, Sheets, Slides, and Forms? This nifty Google Apps Script snippet by Amit Agarwal helps you build a universal menu that works across all these apps!
The solution is a small snippet which detects the context to see which of DocumentApp, SpreadsheetApp, FormApp, SlidesApp are available which then lets you switch to get the appropriate .getUI() method.
Check out the source post for the full code and streamline your add-on development!
Learn how to move uploaded files from Google Forms to a specific folder in Google Drive. You can also rename the files based on the form responses
Amit Agarwal, the brains behind Digital Inspiration’s Document Studio must have one of the most feature full Google Workspace Marketplace Add-ons. I also like Amit’s approach of sharing how you can solve problems like moving Google Form file uploads using Document Studio as well as providing an Apps Script snippet if you would like to manually code it.
In this example some setup is required to specify a folder ID and create an onFormSubmit trigger. After this the script then retrieves uploaded files from the form response, creates a subfolder named after the unique Response ID, and moves the files into that subfolder within the specified parent folder. This provides a structured and organized way to manage file uploads from Google Forms.
Follow the source link to read more about this script solution and about Document Studio.
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.
Template for a workflow to track tasks that follow steps. It can alert on due and expired steps per each task, while also navigating to alerted cells with a single click. It also demonstrates the use of conditional rules and ARRAYFORMULA.
Sig Mandel has created this clever Google Apps Script solution which might be suitable for anyone struggling to manage tasks and deadlines in Google Sheets. This template lets you easily configure overdue and upcoming deadlines, letting you jump directly to problem areas with a click. It also cleverly uses conditional formatting and ARRAYFORMULA to streamline your task tracking.
You can use the provided Google Sheets template as a starting point and add your workflow steps as columns, input your tasks, and the sheet takes care of the rest. You can also use a number of other features including real-time “toast” messages, a dynamic installation menu for easy setup, and optimized code for smooth performance.
All the Google Apps Script related github activity is now on Google Bigquery and Google Looker. Here’s how it was done.
Bruce Mcpherson has recently revisited his work on the visual Google Apps Script explorer tool, scrviz. Bruce has been working diligently to address limitations with the Github API and overcome quota restrictions. This new iteration still allows users to explore Google Apps Script projects and creators, but this time through a shared Looker Studio dashboard. The underlying dataset has also been made available on BigQuery if you would like to do your own analysis.
Follow the source link to find out more and explore the data!
This detailed guide explains how you access your passwords, API keys and other sensitive data stored in the Google Secret Manager with Google Apps Script.
API keys and other credentials are your digital passports to access valuable data and services from various online platforms. When you’re developing Google Apps Script projects that connect to using keys, it’s of paramount importance to handle these keys with care and prevent them from falling into the wrong hands. Mishandled API keys can lead to unauthorized access, data breaches, and potentially even financial consequences.
One way to store API keys in Google Apps Script is the built-in Properties Service. This service offers a mechanism to store key-value pairs attached to your Apps Script project. While the Properties Service offers a convenient way to store sensitive data, it comes with limitations:
Not True Encryption: The Properties Service stores data in a readable format. Anyone with sufficient access to your Apps Script project might be able to view the API keys.
Limited Access Control: It’s challenging to implement fine-grained access restrictions over who can see the stored keys within the Properties Service.
For a more secure and scalable approach you might consider using Google Cloud’s Secret Manager. As described by Amit Agarwal in this source post:
Google Secret Manager is a cloud service where you can store sensitive data such as passwords, database credentials, encryption keys or any other confidential information that you don’t want to hardcode in your application’s source code. You can also set up an expiration time for the secret and the Google Secret Manager will automatically delete the secret after the specified time.
You can read more about using the Google Cloud Secret Manager in Amit post, which also includes the setup and Apps Script code snippets to make it work. For some general best practices on key management, Google provide this support article.
In this blog we are going to find out who exactly has access to my Google Drive files, be it a Google Sheet, Google Doc, Form and more. To do this we are going to be using the DriveApp and Google Apps Script.
Recently Aryan Irani shared this post which shows how you can get the file permissions on a Google Drive file using DriveApp. This uses the DriveApp methods for .getEditors()and .getViewers(), which left me wondering about commenters???
The answer is file commenters are included in the .getViewers() response and as pointed out by TheMaster you can filter out commenters with .getAccess().
Another approach is to use the Advanced Drive Service:
The advanced Drive service lets you use the Google Drive API in Apps Script. Much like Apps Script’s built-in Drive service, this API allows scripts to create, find, and modify files and folders in Google Drive. In most cases, the built-in service is easier to use, but this advanced service provides a few extra features, including access to custom file properties as well as revisions for files and folders.
Learning about the Advanced Drive Service can be useful as it open up more opportunities to interact with Google Drive content and can also help you get file properties.
In the case of permissions there is a dedicated Permissions Resource that allows access to all the file permissions. For example, if I wanted to see what accounts had access to a file in MyDrive you can use:
Using the Advanced Drive Service does require a step up in understanding how to call the Drive API and the response you get but once you begin understanding it can come with huge benefits with more efficient code.
