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.
We are excited to announce the General Availability (GA) of our native data store: AppSheet database. Our goal is to blend the simplicity of a table-driven data editing interface with the performance and scale of a relational database for non-technical users.
During testing, AppSheet database was faster than Sheets for processing adds, updates, and deletes of larger tables. In other words, the performance benefits of AppSheet databases are more apparent for a table with 50,000 rows compared to a table with 1,000 rows. AppSheet databases also have better support for concurrent edits.
It’s also worth noting that quick sync is enabled by default for all apps backed by AppSheet databases, even for security filters, so data updates automatically for app users.
Google AppSheet Databases went into public preview in October 2022, the announcement and context being covered in the Pulse post ‘Introducing AppSheet databases: Build data driven apps for Google Workspace‘. The recommendation while AppSheet databases were in preview was not to use them for production as there may have been breaking changes, which limited how much testing I could do, but I still found the occasional prototype project to give them a try. As noted in the general availability announcement the performance is more noticeable on databases with more rows. Where I saw big gains were around ‘quick sync’, particularly when making changes using the AppSheet API.
Something to keep in mind before lifting/shifting your existing Google Sheet based AppSheet apps are the usage limits. For example, Google Workspace customers eligible for the AppSheet Core licence at no additional cost are limited to 2,500 rows per database and 10 databases. The 2,500 rows is also the total amount across all tables in a database, for example, you will hit your quota if 1 database has 5 tables with 500 rows. Moving up to AppSheet Enterprise Standard increases the limit to 200,000 rows with a current maximum of 50,000 rows per table. In the later case Google have said this is a current technical limit and they are looking at increasing this soon. For AppSheet Core users it doesn’t sound like Google will be increasing the quotas anytime soon so it is worth making sure your data source and/or licence aligns with your anticipated data needs. There is an active discussion about usage limits in the source post.
Who takes the notes ? Simple question we always ask at the begining of the meeting, maybe it will be over in a near future with Generative AI. Using Meet recording, included in the Google Workspace licences, we can generate the transcript of the meeting with Google Speech to Text API and then generate the minutes.
Lots of chatter around ‘Gen AI’, but cutting through the hype and, in particular, the Platform-As-A-Service opportunities created with generally available Large Language Models (LLM) opens lots of new doors for Workspace Developers.
This example from fellow Google Developer Expert, Stéphane Giron, is an area I think has the most potential, using LLMs in Google Workspace Add-ons such as Gmail, Docs, Calendar etc. With Google’s Vertex AI LLMs there is really opportunity to create powerful solutions whilst preserving data sovereignty. Exciting times!
This report introduces the method for easily processing the template of Google Documents and Google Slides using Google Spreadsheet as a database using Google Apps Script. Google Spreadsheet is used as a database. Google Documents and Google Slides are used as templates. The simple method for creating new Google Documents and Google Slides using the database and the templates is introduced.
Latest Apps Script magic from Kanshi Tanaike, this time they turn their addition to an easy way to use data from Google Sheets as a ‘document merge’ for templates created in Google Docs and Google Slides. This is achieved by using the TemplateApp Apps Script library, which has lots of useful features including simply methods for sheetRangeToDocuments() as well as the ability to embeded and fixed width images from placeholders. There are some llimitations documented in the GitHub repo, as well as examples for all the methods available in the library.
A fast approach to get all items in a directory tree in Google Drive with Google Apps Script. Contains video and edge case handling.
This is an excellent detailed step-by-step tutorial from Scott Donald all about how to efficiently list all files and folders in a folder’s directory tree in Google Drive using Google Apps Script. The in parents method is one we’ve covered before in Pulse, but Scott provides a very comprehensive explanation of what’s going on, plus all the code is available if you would like to reuse in your own projects.
We’re excited to announce that new Google Chat Import Mode APIs are now available in the Developer Preview Program! Import Mode allows developers to import data from other messaging systems while maintaining historical timestamps for messages and spaces. Data from large channels can be copied into Chat spaces silently without notifying the user, sending emails, etc until the admin is ready to have users start using the new space in Chat.
Google are continuing to add developer features to the Google Chat platform, the latest being Import Mode APIs. Theses APIs are designed to help when transitioning from other messaging platforms to Google Chat. Google highlight the following advantages of the new Import Mode APIs:
Preservation of resource creation timestamps. You can set a historical time for the creation times of space and message resources, letting Chat apps retain historical context during user adoption of Google Chat.
End users can’t view or access spaces in import mode. To prevent user interference of a space undergoing data import, or to avoid possible user confusion as a result of viewing an in-progress data import, spaces in import mode are hidden from end users. After a space has completed import mode, you can add users to the space.
Chat turns off notifications during import mode, so that users can avoid unnecessary alerts about the migration. [Ref]
The new Import Mode APIs are currently in Developer Preview and if not already part of the preview program the linked page includes information on how to join.
Apps Script library to convert between file types, including OCR for image to documents, with a huge repertoire of conversion combinations.
The Drive API offers a whole range of conversions between mimeTypes, but it’s a little fiddly to figure out exactly how. This library takes a file and an a desired output format and converts it for you. Sometimes, there’s not a direct route – for example if you need to convert a word file to a pdf, it first needs to get converted to a Google Doc, then to a Pdf. This library automatically works out and actions any intermediate conversions required.
For those who are unfamiliar MIME types (mimeTypes) is a standard way of identifying a file’s content type, such as text, image, or audio. The MIME type for a file is usually used by applications to determine how to open and display the file. Google Drive is able to convert various files, such as a MS Word document to Google Docs.
Sometimes there isn’t a direct route and this clever library from Bruce Mcpherson can help alleviate the pain. The library works by first checking if there is a direct route between the input and output file formats. If there is, it simply converts the file. If there is no direct route, the library will use the Drive API to first convert the file to a format that can be converted to the output format. For example, if you need to convert a Word file to a PDF, the library will first convert the Word file to a Google Doc, and then convert the Google Doc to a PDF.
Knowing what mimeType to use can be a bit of a challenge but you can also use the library to give a list of known types, or if you prefer a more readable list of Google Drive export MIME types.
Application Integration is an Integration-Platform-as-a-Service (iPaaS) solution in Google Cloud that offers a comprehensive set of core integration tools to connect and manage the multitude of applications and data required to support various business operations.
The Application Integration platform provides a unified integration designer with out-of-the-box triggers, custom-configurable tasks, and plug-n-play connectors. The integration designer is a drag-and-drop interface that lets you create your entire integration flow with little or no code. You can create, modify, and run all your integrations in the integration designer.
I recently rediscovered Google’s new Application Integration service, which is currently in preview. If you are unfamiliar with this service it in part “offers a drag-and-drop visual interface that lets you create an entire integration flow with little or no code. Connectors also allow you to call and perform operations on various entities”.
There are a long list tasks that can be configured for Google Cloud services, but you are not limited to just these and you can add your own data sources. As a Google Workspace Developer, it’s been interesting to look at the Apps Script task integration. With this you can get task parameters from your integration, run any operations as you would with any other Apps Script project, with the option to set integration variables for the rest of your workflow.
The linked documentation page provides setup instructions. When you setup the Apps Script integration it creates a project with some boilerplate including adding a AppsScriptTask library. Lots of possibilities to use the Apps Script built-in and advanced services to rapidly create applications. I’m looking forward to seeing what the community come up with.
If you frequently find yourself creating multiple Google Docs that follow a similar template and pull data from a Google Spreadsheet, this tutorial is for you. Today, I’m going to show you how to automate the process using Google Apps Script.
I liked the simplicity of this script which does a very basic data merge on Google Docs template from Google Sheets data. There is a bit of piecing together to get this one working but basically it’s uses a very simple replaceText pattern which maps the spreadsheet column number to replacement tokens {{col1}}, {{col2}}, etc. Read the source post to see all the code.
Manually copying Google Sheets macros from one spreadsheet to another can be time consuming and error-prone. This Google Workspace Add-on automatically copies a script project and attaches it to a user-specified spreadsheet. Though this solution focuses on Sheets macros, you can use it to copy and share any container-bound script.
Are you looking for a way to centralize your codebase but deploy it to container-bound scripts? Here is a tutorial for you from the Apps Script samples on the Google Developers site, highlighted by Steve Webster, which gives an alternative approach to deploying as an add-on or sharing a container-bound template.
In the tutorial, you’ll find code that uses the Apps Script API to perform functions like getting, creating, and updating container-bound scripts. As part of the solution you’ll see how you can get the source project content using the Apps Script API, then opening a target Google Sheet to either create or update the container script.
This solution is not limited to Google Sheets only. You can use the same process to update code in other Google Workspace editors like Docs, Forms, and Slides. Usually, I would recommend maintaining and distributing your code as an add-on, however, in certain scenarios, this alternative approach could work well.
