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.
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
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.
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
Get Google Form responses for checkbox-type questions and perform further actions depending on their values.
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.
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
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
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.
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
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
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:
Start date – format mm/dd/yyyy – forms the date range to look for events within.
End date – format mm/dd/yyyy – forms the date range to look for events within.
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’.
Calendar ID – typically your email address for your personal Google Calendar to search for events in.
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
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.
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.
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.
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.
