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.
During the summer I was asked to help with some video recording for a rugby tournament, but rather than spending hours rewatching footage to find highlights, I quickly threw together some tech to make things a lot easier.
A slick solution that uses a AppSheet app for data collection with Apps Script to cue up video files hosted on Google Drive. The Apps Script code is hosted on GitHub if you’d like to take a peak. Hopefully this example gives you inspiration for your own little projects.
Search a date range in Google Calendar and match any events with a given string, then delete those events.
Search for and delete Google Calendar events
The following Google Apps Script is designed to search a date range for Google Calendar events containing a given string (something to help target those events) and then delete them.
There are 4 items at the beginning of the script to complete for your requirements:
Start date – format mm/dd/yyyy – forms the date range to look for events within.
End date – format mm/dd/yyyy – forms the date range to look for events within.
Search string – bit of text that is unique to the events you wish to delete to differentiate them from other Calendar events you may not wish to remove. Note: is not case-sensitive and will not pick-up search string inside of another word eg ‘the’ in ‘there’.
Calendar ID – typically your email address for your personal Google Calendar to search for events in.
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
Google Docs is a popular document editor for creating and editing text documents, spreadsheets, and presentations. With the use of Google Apps Script, a scripting language for automating tasks in Google Apps, users can enhance their document editing experience by adding custom functionality to Google Docs. One such functionality is the ability to check URLs within a Google Document.
Nice little project that has some useful code snippets/functionality you might find useful in your own projects which extracts and tests hyperlinks included in a Google Doc. The post provides an outline of how the script is setup and lets the user interact via a sidebar. I always find interacting with Google Docs a little daunting, particular parsing the document structure. The post links to a useful gist by Mogsdad (David Bingham) with some basic Google Document utility scripts.
In this post, we’re going to look at how we can easily combine arrays of data on separate sheets in a Google Sheet and add them on one single sheet. We’re going to use the relatively new spread operator (…) to do this.
A little knowledge about data manipulation can get you a long way, particularly if you are doing lots of work in Google Sheets. This post from Baz Roberts is an opportunity to learn about spread syntax (also known as the spread operator) in arrays. Have a read and hopefully you’ll see the benefits.
One model of monetization for a Google Addon is to allow a certain number of free uses before restricting that feature. This post shows one way to restrict a feature in a Google Editor Addon sidebar.
John McGowan is continuing his Google Workspace Add-on development tips at pace. You can read the story so far on the Automagical Apps Blog. In the latest post you can find out how John uses the Properties Service, to record the number of times an account has used a feature in your add-on by communicating between the sidebar and Apps Script using google.script.run. A reminder as well that you can see how you can boost User Property read/write with the SpeedStore library.
I always get requests as to how people can add a license to their Google Add-on. There are a few different steps and here I will show how you can share different information in the Sidebar based on a license status.
In Pulse we’ve previously featured a couple of community contributions on how to monetize your Google Workspace Add-On. Corentin Brossault’s How to monetize your Google Workspace add-on? provides some great code snippets and tips for handling user authentication and payment. We’ve also featured Riël Noterman’s solution for Using JWT as a license key in Google Apps Script Google Workspace Add-ons. This related post from John McGowan (Automagical Apps) provides another piece to the puzzle demonstrating how you can use templates in HTMLService to switch user messaging based on stored user properties.
Validate addresses using Google Maps Address Validation and Apps Script. Discover onleeaddress the add-on for Google Workspace.
I missed the announcement in November 2022 that there is a new Google Maps Address Validation API. I also missed this post from Stéphane Giron showing how you can use the Address Validation API in Google Apps Script.
The concept is simple, you provide the Address Validation API with what you think is a correct address and the API returns information on each component of the address and additional metadata. Visit the source post for more details on setup and some example code.
Learn how to search and find all values in an entire Google Sheet spreadsheet, in a specific sheet tab or in a range with Apps Script.
Handy little snippet from Scott Donald on using the Google Apps Script TextFinder class to find all the occurrences of matching text in a Google Sheet. The bit I particularly liked in this solution was how Scott used a .map method to return an object array of cell hits including A1 notation:
Array containing an object for each sheet containing the sheet name and the cell location (Image credit: Scott Donald)
Follow the source link for more information including a video tutorial.
