Tag: SpreadsheetApp

Google Apps Script: Get the last row of a data range when other columns have content like hidden formulas and check boxes [updated Mar 2022]

Google Apps Script: getRange, getLastRow, getDataRange, spreadsheetApp – requires a basic understanding of GAS. Updated 01 Dec 2021

Finding the last row of a data range in Google Sheets using Google Apps Script is a pretty common task. We often do this to find the next available free row to insert new data or to copy an entire data range to put into an array in Google Apps Script.

Generally, for fairly clean data, or a small range of data, we would rely on two approaches to get the data we need:

  • getLastRow(): this will get the last row in a Google Sheet that has data in it. It determines the last row based on the last available row value in all columns in the spreadsheet.
  • getDataRange(): this will get the range up to the last row and column with values in it.

Let’s take a quick look at this clean data set:

Continue reading “Google Apps Script: Get the last row of a data range when other columns have content like hidden formulas and check boxes [updated Mar 2022]”

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: 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”

Google Apps Script – How to create Javascript and CSS files for a Sidebar Project in Google Apps Script

Google Apps Script-templates, Javascript, CSS

So, I started working on a larger Google Sheet Sidebar project in Google Apps Script recently and I quickly realised that it was going to be a mess if I didn’t separate my Javascript, CSS and even some of my HTML into separate files. However, if you have ever noticed in the script editor that there is no way for you to add script or style files to the code. Your only two options are Google Script files *.gs and Html files *.html.

Project File Types - Google Apps Script

Then, what’s the trick?

The trick is to create separate HTML files for your CSS and Javascript and include or import them into your main HTML file. Unfortunately, you can’t do this with the standard:

HtmlService.createHtmlOutputFromFile(filename)

Instead, we will need to use the templating method:

html = HtmlService.createTemplateFromFile(filename)

We will get into a little more detail on templates later in this tutorial.

My main reference for this was the Google Apps Script UI best practice guide, and you will see code snippets of the first example there that I have modified for my own example.

The thing is, the explanations were a bit vague for me to work out clearly so I really needed to create an example of my own to work through how to use it. The example below breaks down the steps to create file relationships to make your code look neater. It also dives into some uses of template statements in HTML.

The Final Result

The goal of our little sidebar project will be to display a sidebar with coloured paragraph text, a list generated with Javascript and a randomly assigned body page the contain the text “Body 1” or “Body 2”.

Take a look at the demo:

Sidebar Example - Google Apps Script

Continue reading “Google Apps Script – How to create Javascript and CSS files for a Sidebar Project in Google Apps Script”

Google Apps Script – Get the Start Row and Length of Each Category in an Ordered Column in Google Sheets

Google Apps Script and Google Sheets

Quite often I will need to get the range of each category in an item and do something with it in Google Sheets. For example, I work in education, I will often have rows of students that are categorized by class sections. I will then be asked to do something like those sections like put each section of students in their own sheet or set alternating colours for each section to make the sheet easier to read.

Alternatively, you may want to grab sales data by region or sales items by a particular category and work with them in Google Apps Script.

The Example

Let’s say we want to get the range values of the following sheet by planets. We will be categorizing our data by the Grouping, column C.

Grouping By Planet - Google Apps Script

First, we don’t want to take into account the headers on the first row. Our first grouping will be Mars, followed by Jupiter, Uranus, and Mercury. We want to know which column that each category starts on and how many of that category there are.

The Code

Continue reading “Google Apps Script – Get the Start Row and Length of Each Category in an Ordered Column in Google Sheets”