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: Google Standard Color Palette Picker for Sidebar and Dialog Boxes in Google Sheets, Google Docs and Google Slides

Google Apps Script: Dev Tools, Color Picker, Side Bar, Custom Prompt, HtmlService, onOpen, Sidebar, Dialog Box

I wanted to update one of my free Google Add-on apps that works with colour. What I had is just the standard HTML color input element where the user selects from the palette and that hexadecimal colour code is returned to Google Apps Script to be used in the App. The problem is that it is really hard to get a good colour match between the palette and Google’s own colour range that is accessible from the fill or text colour buttons.

Take a look at the comparison between the HTML color input element and the Google Sheet background colour palette in the image below.

Color input vs google sheet palette colours

That’s not a user-friendly tool to match colours with the standard Google palette.

So in the back of my mind, I had always wanted to create a tool for a sidebar or dialogue box that would allow the user to easily access the standard colours or use the custom palette provided by the HTML color input.

After finding a bit of time in my recent summer break I came up with this.

Google Color array picker

And this is how it looks in action:

Continue reading “Google Apps Script: Google Standard Color Palette Picker for Sidebar and Dialog Boxes in Google Sheets, Google Docs and Google Slides”