I’ve created a small (pseudo) class that more easily clears and creates conditional formatting rules in a Google Sheet tab with Google Apps Script. Why? Well in Google Apps Script, conditional formatting rules are all or nothing. You can only ever set, get or clear ALL rules in a specified Google Sheet tab.
Conditional formatting in Google Sheets can be a powerful tool. However, Google Apps Script’s native setConditionalFormatRules() method replaces the entire set of rules, requiring the retrieval and reconstruction of the complete rule array to add, modify, or remove a single rule. This can be cumbersome and inefficient.
Fortunately Scott Donald has developed Range_ConditionalFormatting(), a valuable utility that simplifies conditional formatting management. This includes a SetRule method that allows you to add new rules without overwriting existing ones, and the position parameter provides precise control over rule order, which is crucial as conditional formatting rules are applied sequentially.
Scott’s post includes all the code you need to get this working in your own project along with a number of example snippets. This makes managing conditional formatting much more efficient and manageable.
Archive Google Sheet Data with Google Sheets API Advanced Service in Google Apps Script
Keeping your Google Sheets organised can be a chore. A recent tutorial by Scott Donald shows you how to automatically archive old rows based on multi-column parameters using Google Apps Script and the Advanced Sheets API, making data management easier and more efficient. This post highlights Scott’s approach to scripting a solution, including how to set up the Sheets API, the main components of the script, and potential applications.
As always Scott packs in a lot of useful tips and guidance; in particular, the tutorial highlights how to archive data using just five API calls, potentially making it faster than the standard SpreadsheetApp approach. Scott also details how to modify the script to suit different processes and includes details on his SsReq class. It provides a structured way to perform common operations like retrieving data, finding rows based on criteria, copying rows, appending rows, and removing rows, making it a great addition to your personal Apps Script toolbox.
For detailed explanations, code examples, and helpful tips, consult the complete tutorial.
4 Approaches to extracting the body text of a Google Doc with Google Apps Script. Full Code and tutorial
Scott Donald must be one of the most comprehensive Google Apps Script writers I know. All of his tutorials are packed with information and useful tips. This recent post is no exception, as Scott dives in and shares this detailed guide on retrieving a Google Docs body text using Google Apps Script.
The tutorial explores four approaches to extracting text from a Google Doc:
DocumentApp: This approach is straightforward for basic text extraction but may not capture all elements, especially “Smart Chips.”
DocumentApp with Element Iteration: This method allows for extracting text and URLs from standard text and supports some “Smart Chips” like Date, Rich Link, and Person.
OCR Approach: This involves converting the document to a PDF, applying OCR, and reading the extracted text. It captures most displayed text but may not recognise emojis or some formula symbols.
Docs API Advanced Service: This approach utilises the Docs REST API to access text, links, and specific “Smart Chip” data. It offers more control over data extraction but may require navigating complex JSON responses.
Scott’s tutorial provides a comprehensive and practical guide to retrieving Google Docs body text using Google Apps Script. Be sure to check out the full tutorial for detailed explanations, code examples, and helpful tips. And don’t forget to share your preferred approach and any challenges you’ve encountered on Scott’s post.
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.
Creating dynamic charts in the sidebar of your Google Workspace Add-on can be an effective approach to getting across a lot of meaning in a somewhat confined space. In this tutorial, we will use Google’s Chart API to generate a live chart, first from some static data and then live from an external data source like a Google Sheet. All with the help of a little Google Apps Script magic.
Here’s another great tutorial from Scott Donald which provides a detailed tutorial for including charts in a Google Workspace Add-on using the Card Service. The tutorial covers how you can embed dynamic chart data from a Google Sheet. Whilst targeted at Google Workspace Add-ons (Gmail and Drive) you should be able to easily modify this for cards used in Google Chat. The source tutorial includes a video giving an overview of the solution and if you want to take this project further you can signup for Scott’s Create and Publish Google Workspace Add-ons with Google Apps Script: Master Class.
Learn how to conduct a pirate raid in Google Chat Spaces with Webhooks and Google Apps Script. Video Included.
Google continue to add and enhance Google Chat. The fun doesn’t stop at the end user experience, Google also adding functionality to the Google Chat API. A low-lying entry point for Google Workspace developers is using Google Chat webhooks. As a HTTP endpoint you can use your coding language of choice.
In this tutorial from Scott Donald you can learn how Google Apps Script can be used to interface Google Chat using webhooks. The post includes everything you need to get started including a tutorial video. There are some nice tips including how to post replies to a chat thread and formatting responses using the card service. Follow the link to start learning more…
A fast approach to get all items in a directory tree in Google Drive with Google Apps Script. Contains video and edge case handling.
This is an excellent detailed step-by-step tutorial from Scott Donald all about how to efficiently list all files and folders in a folder’s directory tree in Google Drive using Google Apps Script. The in parents method is one we’ve covered before in Pulse, but Scott provides a very comprehensive explanation of what’s going on, plus all the code is available if you would like to reuse in your own projects.
Sending out emails as a part of a Google Workspace automated workflow is a very common task. In Google Apps Script we can send emails to users using the MailApp.sendEmail(), the GmailApp.sendEmail() method or even as a JSON payload with the Gmail Advanced API service.
While one might expect that the sender’s signature block would also be transmitted with the automated email, we find that this is not in fact the case.
Scott Donald highlights some of the pitfalls of trying to access the Gmail signatures using Google Apps Script. Fortunately Scott also shares a workaround for using a Gmail draft to store a number of signatures/snippets. This is all wrapped in a handy getSignatureBlock() function, created and shared by Scott to get either the user’s primary signature block or one stored in a draft.
Learn how to sort Google Sheet tabs in ascending and descending order with Google Apps Script Magic. Code and video inside!
Latest from Scott Donald, this time Scott is looking at Google Sheets tab sorting with Google Apps Script. Hepefully something for everyone in this post and for me it was learning about JavaScript’s Intl.Collator(), which allows language sensitive string comparisons.
Learn how the access the creator’s email of a Google Shared Drive with Google Apps Script using the Drive Activity API & Admin Directory SDK.
Scott Donald has found a clever way to get the Shared Drive creator using Apps Script and the Google Drive Activity API. Whilst there is a Google Drive ‘Drives’ endpoint the response doesn’t include the creator in the Drives Response object. This post is a nice example of how you can combine data from different Google Workspace APIs. Follow the source link for a detailed explanation.
