Tag: map

Google Apps Script: Add and removed Google Sheets columns based on a search array

Google Apps Script: V8 engine, map, filter, reduce, includes, 2d arrays, matrix

Have you ever wanted to delete or add columns in a Google Sheet, based on another set of Sheet data?

I know I have.

There have been a number of instances where I wanted to insert new columns or removed unused columns in large Google Sheets projects with Google Apps Script.

In the past, I have dynamically set headers based on something like an IMPORTRANGE or FILTER, or simply from an array ({}) from another sheet tab or Google Sheet. However, when I update the original data the headers change but all the data underneath them does not move along to the updated column with it.

Now all my data is not lined up with the header! As you can imagine, this creates some serious problems.

In this tutorial, we’ll show you how to use Google Apps Script to update your headers based on another sheets values. These sheets values can come from the current Google Sheet workbook or another one. We will also ensure that the data below the headers is migrated along with the new header location.

Take a look at the visual example below:

Add removes columns based on a search array visual example

As always, read as much or as little as you need to get the job done or learn the skill. 

Continue reading “Google Apps Script: Add and removed Google Sheets columns based on a search array”

Google Apps Script: Filling out your column data to match the number of columns in a range

Google Apps Script, Google Sheets

Have you ever experienced this warning error when trying to set values or paste a range of values back into your Google Sheet with Google Apps Script?

The number of columns in the data does not match the number of columns in the range. The data has 6 but the range has 9. (line 52, file “test”)

Yeap. One of your row lengths is not as long as the width of the column range you set.

This generally occurs when you are manipulating data into a new format before setting it back into your Google Sheet.

Let’s look like some dodgy data I’ve prepared that I want to paste or set backing my spreadsheet.

As you can see, I’ve got a 2d array for data with 7 rows and with a max row width of 9. This means our data will extend to nine columns at its max.

Now, your first instinct might be that if I take a few chips out of square peg, it should still fit into a square hole. I mean, I’m only subtracting from the square, right?

Then you go off blazing a path through the code, hoody hanging low over your head, punching away at your bespoke mechanical keyboard.

With victory within your grasp, you hit run and your world comes crashing down.

column in data does not match number of columns in range Google Apps Script

The Solution

Continue reading “Google Apps Script: Filling out your column data to match the number of columns in a range”

Google Apps Script: Create a Select All Checkbox to Tick Checkboxes in Any Desired Range in Google Sheets

Google Apps Script: isChecked(), switch, filter, map

One Checkbox to Rule them All

Now that’s a fantasy novel I could be my gums into.

Have you ever created a Google Sheet projects where you could really use a select all checkbox (they call them ‘Tick-boxes’ in Google Sheets)? Sure you can copy a range of tickboxes and paste the same range etc. But can you really trust your users not to mess that up?

People are used to select-all checkboxes in their computerised lives. It’s always better to work with familiarity to provide a better user experience rather than try and teach the user on the fly how to do something your way.

With this in mind, I went about creating a select-all checkbox for Google Sheets. Here is a little demo of how it all works. All the black background Tick boxes are select All boxes. These boxes have been assigned a range of other checkboxes that will be either checked or unchecked depending on the main select-all boxes state.

Google Select All Demo with Google Apps Script
Select All Demo

As you can see, the select all checkbox only changes the tick boxes in the assigned range. It does not affect any other non-tick box data in the same range.

To get this up and running on your own project, all you need to do is copy and paste in the two code files in your Google Apps Script editor. The first code file is the function that runs the check-all code. The other file stores all the select-all checkboxes and the ranges that they will affect.

Let’s take a look at the code and then run through a quick use guide before finishing off with an example.

Those of you who want to dive into the nuts and bolts of the code, there will be more explanation of each element of the code at the end.

The Code

Continue reading “Google Apps Script: Create a Select All Checkbox to Tick Checkboxes in Any Desired Range in Google Sheets”