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.
Google Apps Script automates tasks like managing protections in Google Spreadsheets. These protections control user access to specific cells. While scripts exist for this purpose, users still encounter challenges, prompting this report. The report aims to introduce techniques for managing protections using sample scripts, helping users understand and implement this functionality.
Google Sheets aficionados are likely no strangers to the “Protect sheet” and “Protect range” options tucked away in the menus. These features offer a basic level of control over who can edit what within your spreadsheet. But what if you need more dynamic, automated control over these protections? That’s where the power of Google Apps Script and the Sheets API comes into play.
This post from Kanshi Tanaike provides a deep dive into how you can programmatically manage protections in your Google Sheets. While the traditional menu options are great for static scenarios, using Google Apps Script allows you to create more flexible and powerful protection workflows.
Why Go Script?
Dynamic Protections: Instead of manually adjusting protections, you can use scripts to change them based on specific conditions or events within your spreadsheet.
Automation: Integrate protection changes into larger automation workflows, streamlining processes and reducing manual intervention.
Granular Control: Achieve a level of control over cell-level permissions that goes beyond the standard menu options.
Some possible use cases for developers could include:
Approvals Automation: Imagine a scenario where certain parts of a spreadsheet need to be locked down once a manager approves them. With this solution, you could create a script that automatically protects those ranges upon approval.
Time-Limited Editing: Need to open up a section of a spreadsheet for editing only during a specific window of time? You could use Google Apps Script to handle this, automatically protecting
The scripts provided by Kanshi Tanaike offer a starting point for exploring these possibilities.
Automate publication of your script from development to production easier.
This post by Stéphane Giron provides a CI/CD solution for Google Apps Script, which makes it easy for you to automate the deployment of Apps Script code from a development version to production. The solution uses the Apps Script API to manage versions and streamline the code copying process.
Stéphane has previously created a backup solution for Google Apps Script code, but it lacked version control and multi-file support. This new CI/CD solution addresses these limitations by using the Apps Script API to handle version management and the publication process.
The post includes the necessary code and configuration for implementing this solution. To use this solution you will need to enable the Apps Script API and as well as setting up your project with a standard Google Cloud Platform (GCP) Project.
As a bonus the solution also allows you the option of restoring previous versions of your deployed script, providing a rollback mechanism in case of errors or issues. This solution is lighter weight than other CI/CD setups featured in Pulse, but is easier to set up, providing a practical approach to implementing CI/CD for Google Apps Script projects and enhancing code management and deployment processes.
Recently, I worked on a project with a client who needed to parse XML files and convert them to JSON. Simple enough, right? The catch? These XML files were massive — each containing hundreds or even thousands of trades. Naturally, the first thing I thought of was Google Apps Script’s built-in XMLService. It’s reliable, widely used, and integrates smoothly into Apps Script. But when I ran it on a file with 1,000 trades, the parsing took 45 seconds! That’s painfully slow for an automation process.
[Editor: In a recent blog post, Dmitry Kostyuk has shared a valuable tip for developers working with Google Apps Script. When faced with the task of parsing large XML files, he discovered that the built-in XMLService was causing significant slowdowns. For files containing thousands of records, the parsing process could take an agonizing 45 seconds.
Dmitry’s solution? Using his Apps Script Engine (ASE) and the xml-js NPM library. By making this switch, he was able to achieve a remarkable 10x increase in parsing speed, reducing the 45-second process to only 4 seconds.
If you’re working with Google Apps Script and large XML files, click the source post link to find out how you can give ASE and xml-js a try!]
Google Apps Script offers Document service for basic document tasks and Google Docs API for advanced control, requiring more technical expertise. This report bridges the gap with sample scripts to unlock the API’s potential.
Kanshi Tanaike’s latest blog post, “Unlocking Power: Leverage the Google Docs API Beyond Apps Script’s Document Service,” is a great read for Google Workspace developers. It offers a goldmine of code snippets that will supercharge your Google Docs projects. Tanaike highlights the advantages of using the Google Docs API for more advanced features and flexibility compared to the standard Apps Script DocumentApp Service. Even though the API requires a bit more technical know-how, Tanaike provides clear, practical examples to get you started.
Some of the sample snippets include:
Changing page orientation (portrait to landscape and vice versa)
Kanshi Tanaike’s post serves as a valuable resource for developers looking to expand their Google Docs capabilities. Hopefully with these sample scripts, you can start unlocking new levels of automation and customization in your Google Doc projects.
To delve deeper into the world of Google Docs API, follow the source link post.
We’re all used to dealing with iterables such as Arrays, Maps and Sets. Creating a collection of input items, transforming then outputting is what we do all the time. However, sometimes that’s not possible – for example, where we have a potentially large input data set of unknown size that would be too big to fit in memory, or perhaps an output method with rate limits. Dealing with items 1 by 1, despite being by far the simplest to code, is often the least efficient to execute – so we use a paging method to get an input chunk at a time, and a bulk method to flush out the transformed results.
All of that can get a bit tricky, especially if you add asynchronicity into the mix.The use of iterators and generators can make coding as simple as 1 in -> 1 out, while handling chunking and bulking automagically. In this article, we’ll look at how to implement these.
In this blog post, Bruce Mcpherson explores the use of iterators and generators in JavaScript and how these can be used in Google Apps Script. A generator is a special type of function that can be paused and resumed at any point, yielding values one at a time. This is unlike regular functions that return all their results at once. Generators can be useful for handling large datasets efficiently by processing items in chunks and outputting results in bulk, even when dealing with asynchronous operations.
While Apps Script doesn’t natively support generators, Bruce has found a handy converter published by Facebook to make them compatible. As part of the post Bruce includes some Apps Script samples you can test and modify, demonstrating how to apply these techniques in real-world scenarios, even within the limitations of the platform. Bruce provides clear explanations of these concepts, illustrated with practical examples like finding breweries near specific locations and well worth spending the time to digest.
Quickly append new permissions to existing files within Google Drive. Insert email addresses using a comma-space format with optional notifications.
Quickly append new file permissions via a Google Sheet
The following Google Apps Script tool allows you to quickly append new permissions to existing files within Google/Shared drive. This is quite a niche tool so it is anticipated you would already have a Google Sheet of file IDs/URLs, though there is the option to select an existing Drive folder of files and automatically bring them into this tool.
By inserting email addresses using a comma-and-space format you can optionally select to send a notification email to the new user(s)
You can continue to append further rows to the Google Sheet as existing ones will be skipped once the ‘Status’ column has automatically been marked as ‘Completed’. Alternatively you can clear the Sheet each time for a fresh start.
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
Learn how to easily export your WooCommerce customers email, name and address to Google Sheets using Google Apps Script. The script will create a new tab in your Google Sheet and copy the data from the WooCommerce customers table.
If you are a WooCommerce store owner you might be interested in this solution by Amit Agarwal who provides a step-by-step guide on how to export WooCommerce customer data (email, name, address) to Google Sheets using Google Apps Script.
No more manual CSV exports or complex integrations, simply copy the provided script and follow the setup instructions. With this solution you can enhance your workflows, gain insights, and enhance your customer outreach. If you are not an existing WooCommerce user a solution for developers to keep in mind if you are seeking to optimise your Google Workspace add-ons customer experience.
Enhance your Google Sheets skills with this Visual Vocabulary template. Learn how to choose the right charts, create effective visualizations, and design professional-looking dashboards. This comprehensive guide includes 31 common chart types, tips for data organization, and step-by-step instructions for using the SUBTOTAL formula to create interactive “Show/Hide” rows. Perfect for data enthusiasts and spreadsheet users of all levels!
Friend and fellow GDE, Ben Collins, has recently announced a new ‘Sheets Insiders’ membership program where he’ll be sharing some exclusive members-only newsletters, new templates, deep-dive tutorials, and more for both Google Sheets and Apps Script.
If you would like to ‘try before you buy’ Ben has shared the first issue of the Sheets Insiders membership program, featuring a Visual Vocabulary template for Google Sheets. The template is designed to help you in chart selection and the accompanying videos include lots of great tips to help you cleanly format your spreadsheets.
Future Sheets Insiders issues will delve into chart tricks, dropdown menus, interactive elements, AI and Sheets integration, formula challenges, and Apps Script content. The newsletter content will be archived in the Sheets Insiders Content Library for future reference. Click through to read more about Sheets Insiders and get your copy of the Google Sheets Visual Vocabulary Template.
