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.
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
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.
Aryan Irani is a Google Developer Expert for Google Workspace. He is a writer and content creator who has been working in the Google Workspace domain for three years.
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;
}
Learn how to use the Udemy API with Google Apps Script to find free programming courses on Udemy on any topic.
A double win in this post from Amit Agarwal. Not only can you get a list of free programming courses currently running on Udemy, but Amit includes the code snippet used to get/add these to a Google Sheet. This might be useful if you are working with other APIs and are looking for a code snippet to help format the data in a Google Sheets friendly format.
Sending out emails as a part of a Google Workspace automated workflow is a very common task. In Google Apps Script we can send emails to users using the MailApp.sendEmail(), the GmailApp.sendEmail() method or even as a JSON payload with the Gmail Advanced API service.
While one might expect that the sender’s signature block would also be transmitted with the automated email, we find that this is not in fact the case.
Scott Donald highlights some of the pitfalls of trying to access the Gmail signatures using Google Apps Script. Fortunately Scott also shares a workaround for using a Gmail draft to store a number of signatures/snippets. This is all wrapped in a handy getSignatureBlock() function, created and shared by Scott to get either the user’s primary signature block or one stored in a draft.
Highlight Duplicate Rows in spreadsheets efficiently and accurately with Google Apps Script for coders and non-coders.
Some nice techniques in this post which shows you how you can highlight duplicate rows in a Google Sheet using Google Apps Script. If you need to modify there is a isDuplicateRow() which could be modified to suit your needs. Another nice solution in this example is the inclusion of function to getRandomUniqueColor(), which returns a random hex colour.
Imagine you’re responsible for transporting materials from one location to another for a logistics company. Accurately calculating the distance and time between two points is crucial for efficient logistics management, and having this information readily available can save time, money, and effort. However, doing it manually can seem like a daunting task. But don’t worry, there’s a solution!
Discover how to automate distance and time calculations between two points in Google Sheets using Google Apps Script. This blog provides a step-by-step guide on setting up the integration and shows you how to use it to save time and increase productivity.
Aryan Irani is a Google Developer Expert for Google Workspace. He is a writer and content creator who has been working in the Google Workspace domain for three years.
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
In this article I’ll show you how to setup reminder emails that will get sent automatically each day if there are any pending tasks in your spreadsheet. Stay tuned until the end, where I’ll show you an easier way to accomplish this using Coda.
Eric Koleda must be the undisputed master of the Apps Script demo. The ‘random cat’ Add-on, is one of my favourite examples where Eric finds a really simple hook to get you engaged in what can often be a complex project. Eric is now Developer Advocate at Coda and he’s not lost is eye. In this post Eric gives the gift of script with a basic example of how you can handle email notifications from Google Form responses in batches with a timed trigger (cats being replaced for cakes). Eric also shows how a no-code alternative can be accomplished in Coda.
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
