Exponential Backoff, a process by which when something fails you try again a set number of times whilst increasing the delay between each attempt.
Sample Apps Script code for Exponential Backoff
The following Google Apps Script is designed to explore Exponential Backoff – a process by which when something fails you try again a set number of times whilst increasing the delay between each attempt, up to a certain point.
I needed this for a tool I built which adds Guests to a Calendar event from a Google Form submission. Whilst I was using ScriptLock to prevent simultaneous submissions, the code ran so fast that it would infrequently trip the Calendar API with the following error message “API call to calendar.events.patch failed with error: Rate Limit Exceeded”.
By infrequently I mean a reported issue only once in 3,500 submissions over the course of 12 months. Enough however to take the opportunity to learn about Exponential Backoff and to squash that single instance.
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
Bluesky is gaining traction this end of 2024, and if you’re on the platform, you need to know your numbers. Want to track your Bluesky stats without the hassle? Google Apps Script and Google Sheets are here to rescue you with a simple, yet powerful solution.
In this blog post, Stéphane Giron provides a guide to tracking Bluesky social media statistics using Google Apps Script and Google Sheets. With the growing popularity of Bluesky it can be useful and interesting to understand the social media dynamic of the platform.
The script he offers tracks metrics such as the number of posts, followers, likes, reposts, and more. Stéphane also details how to install and set up the script, including copying and pasting the provided code, running initialization functions, and scheduling automatic data collection. The result is raw data that users can then use to create graphs and visualize their Bluesky activity over time.
Introduction Managing multiple calendars can be complex, particularly when it comes to accurately reflecting availability across different contexts—work, personal, and project-specific.
Use Cases
Multi-Calendar Management: Useful for managing both personal and work calendars, ensuring that colleagues or friends can see when you’re actually available.
Team Collaboration: Helps maintain clarity around availability in team environments.
Event Coordination: Assists in coordinating events across multiple calendars by providing a unified view of availability.
SyncFreeBusy is a handy solution published by Jasper Duizendstra designed to synchronize events between a primary and secondary Google Calendars. It can be used to provide a unified free/busy status across different calendars, preventing scheduling conflicts and reducing manual overhead.
The script works by fetching events from both calendars, creating corresponding “blocking events” in the other calendar to mark busy times, and periodically cleaning up obsolete events. The solution is achieved using Google Apps Script which can easily be setup to run on a timed trigger and also allows for easy customisation to your specific needs.
The script is available on GitHub for those interested in trying it out, just follow the source link below.
This is a Google Apps Script library including useful scripts for supporting to development of applications by Google Apps Script. In the current stage, the 3 categories “For array processing”, “For binary processing”, and “For string processing” are included in this library.
Google Apps Script developers often find themselves grappling with repetitive tasks particularly when handling Google Sheets data like array manipulation and A1 notation handling. Fortunately, this little versatile library from Kanshi Tanaike called UtlApp is here to help you simplify many of these common challenges.
Array Processing Made Easy
UtlApp includes a number of powerful array processing methods that can significantly streamline your code. Need to rearrange your data? The transpose method makes it effortless to flip your rows and columns. Want to extract specific data points? getSpecificColumns allows you to pinpoint and retrieve the exact columns you need. Dealing with large datasets? Quickly identify the first empty row or column using get1stEmptyRow and get1stEmptyColumn. And when it’s time to convert your array data into a more structured format, convArrayToObject can transform it into a JSON object, ready for further manipulation or integration.
Simplifying String Processing
UtlApp helps with common string processing tasks specifically for Google Sheets users. With columnLetterToIndex and columnIndexToLetter, you can convert between column letters and indices, making it simpler to work with spreadsheet data. UtlApp also offers convenient methods for managing A1Notations, such as consolidating scattered ranges using consolidateA1Notations or expanding them with expandA1Notations.
Handling Binary Data
UtlApp doesn’t stop at arrays and strings; it can also handle Blobs with the blobToDataUrl method. This function can convert Blob data into a convenient data URL format, making it suitable for embedding images or other binary content directly within HTML or CSS. This simplifies the process of working with Blobs in web-based Google Apps Script applications.
Effortless Integration
Adding UtlApp to your Google Apps Script project is a breeze! You have three convenient options: add the library directly using its project key, copy the individual script source files for array, binary, and string processing or copy individual functions into your project.
To find out more follow the source link to the GitHub repository.
Learn how to convert Google Slides into high-resolution PNG images using Google Apps Script. Choose between the Google Slides API and the Google Drive API based on your requirements.
Here’s a useful snippet from Amit Agarwal exploring two methods exploring how to convert Google Slides to PNG images with Google Apps Script. The first approach uses the Google Slides API to get the thumbnail images of the slides, fetch the blob of the image, and then upload it to Google Drive. However, this method has limitations including predefined fixed widths and requires two URL fetches.
The second approach uses the Google Drive API, which offers advantages like generating images with the same resolution as the original slide and requiring a single URL fetch. For both code snippets follow the source link.
Great news for fans of both Google Docs and Markdown! Google Docs recently acquired the ability to export documents directly into the markdown format.
This functionality extends beyond the user interface, with early indications suggesting the Google Drive API might also be capable of converting between Google Docs and Markdown. I confirmed that this could also be achieved by Drive API. This opens exciting possibilities for automated workflows.
Google recently announced in July 2024 that import and export Markdown in Google Docs. This is a user facing features, which Google announced includes the ability to:
Convert Markdown to Docs content on paste
Copy Docs content as Markdown
Export a Doc as Markdown (from File > Download)
Import Markdown as a Doc (from File > Open or “Open with Google Docs” from Drive)
Kanshi Tanaike hasn’t wasted any time in unpicking Markdown conversion capabilities using the Google Drive API. This functionality enables automated workflows for converting between Google Docs and Markdown. There are various scenarios where this can be useful, in particular, given GenAI solutions like the Gemini API often generate markdown there are opportunities to automatically convert these to Google Docs.
As part of the source post there are sample scripts: one for converting Google Docs to Markdown and another for converting Markdown to Google Docs.
For the Markdown to Google Docs it is assumed that there is already a Markdown file in Google Drive. If you have a Markdown text as a string, for example from a Gemini API response, then you can create a formatted Google Doc using the following snippet designed to be used with v3 of the Google Drive Advanced Service:
function sample3() {
// note string is tab sensitive (tabs are converted to code blocks on certain Workspace accounts)
const sampleText = `sample text 1
| a1 | b1 | c1 |
| :---- | :---- | :---- |
| a2 | b2 | c2 |
sample text 2
* sample option1
* sample option2
* sample option3
sample text 3`;
const blob = Utilities.newBlob(sampleText, 'text/markdown');
const fileMetadata = {
name: `Sample MD Conversion`,
mimeType: MimeType.GOOGLE_DOCS,
};
Drive.Files.create(fileMetadata, blob, { supportsAllDrives: true });
}
This post describes a Google Apps Script function to create a dummy folder structure in Google Drive, including nested folders and files (Documents, Sheets, Slides). It highlights some useful features beginners might find useful such as recursion, randomness, and the use of Google Apps Script services for Drive interactions. The post additionally discusses specific challenges encountered, like using Drive.Files.create for file creation and ensuring compatibility with Shared Drives.
Working with Google Workspace Enterprise customers we recommend using a DEV/Test Google Workspace domain. If you purchase your Workspace licences through a Google Partner you should be able to get a free test domain, which admins can use to test features like Data Loss Prevention, Context Aware-Access without fear of breaking things for your live domain.
Unfortunately there are no convenient settings or data duplication so configuring your test environment can be a challenge. To help create some dummy data with a little help from Gemini I was able to create the following createDummyFoldersWithNesting() function:
To use the script
Copy into the Apps Script Editor
Enable the Google Drive Advanced Service
Configure the settings inside the function for the root folder and the number of files/folders to generate
Gemini suggested I highlighted the following key points:
Recursion: The createNestedFolders function calls itself to create folders within folders.
Randomness: The script randomly determines the number of folders and files to create, and the types of files.
Google Apps Script Services: It uses DriveApp and Drive.Files to interact with Google Drive.
File Metadata: When creating files, it uses the mimeType property to specify the Google Apps Script file type.
Whilst Gemini wrote a lot of the code for me (with a little guidance), there were a couple of gotchas I’ll highlight.
Drive.Files.create instead of DriveApp.createFile(name, content, mimeType) – the current documentation would suggest that you can use the .createFile() method and include a MimeType like GOOGLE_SHEETS, but as explained in this Stackoverflow post Google have said “After extensive consideration, we have determined that DriveApp.createFile() should not be used to create MimeType.GOOGLE_* files.”
I could have asked Gemini to rewrite this to use DocumentApp, SpreadsheetApp or SlidesApp .create() methods e.g. SpreadsheetApp.create() but then I would have to move into a folder, use extra scopes, which all felt a bit messy so instead opted for Drive.Files.create.
Drive.Files.create supporting Shared Drives without a blob – when using Advanced Services there is a bit of cross referencing required between the auto-complete in the script editor and the associated API documentation. For my script I wanted to support creating files in Shared Drive. To do this requires adding the supportsAllDrivesas optionalArgs in the Drive.Files.create(resource, mediaData, optionalArgs) method. As I only wanted blank Docs, Sheets and Slides I was scratching my head as to what to include for the mediaData blob. Fortunately this issue was discussed in the Google Apps Script Community – Google Group and it was clear I could use null or undefined.
As a Developer Advocate for Google Workspace, I live and breathe productivity tools. But even the most tech-savvy among us can have frustratingly simple oversights. My recurring pain point? Forgetting to turn on my Gmail vacation responder to automatically notify people of my absence before heading out of office.
Google Workspace Developer Advocate, Chanel Greco, has shared a clever solution to automate their Gmail out-of-office notification using Google Apps Script. Faced with the recurring problem of forgetting to set up the out-of-office notification, Chanel crafted a script that does the job based on Google Calendar events marked as ‘outOfOffice’.
Chanel’s project not only solves a personal problem but also demonstrates the benefits of Google Apps Script for task customization and productivity. By harnessing the power of Workspace APIs, you can create innovative solutions to automate routine tasks and streamline workflows.
Welcome to the Essential Apps Script course. This guide is designed so you can work through an introductory course in Google Apps Script, designed so you do not need any knowledge of coding to get started.
As part of our latest episode of Totally Unscripted we chatted to Phil Bainbridge about the work he does at the University of York supporting and creating Google Apps Script projects.
One of the resources the University has developed is the Essential Apps Script course which is designed to lead beginners through the first steps in learning Google Apps Script. The guide is shared under a Creative Commons CC-BY-NC-SA which allows anyone to reuse course materials for non-commercial use if shared with the same licence and attributing the University of York.
This course is a perfect starting point for both beginners and those tasked with helping others learn Google Apps Script. Its step-by-step approach and beginner-friendly language make it easy for anyone with no prior coding experience to grasp the fundamentals. For those supporting others, the course provides a clear and structured resource to guide colleagues through the process, saving valuable time and ensuring everyone is on the same page from the beginning.
