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.
|
1 2 3 4 5 6 7 8 9 |
var dodgy2dArray = [ [1,2,3,4,5,6], [1,2,3,4,5,6,7,8,9], [1,2,3,4,5,6], [1,2,3], [1,2,3,4,5,6,7,8], [1,2,3,4,5,6,7,8,9], [1] ]; |
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.
|
1 2 3 4 5 6 |
function stoopid(){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var range = sheet.getRange(1,1,7,9); range.setValues(dodgy2dArray); }; |
With victory within your grasp, you hit run and your world comes crashing down.
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
/********************************************* * Fills each row array to the right with selected value * to match the largest row in the dataset. * * @param {array} range: 2d array of data * @para, {string} fillItem: (optional) String containg the value you want * to add to fill out your array. * @returns 2d array with all rows of equal length. */ function fillOutRange(range, fillItem){ var fill = (fillItem === undefined)? "" : fillItem; //Get the max row length out of all rows in range. var initialValue = 0; var maxRowLen = range.reduce(function(acc, cur) { return Math.max(acc, cur.length); }, initialValue); //Fill shorter rows to match max with selecte value. var filled = range.map(function(row){ var dif = maxRowLen - row.length; if(dif > 0){ var arizzle = []; for(var i = 0; i < dif; i++){arizzle[i] = fill}; row = row.concat(arizzle); } return row; }) return filled; }; |
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.
Quick Start Guide
The fillOutRange function takes two parameters:
range: Your dodgy uneven 2d array.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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
var dodgy2dArray = [ [1,2,3,4,5,6], [1,2,3,4,5,6,7,8,9], [1,2,3,4,5,6], [1,2,3], [1,2,3,4,5,6,7,8], [1,2,3,4,5,6,7,8,9], [1] ]; function bang_n(){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var range = sheet.getRange(1,1,7,9); var betterArray = fillOutRange(dodgy2dArray); range.setValues(betterArray); }; |
What if we wanted to put a value in to fill out the row instead:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
var dodgy2dArray = [ [1,2,3,4,5,6], [1,2,3,4,5,6,7,8,9], [1,2,3,4,5,6], [1,2,3], [1,2,3,4,5,6,7,8], [1,2,3,4,5,6,7,8,9], [1] ]; function bang_n2(){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var range = sheet.getRange(1,1,7,9); var betterArray = fillOutRange(dodgy2dArray,"Bleat!"); range.setValues(betterArray); }; |
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.
|
14 15 16 17 18 |
//Get the max row length out of all rows in range. var initialValue = 0; var maxRowLen = range.reduce(function(acc, cur) { return Math.max(acc, cur.length); }, initialValue); |
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.
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.
|
20 21 22 23 24 25 26 27 28 29 30 |
//Fill shorter rows to match max with selected value. var filled = range.map(function(row){ var dif = maxRowLen - row.length; if(dif > 0){ var arizzle = []; for(var i = 0; i < dif; i++){arizzle[i] = fill}; row = row.concat(arizzle); } return row; }) return filled; |
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.
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.
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.