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.
If you have ever worked with Google Apps Script (GAS), you may have found yourself in this situation. You want to move some files from some Google service to another, e.g., from Drive to a Google Cloud Storage Bucket. You get the file’s contents using GAS’s built-in service in the form of a Byte array. You pass this byte array to the URLFetchApp.fetch() function in the body.
All is going good and well, but then you notice. Why is it taking 30 seconds to upload a 10MB file? Am I doing something wrong? Is GAS not meant for this?
Don’t panic. I’ve been there, and I decided to take a closer look. Here’s what I found.
In this article Ignacio Lopezosa takes a deep dive into how to handle bytes in Google Apps Script, sharing his journey in finding a solution on how to share (large) files.
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
When I create some applications using Google Drive, there are often the case which is required to retrieve the file list and folder list. I had prepared the script each time for each case so far. But recently, I thought that if there is a library for retrieving the file and folder list (as a tree), it will be useful for me and other developers.
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.
There’s a long standing issue with triggers installed programmatically by other triggers, that was kind of fixed last year, but users are still reporting problems, at least in certain locations (Japan, India and others). The issue in short: triggers installed programmatically would not be created, or fail systematically.
In many cases this affects add-ons (and, to be noted, the issue still persists when testing them, see Testing details); I had a slightly different scenario: a webapp that, when executed (doGet), would install a time based trigger for the user executing the script.
This article provides two solutions to an annoying issue and unlocks many possibilities for programmatically installed triggers.
Image credit: Martin Hawksey (with the help of DALL·E 2)
Think 10x — supercharging your Google Apps Script solutions by directly calling Google Workspace Enterprise APIs.
The Google Apps Script built-in services like SpreadsheetApp, Maps and GmailApp are a great onramp for users with limited coding experience, the flip side is you can find yourself easily getting results but not in the most efficient way. DriveApp is a great example where Google have made it easy to iterate across folders and files, but when you have lots of folders and files it becomes a time consuming process and you hit execution limits.
An alternative approach is using Google Apps Script to make direct calls to the Drive API. The benefits of this approach is you can be more specific in the data you want back and it gives more flexibility with how you call the API, in some cases with the ability to make batch or asynchronous processes.
In this post I highlight a method ideal for scenarios when you want to index larger volumes of My Drive files and folders to a Google Sheet with calls directly to the Drive API. The post includes some sample code you can use which instead is able to reduce a 4 minute runtime to index 10,000 files and folders to one that can complete in under 40 seconds!
This is a sample script for putting the values of all Spreadsheets in a folder to the master Spreadsheet with a low process cost using Google Apps Script.
Some clever scripting from Kanshi Tanaike to combine individual Google Sheets in a Google Drive folder into a single master sheet. To achieve this the solution uses the Google Sheets Advanced Service combined with UrlFetchApp.fetchAll() to asynchronous process the source Google Sheets. It’s reported that this method was able to process 50 source sheets in 10 seconds!!! There are some limitations to be aware of highlighted in the source post, but for a method to quickly process a lot of data this solution is definitely one to keep in mind.
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.
