See how AI tools work with Google Sheets to boost your productivity. Covers ChatGPT, Google Bard, and AI add-ons.
A very informative post from Ben Collins, discussing how AI can be used to automate tasks, identify patterns, and make predictions in the context of Google Sheets. Ben provides several examples of how Generative AI can be used to improve the way you can interact with data in spreadsheets from helping with formula to generating and improving data.
As Ben points out in the post users have benefited from AI in Google Sheets for a number of years with features like Explore. Perhaps the biggest recent change is exposing Large Language Models as a service, with users able to directly access the ‘prompt’ to generate output.
At the Google I/O ’23, various announcements were made with respect to different AI and ML advancements Google is making as we speak. While listening to the session, I stumbled on PaLM 2. It was really interesting as to what capabilities it has with respect to generative models and machine learning.
This tutorial series will introduce you to PaLM 2, the API, MakerSuite, and Google Apps Script. We will combine these tools to do something interesting with prompts.
Editor: There are a couple of ways you can access Google’s Gen AI language models. Bard is the consumer version but for developers you might want to start exploring API access to one of the underlying foundation language model, PaLM 2, using Google’s MakerSuite. There is a waitlist for MakerSuite, but worth putting your name down if you are interested in an easy way to programmatically start prototyping applications with the PaLM using a basic API key. This video series from Aryan Irani covers everything you need to get started, particularly if you are interested in using PaLM with Google Apps Script.
If you prefer to get started straight away, you can also access the PaLM API in Vertex AI today from a Google Cloud project (more information about this on the MakerSuite site).
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 tool is designed to take a single Google Drive file e.g. a Doc / Sheet / Slide and make duplicates/copies of it with unique file names. The tool will also create a clickable link within the Google Sheet to each new file copy.
Screenshot of the tool for duplicating Drive files
In today’s fast-paced digital world, effectively condensing lengthy content has become a necessity. Whether you’re a student grappling with research papers or a professional striving to stay ahead, generating concise summaries can save time and boost productivity. Luckily, the powerful combination of OpenAI APIs and Google Apps Script provides a seamless solution for effortlessly summarising paragraphs within Google Docs
Learn how to generate instant summaries in Google Docs using a step-by-step guide that combines the OpenAI API and Google Apps in this informative blog post on Medium. Discover the power of automated summarisation to enhance your document creation process.
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.
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.
Use a row of data in a Google Sheet to pass to a Google Doc via Apps Script
Create a Google Doc containing the information from a selected row of Sheet data.
The following Google Apps Script is designed to create a Google Doc for the selected row of data in a Google Sheet and to include some of that data within the new Doc. it also creates a link to the new Doc back in the Sheet on the relevant row.
Are you someone who loves reading blogs but finds it difficult to make time for them? Or, are you a blogger looking to make your content more accessible to readers? You’re in luck! I discovered an incredible combination of tools that can help automate the summarisation of blog posts.
Learn how to automate blog summarisation with Google Apps Script, ChatGPT, and OpenAI API. This blog post by Aryan Irani guides you through the process, enabling you to enhance your content creation efficiency. Simplify summarisation and maximize productivity in just a few steps.
Automatically create PDFs with information from sheets in a Google Sheets spreadsheet. Once the PDFs are generated, you can email them out directly from Sheets. This solution focuses on creating custom invoices, but you can update the template and script to fit your needs.
Today as part of a customer Google Apps Script introduction workshop, we covered a basic invoice / PDF example. By coincidence this month’s Google Workspace Developers Newsletter highlighted a solution spotlight from the Apps Script samples catalogue which does something very similar.
A trick I learned today from our Lead Workspace Trainer, Tim McLardy, was rather than using UrlFetchApp to get a PDF version of the Google Sheet with the export/?format=pdf url, is instead getting the Google Sheet as a blob and creating a pdf with DriveApp.createFile(). As this technique will generate a PDF with pages with all your Sheet tabs the trick is to selectively hide all but the tabs you want in your PDF. In the case of the example linked as the source post you can rewrite the createPDF()Â function as:
/**
* Creates a PDF for the customer given sheet.
* @param {string} ssId - Id of the Google Spreadsheet
* @param {object} sheet - Sheet to be converted as PDF
* @param {string} pdfName - File name of the PDF being created
* @return {file object} PDF file as a blob
*/
function createPDF(ssId, sheet, pdfName) {
// Based on https://webapps.stackexchange.com/q/162155/30021
const ss = sheet.getParent();
const sheets = ss.getSheets();
// Hides all sheets that are not the main one
sheets.forEach(s => {
if (s.getSheetName() !== INVOICE_TEMPLATE_SHEET_NAME) {
s.hideSheet()
}
});
// make a pdf of the sheet
const folder = getFolderByName_(OUTPUT_FOLDER_NAME);
const pdfFile = folder.createFile(ss.getBlob()).setName(pdfName);
// show all the sheets
sheets.forEach(s => s.showSheet())
return pdfFile;
}
