Month: April 2019

Google Apps Script: Send Bulk Emails and Share a Google Sheet with One Button Click

Google Sheets, Google Apps Script, GmailApp

Ever wanted a quick way to give edit permissions to a user and then send them an email based on values in your Google Sheet?

This task crosses my table several times a week. Whether it is sending reminders to complete a Google Sheets task or share a Google Sheet to complete a task.

You might be thinking that it’s pretty easy to just go up to the big green Share button on Google Sheets and plug in all the people you need to give edit permission too. But what if you need to give edit permission to a number of people that are on your Google Sheet. Ever noticed that the Share button gets in the way when you are looking through your Sheet for those names or emails?

When you normally share a Google Sheet you have the option to send a message, but what if you want to send a custom message with the user’s name? You are kinda stuck to a brief message which sucks.

So I decided that it would be best to create a template script that I could quickly adapt to suit my need in different circumstances.  Below is the result.

Basically, this little piece of Google Apps Script allows you  to plug in a custom:

  1. Email column.
  2. Columns you want to use to create a unique email for each person you want to send your message to.
  3. Email sent checkbox location
  4. An Image (I use a button) that the user can click on to send the email and give edit permission to the Spreadsheet.

Google Sheet necessary items for GAS email and share template

The script will then get all the emails and their associated columns you want to use to personalize your email. Remove the duplicates and then give permission to edit the current Google Spreadsheet and send them an email.

If you are in a hurry to copy and paste the code into your own project the script is below with a short Quick Start Guide.

If you are looking to learn how it all works, I will go into the details after the Quick Start Guide.

Continue reading “Google Apps Script: Send Bulk Emails and Share a Google Sheet with One Button Click”

Google Apps Script: Basic Beginners Guide to Using Strings in Code

Google Apps Script, Javascript, strings

Hey there, Yagi here, you’ve probably stumbled across this page from a link from one of my other in-depth tutorials. This is just a quick primer on Javascript Strings in Google Apps Script for the non-coder.

Here’s a bare-bones example of how a string of text might come together in your code:

The resulting log would  look like this:

strings in Google Apps Script

 

Strings, +, \n and arrays[value]

If you are unfamiliar with basic string syntax and joining (concatenation), basically you can write text inside a single (‘) or double(“) quotation marks. For example:

To join two strings together you can concatenate them with a plus sign (+).

Just remember to put a space inside your quotation mars at the end if you are going to join another word or the words will not be spaced and theywillbealltogether.

This is handy when you want to put things on separate lines in your code but it won’t be on a separate line when it is displayed. You can use the newline escape sequence \n to return the next part of a string to a new line.

To insert a custom variable into a string, one fairly beginner-friendly approach is to put it between two plus signs.

You can see in the meatz variable above, we have put our two meat items in an array. An array always starts at zero (0). So to get chicken we would do, meatz[0].

Meow get on back to your main tutorial and get learn’n.

Create and Publish a Google Workspace Add-on with Apps Script Course

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.


~Yagi

Google Apps Script: Getting Selected Google Sheets Columns and Reducing them to a Unique Value Based on a Key Column

Google Apps Script, Google Sheets, getRangeList, 3D arrays

In a number of situations in my day-to-day work, I need to grab data from only Selected Columns in Google Sheets. Then using a selected column as a key, remove any duplicates in a similar way that a pivot table does.

I might want to use this information as part of a report or grab emails from a sheet and send custom emails to clients or staff.

I don’t always need all the data in each row, but I may need a lot of it. The two standard approaches would be to:

  1. Get all the data and then in Google Apps Script just select the relevant columns you need.
  2. Iterate through a list of columns you want to get ranges of and select each range individually making calls to your Google Sheet in each turn.

This might be useful for small data sets and, to be perfectly honest, I have used both these approaches in the past, but recently I stumbled across a method in the Google Apps Script Sheet Class called:

sheet.getRangeList([A1Notation,...,...])

Now I think that the awesome gods of GAS may not have purposed this method with the thought in mind that it would be used for collecting range values. I kinda get the impression that it was more designed for applying formatting to the selected column ranges.

However, what I have found is that it seems to be pretty fast at collecting all the ranges and then, on the Google Apps Script end, getting their values.

The resulting values of using getRangeList() can be then pushed into a 3D array of columns with row values.

For those of you in a hurry, the code is below. We will move on to an example and explanation after. Feel free to reference what you need and discard the rest.

The Code

Continue reading “Google Apps Script: Getting Selected Google Sheets Columns and Reducing them to a Unique Value Based on a Key Column”

Working with Google Apps Script in Visual Studio Code using clasp

Google Apps Script, clasp, Visual Studio Code, Node.js, npm, Windows 10: Updated 23 Oct 21

I don’t mind the Google Apps Script built-in online editor. It provides word completion, error handling, and pretty formatting, making it ideal to hash out a small automation project pretty quickly.

However, one of its major limitations is that after 1,000 or so lines of code and multiple files, it can start to get a little clunky. It also makes things a bit difficult when you want to use your own debugging or testing software. Further, if you have a HTML (CSS, JS) intensive app, it is quite hard to create and test the front end. Finally, it can be just damn comfortable slipping on a pair of your favourite slippers and using your preferred Text Editor or IDE.

This little tutorial is going to guide us through the steps that will allow us to work, push and pull Google Apps Script code from the online editor to Visual Studio Code. We’ll even be able to run the program from the terminal in VS Code and manage versions.

Fortunately, Google developed a tool that enables us to develop Google Apps Script code from the command line, clasp.

Setup Visual Studio Code for Google Apps Script

Continue reading “Working with Google Apps Script in Visual Studio Code using clasp”