AppsScriptPulse

Custom function for get subtotals for each group in Google Sheets using Google Apps Script

You get subtotal sums for each of the selected columns.

The code speaks for itself in this post from Max Makhrov in which he shows how to create a custom function for Google Sheets to subtotal groups of columns. There is some impressive data manipulation/reshaping going on in the function and some nice use of using arrays in formula created with brackets { }. Some of the code is also written with OpenAI/ChatGPT (I’m guessing anything with a for loop). The icing on top is the conditional formatting for group totals. Follow the source link for all the code.

Source: Subtotals for each group in Google Sheets

📮`MMailApp` for Google Apps Script: GmailApp+MailApp+Gmail API

The library lets you send emails, using MailApp, GmailApp, and Gmail API. The library takes care of parameters, so you do not need to care about which method to use. I had trouble with my project when I wanted to automatically send emails and suddenly for me we’ve reached the daily limit. This is why I’ve decided to make some research and be sure fewer bad things happen in the future.

If you are looking for a deep dive into Google Apps Script powered email sending options you are at the door of enlightenment. In this post from Max Makrov you not only to get an explanation of the options and benefits for sending email with MailApp, GmailApp and the Gmail API, but also a handy library, MmailApp. which makes it easy to switch between all three.

Source: 📮`MMailApp` for Google Apps Script: GmailApp+MailApp+Gmail API

Sending emails with Amazon AWS Simple Email Service (SES) with Google Apps Script

This week was the first time I’ve tried AWS. I wanted to test AWS SES to write some emails with the help of Amazon. The learning curve was painful for me because I was totally lost in the documentation. Here’s what I came through

Max Makhrov is on a role with Google Apps Script and Google Sheets development. As well as this latest Medium post looking at Amazon AWS Simple Email Service (or as Max discovered – ‘Not so Simple Email Service’) if not already well worth following Max on Twitter (@max__makhrov) for lots of very useful Google Sheets/Script tips.

If you are hitting your quotas on Google Workspace daily email sends using AWS SES could be one solution. If you want to avoid the bold banners in recipients inboxes here’s some additional required reading on How to Set Up AWS SES and Avoid Spam Folders is essential reading. This covers setting up DNS records for SPF, DKIM, DMARC when using AWS SES. As these changes are required at a DNS level it’s not possible to set this up on regular @gmail.com accounts.

Source: Send Emails with Amazon AWS SES from Google Scripts

Google Apps Script library if you need to get a Google Sheet as a PDF

Library for converting Google Sheets Into PDF

Features:

  • All PDF settings including colontitles = custom headers and footers.
  • Input parameters is a single plain object.

The library uses the printing features of Google Spreadsheets to provide a complete representation of a document in a different format. It contains a huge amount of features There are settings such as page size, headers and footers, colontitles, gridlines, notes and more.

Source: Max-Makhrov/sheets2pdf_gs: Library for converting Google Sheets Into PDF

Using Google Apps Script to convert a Google Sheets selected range to an image 

Convert selected range into PNG image and save it to Drive

In this post from Max Makhrov you can discover how you can convert a selected Google Sheets range to an image. With options to embed charts and tables in Google Docs and Slides you might be asking why you would want to do this. Max highlights a couple of great reasons:

There are some limitations with this approach which are outlined in the post and if you have any solutions there is a link to contribute to the GitHub repo.

Source: Google Sheets Script. Range 2 Image

Dependent dropdown lists for Google Sheets (and lots of other Google Apps Script goodies)

Updated the script for dependent lists, and now it supports new options. New for 2022:

Dependent drop-down lists can be used to create a dynamic list of choices in Google Sheets. When the user makes a selection from the first drop-down list, the choices in the second drop-down list will be updated based on the selection. This can be used to create a cascading list of choices.

If you are looking for a nice solution to build dependent drop-down lists in Google Sheets this is a great solution from Max Makhrov. For more experienced Google Apps Script developers the solution incorporates some clever snippets both from Max and a number of other members of the community. Some highlights include: converting a column index into corresponding column letter; a tasker to batch apply updates to a Google Sheet; ChuckyCache for objects above 100Kb; and a reference to a zip compression solution. All these are referenced in the source code provided in the post.

Source: Dependent Drop-Down Lists For Google Sheets v2022

Multiple Sheets Action. Use Macro Recorder 🔴

Image credit: Max Makhrov

The idea: perform the same action on multiple sheets. The idea is not new, my approach was to use macro each time to see the correct code snippet. It was useful and saved me hours on my current project.

A clever solution from Max Makhrov with some boilerplate script that makes it easy to use a recorded macro across multiple sheets. To make this solution even better I’ve suggested an addition which activates the sheet which makes it easier to reference recorded macro functions. To see in action here is a copy of Max’s Google Sheet with updated code.

Source: Multiple Sheets Action. Use Macro Recorder 🔴