AppsScriptPulse

Easily managing time-driven triggers using Google Apps Script and the TriggerApp library

Google Apps Script can be executed by time-driven triggers. This is one of the very important points for taking cloud computing. But, the scenarios using time-driven triggers are different for each user, and there are a lot of situations for using time-driven triggers. But, when a script for implementing time-driven triggers is developed, each script is different and complicated. In this report, I would like to introduce easily managing time-driven triggers using a Google Apps Script library.

In Google Apps Script time-driven triggers are a powerful way to automate tasks including Google Sheets. However, managing them can be difficult, especially if you have a lot of triggers and need triggers to run at different times and frequencies. The TriggerApp library from Kanshi Tanaike makes this a lot easier and this post includes a number of examples that are easy to copy/modify for your own script projects. The post covers the following scenarios:

  • Execute 2 functions at specific dates and times
  • Execute a function with a specific cycle between specific times between specific dates
  • Execute 3 functions with a specific cycle between specific times on weekdays
  • Send an email on a birthday every year
  • Execute specific functions on specific weekdays in a week
  • Execute 6 different functions every 10 minutes from “09:00:00” to “11:50:00” in order
  • Opening hours of Google Form from 09:00 to 17:00 on Weekdays
  • Opening Hours of Google Spreadsheet from 09:00 to 17:00 on Weekdays
  • Execute specific functions at 09:00 on Monday to Friday at the beginning of specific months

The source code for the library is on GitHub and includes documentation on all the methods it uses.

Source: Easily Managing Time-Driven Triggers Using Google Apps Script

Retrieving and putting values for PDF forms with Google Apps Script (and other PDF solutions)

This is a sample script for retrieving and putting values for PDF Forms using Google Apps Script.

We’ve featured a couple of posts from Kanshi Tanaike on Pulse with solutions for handling/manipulating PDF Documents with Google Apps Script. This post on retrieving values from PDF Documents is just one in a series of recent contributions from Kanshi looking at how Google Apps Script can be used with PDFs:

All these examples use the PDF-LIB JavaScript library and as previously noted in the Pulse post Merging multiple PDF files as a single PDF and converting all the pages in a PDF to PNG images using Google Apps Script , with minor modification to can load this library into the Apps Script editor.

Source: Retrieving and Putting Values for PDF Forms using Google Apps Script

Management of rich text cell formatting in Google Sheets with Google Apps Script

In order to add and delete a text for the rich text in a cell, it is required to create a script while the current text style is kept. This is actually complicated. In this post, I would like to introduce the enriched management of rich text on Google Spreadsheet using Google Apps Script. In order to enrich the management of Rich Text using Google Apps Script, I created a library RichTextAssistant.

Some very clever work from Kanshi Tanaike which can help with the management of cell text formatting in Google Sheets.  The RichTextAssistant Apps Script library included in the post has some nice methods for both handling and preserving cell text formatting. It’s worth spend a little time checking the various samples provided to see what is possible and how these might enhance one of your own script projects.  If you are curious the source code for the library is also on GitHub and linked from the post.

Source: Enriched Management of Rich Text on Google Spreadsheet using Google Apps Script

Generating a Time-based One Time Password (TOTP) in Google Apps Script

In this post, I would like to introduce a sample script for putting Time-based One-time Password (TOTP) value into Google Spreadsheet using Google Apps Script.

For UK readers, no not Top of the Pops (TOTP), but Time-based One Time Passwords (TOTP). In the context of Google Workspace it’s more than likely that you can rely on the built-in Google Identity and account security features rather than rolling your own form of two-factor authentication (2FA). Should you be in such a situation when you need to authenticate outside of Google then this example integration of the OTPAuth library in Google Apps Script might be of interest. Rather than just updating a Google Sheet you could combine with MailApp/GmailApp to email the passcode for the user to use. See the source post for implementation information and caveats.

Source: Putting TOTP into Google Spreadsheet using Google Apps Script

Google Drive folder picker using jsTree with Google Apps Script and Javascript

This is a sample script for the folder picker using jsTree with Google Apps Script and Javascript.

The Google Picker is a file picker that allows users to select files from Google Drive which can be used in your Apps Script projects (See Using Google Picker with Google Apps Script). The Picker is good but there are certain scenarios where the functionality isn’t available, one I’ve personally found is selecting the My Drive or Shared drive root. Here’s an alternative approach from Kanshi Tanaike for an alternative folder picker created using jsTree. In the post you’ll find more information plus how you can use this solution with a service account.

Source: Folder Picker using jsTree with Google Apps Script and Javascript

Rearranging column order in Google Sheets using Google Apps Script

This is a sample script for rearranging columns on Google Spreadsheet using Google Apps Script.

Following on from the recent example from Scott Donald on How to sort tabs in Google Sheets with Google Apps Script, here is a handy little snippet from Kanshi Tanaike for changing the column order in Google Sheets using Apps Script and the moveColumns method.

Source: Rearranging Columns on Google Spreadsheet using Google Apps Script

Efficiently exporting mulitple Google Docs files in PDF format with batch requests and Google Apps Script

This is a sample script for exporting Google Docs files (Spreadsheets, Documents, and so on) in PDF format with batch requests using Google Apps Script.

As a reference sample situation, in order to export 100 Google Document files as PDF files, when I tested this sample script, the processing time was about 150 seconds and no error occurred. And, I confirmed that 100 valid PDF files were created in my Google Drive.

Kanshi Tanaike has been busy again, this time looking at how you can handle batch exports from Google Docs, Sheets and Slides using Google Apps Script. There is quite a bit of engineering to get your head around but if you are looking for a copy/paste solution everything is well commented for you to drop this code into your own project. If you’d like more context about the solution there is a related post on Stack Overflow.

Source: Exporting Google Docs Files in PDF format with Batch Requests using Google Apps Script

FilesApp is a GAS library for retrieving file and folder list in Google Drive

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.

The FilesApp library by Kanshi Tanaike has been around since 2018. I have to confess I wasn’t aware of this library until I read a more recent post, Retrieving Total File Sizes in Specific Folder of Google Drive using Google Apps Script. Similar to my own post on Creating a Google Drive report in Google Sheets the FilesApp library directly communicates to the Drive API, this library however goes a step further in terms of performance by using UrlFetchApp.fetchAll to handle some asynchronous Drive API calls.

Source: GAS Library – FilesApp

Putting values from of all Google Sheets in Google Drive folder into a master Sheet with low process cost using Google Apps Script

 

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.

Source: Putting Values of All Spreadsheets in Folder to Master Spreadsheet with Low Process cost using Google Apps Script

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