Google Apps Script: 3 Google Sheets Custom Functions to Allocate Items from a Column and Spread Them Out into a New Column N Times (Updated Feb 2022)

Google Apps Script, Google Sheets, Javascript: Spread operator, fill, new Array, forEach, isArray, flat, every, slice, length, typeOf

Imagine if you have a set of data, say, Cat, Dog, Goat and you want to create a new column where each item is repeated 3 times.

Essentially what we are doing here is spreading out our original list of numbers in multiples of 3.

What if we wanted to specify how many times each item will be spread out into our new column? For example, maybe we want cat to repeat 2 times, dog to repeat 4 times and goat to repeat 3 times. Something like this:

Finally, what if we want to evenly assign our list of cat, dog and goat to another list? Imagine we have a bunch of people wanting to pet an animal. We need to evenly assign our animals to the list of people.

There is no real easy approach to do these things with the standard Google Sheets Formulas (Known as Functions in Google Sheets). Fortunately, we can create custom functions with Google Apps Script to do the job.

In this post, I will go over the 3 custom functions that I created to solve these three problems. I’ll then provide a quick-use guide if you just want to dive in and get started using them. Finally, I go over some of the interesting parts of the Google Apps Script code for those of you who wish to learn more about creating your own custom functions.

The Custom Functions

Spread by Number

The SPREAD_BYNUMBER custom function creates a column of data based on an array of values repeating each value n times, where n is the frequency of time you want each item in the array of data to be repeated.

SPREAD_BYNUMBER takes two arguments:

=SPREAD_BYNUMBER(list range, frequency)

1. List: The range or array of values. For example, you may have a list of value you want to duplicate in a new column. In our example below, we have a list of constellations (LMK in the comments if you can figure out where this is from 🐰🥚)

In our formula this is represented like this:

=SPREAD_BYNUMBER(A2:A11, frequency)

You could also grab row of data, say, A2:G2, or a matrix of data, say, A2:G14. Alternatively, you could type an array straight into the function:

=SPREAD_BYNUMBER({“Crux”;“Balrille”;“Peccanouette”}, frequency)

2. Frequency. The number of times you want the list to repeat. Perhaps we want to repeat our list of constellations 3 times. Our function would look like this:

=SPREAD_BYNUMBER(A2:A11, 3)

Spread by Value

The SPREAD_BYVALUE custom function creates a new list of duplicating values, where n is a corresponding value to be repeated for each item of the list.

That’s not half confusing, Yagi!

Yeah, fair enough.

Imagine you have two columns of equal length. One column is the list of items and the other column is the frequency that they will be displayed.

Better?

Take a look:

SPREAD_BYVALUE takes two arguments:

=SPREAD_BYVALUE(list,frequency)

1. List of items you wish to spread out n times. This can be a selected range, single-cell, array or single-character/digit item.

=SPREAD_BYVALUE(A2:A11,frequency) <<range

=SPREAD_BYVALUE(A2,frequency) <<cell

=SPREAD_BYVALUE({“Crux”;”Balrille”;”Peccanouette”},frequency) <<array

=SPREAD_BYVALUE(“Crux”,frequency) <<single character or number

2. Frequency. In the SPREAD_BYVALUE function, there is a unique frequency for each item. These must all be represented as a number. The number of frequencies must match the number of list items. Frequency can be a selected range, single-cell, array or digit.

=SPREAD_BYVALUE(list,B2:B11) <<range

=SPREAD_BYVALUE(list,B2) <<cell

=SPREAD_BYVALUE(list,{2;5;4}) <<array

=SPREAD_BYVALUE(list,2) <<single number

Spread by Range

The SPREAD_BYRANGE custom function evenly distributes all the items on a list across a selected range.

If the range is not even, then one extra duplicate item will be added until the remainder value have been used. For example, if I have a range of 11 rows and I have a list of 3 items. 3 goes into 11, three times with a remainder of 2. So the first and second items will be repeated down the range 4 times (Using up the two remainders) while the last list item will be displayed 3 times:

4 + 4 + 3 = 11

If a range is smaller than the list then only those first items of the list up to the range length will be included. For example, if we have a range A1:A2 and our list is {“Cat”, “Dog”, “Goat”} then one “Cat” and one “Dog” will be assigned to the range.

SPREAD_BYVALUE takes two arguments:

=SPREAD_BYVALUE(list,frequency)

1. List of items you wish to spread out n times. This can be a selected range, single-cell, array or single-character/digit item (Same as the previous examples).
2. Range is the range over which you wish to evenly distribute the list items. This is strictly a selected range.

Check out our example:

=SPREAD_BYVALUE(A2:A11,D2:D564)

 

Take a look at the example Google Sheet below. If you want your own version to play with go to File >> Make a Copy:

Essence – SPREAD custom formula examples in Google Sheets

The Code

Quick-use Guide

Getting up and running is simple. First copy the code above. If you hover over the code at the top a menu will appear and you can select copy mode.

Next, open a Google Sheet. Go to Extensions>> App Script.  

Your Google Apps Script editor page will appear.

Paste in the code and then got to the headings bar and select > Save.

You will be prompted to rename the project. Rename then close the Google Apps Script editor.

You are now ready to use your custom functions in this Google Sheet.

Quick tip for reusability.

Custom functions do not automatically carry over into all of your projects. You will have to follow the approach above to put them into existing projects.

If you are in an organisation with your own GSuite domain you can create an add-on so all users can make use of these custom functions, but this is quite a process and beyond the scope of this tutorial.

However, it might be a good idea for you to save a version of an empty spreadsheet with all of your custom functions in it. Then, when you know that you are going to use one or more of your custom formulas, you can go to File >> Make a copy and then rename your new Google Sheet for your current project. Your formulas will be carried over with the copy.

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.

Code Breakdown

You can find out more basic details on how to create a custom function here:

Google Apps Script – How to make a Custom Function to Use in Google Sheets

Each custom function has 4 main stages:

Autocompletion

Have you ever noticed when you are using a formula that there is an autocomplete help tool that guides you through the process? Well, you can do the same with custom functions.

To create this autocomplete information you add a comment above the function. Take a look at our SPREAD_BYNUMBER example.

This comment information is based on JSDoc, a standard for documenting code. The most important thing here is to ensure that you have @customfunction at the bottom of these comments.

The @param‘s will be displayed as instructions for each argument that the user puts in. While the first part of the comments creates the “About” information.

@return explains the expected result of using the formula.

Once the comments are done, we dive into the functions.

Sanitization

When I say sanitization, I am referring to cleaning up the values inputted by the user before they are applied to the code.

My end goal for all 3 of these custom functions is to get an array of all the list items in a simple single dimensional array.

To do this I use the following code:

list is the first parameter for all 3 of the functions. We want to update the list to ensure that it is a single dimensional array.

Users can do wonderous things to create new lists and we want to be able to support them as much as we can. Users should be able to enter a single item, a column, a row or a matrix of ranges or create an array manually inside the function.

Unfortunately, the result is that the parameter can be a 2d array, 1d array or a single character or number. To fix this we need to transform anything we receive from the user into a 1-dimensional array.

Looking up at the code now, we can see that we are using a ternary operator (It’s like a single line if/else statement) to check that if the list is not an array using Array.isArray.

Then if the item is not an array, we want to make it an array. We can do this simply by putting the list between two square braces.

If the list is an array, we want to make sure it is a 1 d array. We can flatten an array by using the flat method.

You might also see we use this process too with the frequency array generated in SPREAD_BYVALUES. This is because we need an equally long array of numerical values to pair with our list.

Validation

There are one or two crucial things we need to check in the arguments of our users put in. And if they are incorrect, then we need to stop the function and return an error message containing some guidance for the user.

-SPREAD_BYNUMBER: Validation

In SPREAD_BYNUMBER, we need to ensure that the 2nd argument is a number, either typed into the function or selected in a cell on the page. We use the typeof operator to check if the parameter is not a number and it will return an error. The typeof operator checks the type of data a variable is.

-SPREAD_BYVALUE: Validation

The SPREAD_BYVALUE is probably the most convoluted of the validators. First, we need to check to ensure that the list and the frequency arrays are the same length. This is because each item of the list must have a corresponding frequency.

Then, we need to check that all items in the frequency array are numbers. Even if one is not a number the code will fail. To check all elements in the frequency array are number we use the every method. This method test whether all elements in an array pass a particular test. For us, we are checking to see if the element type is a number with typeof. If any element is not a number, every will return false and then we return an error message to the Google Sheet fo the user.

-SPREAD_BYRANGE: Validation

In SPREAD_BYRANGE we want to evenly distribute our list over a selected range in Google Sheets.

The range will be a 2-dimensional array. Where the first level of the array will be the rows and the second level of the array will be the columns. We want to ensure that we only have one column selected. To do this we check the length of the zeroeth row’s array. If the length is greater than one, then the user has selected more than one column.

Create the new distributed array

Creating the array of duplicated list items is actually quite simple and we use the same process for each of these custom function with little variation. Let’s look at the first one, SPREAD_BYNUMBER,  in detail and then briefly cover the changes for each of the other two SPREAD custom functions.

-SPREAD_BYNUMBER: Array Creation

First, on line 2, we create an empty array. This will be the array that we load the new spread of our duplicated list into.

Next, we loop through our list parameter using the forEach method. Rember that the list parameter has been converted to a 1d array.

Inside the loop, we create a new variable called duplicateN. We can use the new Array constructor to create an array with a length of n. Here, our n is the frequency parameter provided by the user. Then we use the fill method with our item from the list surrounded by square braces to create a 2d array that represents our column. Fill, takes one necessary argument, the value, and two start and endpoint variables that are optional. For us, we only need the first, value, argument.

Once the array is created, we updated the new spreadsheet array using a spread syntax(Line 8). The first item in the spread is the original array, newSpreadArray, then we add the new set of values we created  in duplicateN.

-SPREAD_BYVALUES: Array Creation

In SPREAD_BYVALUES, we need to change the number of elements in the array so that it corresponds with the frequency adjacent to the list. Remember, here that each list item has its own unique amount of times it needs to be duplicated.

On line 3, we include an optional argument, the index into the forEach loop. This will keep track of each row as we iterate down both the list column and frequency column.

Then when we create the new Array, we include the frequency array at the current index.

-SPREAD_BYRANGE: Array Creation

In SPREAD_BYRANGE, we want to evenly distribute our list over a new range. To do this, we need to do a bit of a set up first.

First, we need to get the length of the list and the range parameters fed in by the user. (Lines 1 & 2)

Next, we need to find the difference in length between the list and range arrays by dividing the rangeLength by the listLength. We need to ensure that we round the result of this down otherwise our list items may overflow our range length. To do this we use the math.floor() function. (Line 3)

Then, we need to find out how many rows in the range are remaining after the range length has been divided by the list length. To find out the remainder between two numbers we use the percentage symbol (%).

We then determine that if the range is shorter than the list array, we simply apply the list array up until the range stops. We do this with slice, trimming the end of the list array to match the length of the range.

If the range length is larger than the list we continue on…

Lines 12 to 14 should be very familiar to you now. So let’s move onto line 15.

We need to ensure that our list is distributed over the entire range, particularly if it is not evenly divisible by the range. This means that we will have to add an extra list item to each row equal to our remainder number.

To do this we check to see if the index of our loop is less than our remainder. If it is we add one to our frequency and then store it in our freq variable. Otherwise, we just use our frequency as-is for our freq variable. (Line 15)

Once this is done, the remainder of the code is the same as the other two custom functions.

Conclusion

These three spread custom functions can be very useful in a number of circumstances.

You could use the SPREAD_BYNUMBER to create sample groups or as a means to provide conditional formatting to a particular list item type.

SPREAD_BYVALUE could be used to reexpand a group into a sheet based on the provided number of each group. Alternatively, it could be used to create a weighted random list where all items are randomised but the weight given by the frequency of each item on the list could improve the probability of that item is selected. Perhaps something like this:

=ROUND(COUNTA(SPREAD_BYVALUE($A$2:$A$11,$B$2:$B$11))*RAND())

However, for me, the SPREAD_BYRANGE is going to be the most useful. I think that it is a great way to evenly assign proctors to students or sections to students.

What do you think you would use these functions for? I would love to hear in the comments below.

If you liked this post please click like. It helps me know that I am on the right track. And if you want to get a notification each time I send a post out, scroll up to the top and subscribe!

 

~Yagi.