This is a sample script for opening and closing Google Forms on time using Google Apps Script.
Here’s a handy little snippet if you would like to programmatically open/close one of your Google Forms to responses for specific hours of the day. The script includes another trigger that will repeat opening/closing the Google Form for responses each day. As this snippet uses
it’s easy for you to modify if you want to only have the form open to responses between two specific dates/times.
Retrieving and saving properties in Google Apps Script can be slow, especially if there are a lot of them. SpeedStore is a blazingly fast in memory properties store which you can use to make retrieving and saving properties much easier.
Continuing yesterday’s theme highlighting some of the components for developing a Google Workspace Add-on where John McGowan highlighted how Properties Service can be used to store and use a “licence” property to customise your add-on UI, we continue by looking at how you can handle property storage.
There have been a number of community contributions in this area such as Bruce Mcpherson’s bmCrusher. Another option is SpeedStore from Joshua Snyder. Not as feature filled as bmCrusher but the benefit is the library is more compact. SpeedStore still comes with some very useful features including automatically handling properties over 9kb and JSON encoding/decoding. Perhaps the biggest benefit is speed particularly when you are using a single store for all your properties.
These are the sample scripts for hiding and deleting rows and columns on Google Spreadsheet [sic] using Google Apps Script. I sometimes see the questions for hiding and deleting rows and columns on Spreadsheet at Stackoverflow. So here, I would like to introduce the sample scripts for this.
there is the .deleteRow() and .deleteRows(), but when you have a lot of data or non-consecutive rows it can really eat into your Apps Script runtime limit. In searching for a better way of doing this I found this great solution from Kanshi Tanaike on Stack Overflow which uses the Google Sheets Advanced Service to delete multiple rows using one API call!
isn’t just limited to deleting rows and for more examples see the source post which includes examples for hiding and deleting both rows and columns.
ChatGPT is pretty much a universal API: one endpoint to get any data.
ChatGPT has recently taken the world by storm. Unless you have been living under a rock, then you have certainly seen a variety of content on this topic. One of its greatest advantages is its ease of use, and it is perfectly usable in Google Apps Script. With that in mind, here is one use case that we can implement: generating fake data.
Fake data is useful for testing purposes. At times, real data is too sensitive, and sometimes you do not have access to real data before production, but you need to begin working with something. Here is where fake data comes in.
We used to have a great library for fake data called Faker.js; however, Marak, the author, became so frustrated that he could not monetize the solution that he pushed an update that broke everything, which led to him being blocked on GitHub and NPM. As a result, the library is no longer maintained. ChatGPT, however, is not going anywhere.
This is a sample script for merging multiple PDF files as a single PDF file using Google Apps Script. [and] This is a sample script for converting all pages in a PDF file to PNG images using Google Apps Script.
The solution uses
to load PDF-LIB, but with a minor modification hoisting the declaration of
you can also copy the source code into the script editor and avoid the evils of evals (a modified example here).
The Apps Script execution runtime limit will be a factor in the size of PDF Documents you can handle, but for smaller jobs a great solution to keep in mind.
Get security and peace of mind by backing up your Google Apps Script files to Google Docs and trigger for continuous backup.
We’ve featured a couple of methods for restoring Google Apps Script code in Pulse, but most of these rely on recovering a deployed version of the script. Stéphane Giron wields his Apps Script hammer and skilfully demonstrates how you can use Google Docs and it’s built-in version history as a way of keeping a backup copy of your code.
Illustration by ahmiruddinhidayat111198 on freepik.com https://www.freepik.com/author/fahmiruddinhidayat111198
trigger is likely the most used trigger in Google Apps Script. It runs automatically with an event object whenever you change a value in a spreadsheet — programmatic changes excluded — thus allowing you to execute a script based on context. When done properly, it can be extremely powerful. When done wrong, however, it can feel unreliable and messy.
In this article, we will learn to avoid three common pitfalls:
Not Exiting Early
Making a Single Function Do Everything
Expecting onEdit to Catch All Changes by Default
For this purpose, we will build a simple script to handle a task list in Google Sheets. It will do two things: add a checkbox next to new tasks and add a completion date when each task is checked as done.
Many developers want to offer specific, additional functionality for specific users. One very common scenario is to differenciate in users that pay for this functionality.
When you deliver an add-on, you want to know whether this user has this special access. We know this as a license.
In this blog, I will show you how we can let the add-on know what the user’s license is. We are going to do this by giving the user a key, where they can ‘unlock’ functionality by pasting this in their add-on.
Here’s an elegant solution from Riël Notermans (Zzapps) for letting users unlock premium functionality in your Workspace Add-on without relying on an external fetch or database by using an encrypted JWT web token.
You can include various data in the token and this example a subscription email and expiry is included which can be checked locally, for example, comparing the email from
There can be scenarios where you’d like to publish an Apps Script web app with ‘anyone can access’, but still provide a level of security. Here’s a nice example from Kanshi Tanaike where they use
to email the user a a time limited random passcode.
Magic links and passwordless login are used by a number of services, including Slack, and rather than including a password, as demonstrated in this example, it wouldn’t take much to turn this solution into a passwordless app. There are risks associated with email based authentication and if an attacker already has access to your email so other solutions are worth considering depending on the sensitivity of your web app.
Working around the 100-widget limit in Card Services, used when building Workspace Add-ons.
Like it says on the tin a way to display more than 100 items in a Google Workspace Add-on. The solution also has a nice UX friendly search feature to make it easier to find stuff in the UI. The source post from Sourabh Choraria includes additional information and links to the code on GitHub.
Have you ever wanted to convert currencies instantly while in Google Chat with colleagues and clients? In this tutorial, we are going to build a Currency Converter Google Chat App with Google Apps Script to do just that.
This tutorial is a multi-media series containing step-by-step video instructions for each stage of the process along with code snippets for each stage, important links and some further details on the more unusual parts of the code.
This is a great video series wrapped in a blog post, which highlights a Google Chat app development process. The particular project presented by Scott Donald is a currency converter but there are many repeatable aspects that would work in a wide range of Google Chat applications. The project is coded in Google Apps Script and the post includes plenty of snippets to help you understand various aspects of Google Chat app development including slash commands and information cards.
You’ve built a great add-on for the Google Workspace ecosystem (Google Docs, Google Sheets, Gmail & co.). It’s getting a lot of traction and you feel like you can monetize it. This is the guide I wish I had found a few years ago when I was researching how to monetize our own add-on for Google Sheets.
Here is a very concise and incredibly useful guide written by Corentin Brossault, co-founder and CTO at Mailmeteor, on the practical steps you can take if you are interested in monetizing your Google Apps Script solution. The post includes some code snippets and guidance on protecting personal information of your users, storing licence data in Firebase and integrating with Stripe as a payment processor.
I’ve been running this site for about 12 years ago. with over 1000 pages of content, here’s some of the high (and low) lights. I came to Apps Script not long after it was available, my first foray into it was probably around 2010, and I started writing about it not long afterwards.
I’m sure many Google Workspace developers are familiar with the work of Bruce Mcpherson. Regardless of whether or not you have, this is a nice summary of the last 12+ years of work published by Bruce last year but well worth revisiting. It covers everything from his move from VBA to focus on Apps Script, useful script libraries and code as well as explorations into other Google Cloud products.
Chances are that you may need to display user’s picture profiles in your applications. However, this API is part of the Directory API, which is part of Google Workspace Admin area. And chances are that your Organization has simply not giving you the rights to use Admin APIs
Sometimes within your Google Workspace domain you’d like to include account profile images. A number of Google Workspace APIs, like the Drive API, will include profile pictures in the response object. When this isn’t the case this post by Turhan Oz is a nice reminder that the dedicated People API can be used to get profile images. Click through to the source post to read more and sample code.
Sometimes, when using Salesforce, we come across the situation of needing to upload several object records at once, such as accounts, contacts, tasks, etc.
There are several ways to resolve this issue. One approach that can be efficient is through Google Sheets, especially if you want to “convert” each row in a table into separate records in Salesforce.
In the example of this article, I will talk about an implementation for bulk exporting of tasks using information contained in a table in Google Sheets using the Salesforce API.
This post on interacting with the Salesforce API highlights the tricky situation of handling different user credentials. The author comes up with a clever solution of using user owned Google Docs as a way to store different identities. Another approach is to use Google Apps Script’s Properties Service. The Properties Service includes an option to store User Properties which are only accessible to the user running the script. The downside of this approach is you then have to create UI for the user to input properties.