AppsScriptPulse

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

How to transcribe audio and video files from Google Drive with Google Apps Script

Learn how to automatically transcribe audio and video files in Gmail messages with the help of OpenAI speech recognition API and Google Apps Script

Amit Agarwal highlights a no-code solution for transcribing audio in Gmail attachments available in his ‘Save Gmail to Google Drive’ Google Sheets Add-on. As part of this he shares the code and tips for transcribing audio files using OpenAI’s Whisper API. When you look at the snippet provided you’ll see once the audio file is in Google Drive it is very easy to use the Whisper API to get a transcript.

Text-to-speech services aren’t new, but approaches and larger training datasets are improving accuracy and if you prefer to work in the Google ecosystem you can find out more about Cloud Speech-to-Text.

Source: How to Transcribe Audio and Video Attachments in Gmail – Digital Inspiration

Auto close Google Form after X responses with Google Apps Script

The following Google Apps Script is designed to automatically close a Google Form once it has reached the number of responses you specify.

Use Apps Script code to automatically close a Google Form

Use Apps Script code to automatically close a Google Form

Do you ever create a Google Form and then forget to close it? Or maybe you want to limit the number of responses that a form can receive? If so, you can automatically close a Google Form once it has reached the number of responses you specify with this example Google Apps Script.

Source: The Gift of Script: Auto close Google Form after X responses

Handling Google Forms checkbox responses with Google Apps Script

Get Google Form responses for checkbox-type questions and perform further actions depending on their values.

Get all checkbox responses from a Form

Get all checkbox responses from a Form

The following Google Apps Script is an example of one way to get the responses from a Checkbox-type question on a Google Form and how you might go about differentiating them.

This came up for a project I was working on where I need to put a Yes/No value into 3 separate Google Sheet cells based on 3 options in a question. The slight challenge is that all of the responses come out as a single array for this question, containing the strings of the values that have been ticked only.

Source: The Gift of Script: Google Form Checkbox responses

Read and write multiple Properties with Google Apps Script

Bulk read and write a number of key-value pairs in the User Properties store. Extract to an Object for ease of use elsewhere in your code.

Access User Properties and put the values into an Object

Access User Properties and put the values into an Object

The following Google Apps Script is a few snippets of some larger code where I needed to write (and then later read back) a number of User Properties in one go. Rather than creating multiple single write requests it is more efficient to do this in bulk.

I also needed a way to bulk read/extract these values later so I implemented a JavaScript Object that would allow me to easily call the Property name and get its value in return.

Source: The Gift of Script: Read & write multiple User Properties

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

Delete Google Calendar Events by keyword and date range

Search a date range in Google Calendar and match any events with a given string, then delete those events.

Search for and delete Google Calendar events

Search for and delete Google Calendar events

The following Google Apps Script is designed to search a date range for Google Calendar events containing a given string (something to help target those events) and then delete them.

There are 4 items at the beginning of the script to complete for your requirements:

  1. Start date – format mm/dd/yyyy – forms the date range to look for events within.
  2. End date – format mm/dd/yyyy – forms the date range to look for events within.
  3. Search string – bit of text that is unique to the events you wish to delete to differentiate them from other Calendar events you may not wish to remove. Note: is not case-sensitive and will not pick-up search string inside of another word eg ‘the’ in ‘there’.
  4. Calendar ID – typically your email address for your personal Google Calendar to search for events in.

Source: The Gift of Script: Delete Google Calendar Events by keyword and date range

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

Validate postal address with the new Google Maps Address Validation API and Google Apps Script

 

Validate addresses using Google Maps Address Validation and Apps Script. Discover onleeaddress the add-on for Google Workspace.

I missed the announcement in November 2022 that there is a new Google Maps Address Validation API. I also missed this post from Stéphane Giron showing how you can use the Address Validation API in Google Apps Script.

The concept is simple, you provide the Address Validation API with what you think is a correct address and the API returns information on each component of the address and additional metadata. Visit the source post for more details on setup and some example code.

Source: Validate postal address with the new Google Maps Address Validation API and Apps Script

Find all matching values in Google Sheets cells with Google Apps Script

Learn how to search and find all values in an entire Google Sheet spreadsheet, in a specific sheet tab or in a range with Apps Script.

Handy little snippet from Scott Donald on using the Google Apps Script TextFinder class to find all the occurrences of matching text in a Google Sheet. The bit I particularly liked in this solution was how Scott used a .map method to return an object array of cell hits including A1 notation:

Array containing an object for each sheet containing the sheet name and the cell location (Image credit: Scott Donald)

Follow the source link for more information including a video tutorial.

Source: Find All Values in Google Sheets with Apps Script – Yagisanatode