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

expand column data to macth all rows Google Sheets

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

One possible option is to simply iterate through each line of and paste it in as you go.

If you are a masochist.

Alternatively, you could update your 2d array so that each row is the same length by entering some empty array values.

Oh but Yagi! This sounds so tedious, I wish someone had created a function to do this for me automagically?

Me?!

Oh right, yeah.

Here is it is:

The Code

This little piece of code will fill out your row values up to the largest row length in your data set. This will make all rows of equal length.

You can choose what you want to use as fill item to fill out your row. However, if you leave that parameter, blank, it will simply be an empty text string.

More examples of SpreadsheetApp

Quick Start Guide

The fillOutRange function takes two parameters:

  1. range : Your dodgy uneven 2d array.
  2. fillItem (optional) : Any value you want to add to the cell to fill out the array. The default is set to an empty string (“”).

Simply copy and paste the function into your Google Apps Script and call it when you want to fill out an uneven range of row lengths before setting your data.

Here is an example where we update our 2d array and use the default empty string fill item.

paste values google Sheet with extra row data

What if we wanted to put a value in to fill out the row instead:

paste values google Sheet with extra row data bleat

Simple as that.

Code breakdown

for the sexy ones. 

Once the function is run, the first action is to check if the user added a fillItem parameter as the second optional parameter. If they didn’t then when the fillItem is called for the first time, it will return undefined. Alternatively, if there is a value it will set that value to the fill variable.

We use a ternary operator to put all this neatly on one line:

var fill = (fillItem === undefined)? "" : fillItem;

Next, we are going to iterate over all the rows, grabbing their length. We want to find the row with the longest length.

Fortunately, Google Apps Script has implemented the Javascript reduce (Array.prototype.reduce) method to help us out. The reduce method takes the values from each iteration and reduces them to a single value. The most common and easily understandable example is to use reduce to get the sum of all the values in an array.

More examples of reduce

We’re obviously not doing that here. We are going to use reduce to iterate through our array lengths to find the largest length by incorporating the Math.max() function.

The Math.max() function takes a set of zero or more numbers and determines the largest number.

Let’s backtrack a little first. Our reduce method first takes a function call containing an accumulator (acc) parameter and a current (curr) value of the iterated row.

On each row, we take the row cur length and compare it with the value in our accumulator (acc) using Math.max(). If the cur.length is greater than the acc, the acc is updated to the cur.length. reduce then iterates to the next line and repeats the process. This continues for the entire array.

The result from reduce is then the largest row length from our array of arrays.

Our next task is then to fill out all the other rows to equal the max row length.

Here we are going to use the map (Array.prototype.map) method. When we return a map method, it allows us to modify each array item in the manner we dictate inside the map.

More examples of map

The map method takes a function containing the current item in the array as a parameter. For us, we’ll call this row, because that is what we are working with.

Line 22 subtracts our maxRowLen we worked out earlier from the current iterated row length. The remaining value is how many extra items in our array we need to add. We called this dif for the difference.

Now, we don’t want to add any arrays if our row is already the same length at the max row so on Line 23 we make an if statement saying that if the difference of the maxRowLen minus the current row length is greater than zero, then let’s add some more array items.

I decided to go with creating a new empty array (Line 24). I then use a for loop to loop through dif times adding the fill to the array (Line 25). Remember, the fill is either an empty string or whatever the user designated to fill the remaining values.

Then we concat, or join the current row array in our map to the newly created filled array (Line 28).

The newly formed row is then returned creating a nice clear 2d array of rows of all equal length in the filled variable.

Lastly, our main fillOutRange function returns the new array back to your code for you to use.

Conclusion

This little tool has saved me a bunch of time when transforming data in Google Apps Script before setting it back into the Google Sheet.

What will you use it on? I would love to hear.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro!

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

Leave a Reply