AppsScriptPulse

Opening and closing Google Forms on time trigger using Google Apps Script

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 .timeBased().at(date) it’s easy for you to modify if you want to only have the form open to responses between two specific dates/times.

Source: Opening and Closing Google Forms on Time using Google Apps Script

SpeedStore: Blazingly fast Properties storage for Google Apps Script ⚡

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.

Source: GitHub – joshsny/SpeedStore: Blazingly fast Properties storage for Google Apps Script ⚡

Hiding/deleting non-consecutive rows and columns in Google Sheets using Google Apps Script

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.

In SpreadsheetApp 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!

Using Sheets.Spreadsheets.batchUpdate 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.

Source: Hiding and Deleting Rows and Columns on Google Spreadsheet using Google Apps Script

Using ChatGPT to Generate Fake Data in Your Spreadsheet with Google Apps Script

ChatGPT is pretty much a universal API: one endpoint to get any 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.

Source: Using ChatGPT to Generate Fake Data in Your Spreadsheet with Google Apps Script

Merging multiple PDF files as a single PDF and converting all the pages in a PDF to PNG images using Google Apps Script

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.

Kanshi Tanaike has recently been exploring and sharing some Apps Script solutions for handling PDF Documents using the PDF-LIB JavaScript library. So far they have looked at merging PDF files as well as converting PDF pages into PNG images.

The solution uses fetch and eval to load PDF-LIB, but with a minor modification hoisting the declaration of setTimeout 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.

Source: Merging Multiple PDF Files as a Single PDF File using Google Apps Script and Converting All Pages in PDF File to PNG Images using Google Apps Script

Quick backup solution for Google Apps Script by creating versions in Google Docs

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.

Source: Quick and not so Dirty backup solution for Google Apps Script Code

Three pitfalls to avoid when using the onEdit trigger in Google Apps Script

Illustration by ahmiruddinhidayat111198 on freepik.com https://www.freepik.com/author/fahmiruddinhidayat111198

  1. Making a Single Function Do Everything
  2. Expecting onEdit to Catch All Changes by Default

Source: Three Pitfalls to Avoid When Using the onEdit Trigger in Google Apps Script

Using JWT as a license key in Google Apps Script Google Workspace Add-ons

Image credit: Zzapps

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 Session.getActiveUser().getEmail() .

Source: Use JWT as a license key in Google Apps Script add-ons

Report: Implementing a pseudo 2FA for Web Apps using Google Apps Script

In Google Apps Script, there is the Web Apps. When Web Apps is used, the users can execute Google Apps Script using HTML and Javascript. This can be applied to various applications. When the Web Apps is deployed with “Anyone”, anyone can access the Web Apps. And, there is the case that Web Apps deployed with “Anyone” is required to be used. Under this condition, when 2 Factor Authentication (2FA) can be implemented, it is considered that the security can be higher and it leads to giving various directions for the applications using Web Apps. In this report, I would like to introduce the method for implementing the pseud 2FA for Web Apps deployed with “Anyone” using Google Apps Script.

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 MailApp 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.

Source: Report: Implementing Pseudo 2FA for Web Apps utanaikech.github.iosing Google Apps Script

[Workaround] An alternate to displaying more than 100 widgets/sections in a Google Workspace Add-on

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.

Source: [Workaround] An alternate to displaying more than 100 widgets/sections.

Develop a Google Chat App Currency Converter with Google Apps Script – Yagisanatode

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.

Source: Develop a Google Chat App Currency Converter with Google Apps Script – Yagisanatode

How to monetize your Google Workspace add-on?

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.

Source: How to monetize your Google Workspace add-on?

12 Years and 1000 pages in Office, Google (Docs,Gsuite) Workspace, and other stuff – Desktop Liberation

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.

Source: 12 Years and 1000 pages in Office,Google (Docs,Gsuite) Workspace, and other stuff – Desktop Liberation

Using Google Apps Script to get Google Workspace user profile pictures without using the Admin APIs

Image credit: Google

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.

Source: AppScript — get user profile picture without using Admin APIs

Uploading multiple records with the Salesforce API using Google Apps Script

Image credit: Eduardo Ramon Resser

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.

Source: Uploading Multiple Records Via Salesforce API Using Google Apps Script