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:

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

The Code

The following code contains an example. All the example data is drawn in from the runsies() function. The main function to add and remove columns is the addRemoveColByRowWithArray_() function.  You see this being executed in the runsies() function along with a bunch of arguments.

You should substitute the runsies() function for your own function for your project and input in all the relevant arguments before calling the addRemoveColByRowWithArray_() for this project.

Quick use guide

To run in your own project, copy and paste all the functions starting from addRemoveColByRowWithArray_() all the way down to the bottom of the script above.

Then, paste the function call, addRemoveColByRowWithArray_(), into your own project just like in the runsies() example.

addRemoveColByRowWithArray_() takes five arguments:

1. Matrix (2d array): the 2d array of values that needs changing.
2. Row Reference (number): this is the row in the Matrix that will be referenced against the search matrix. This will probably be the header of your current data row you want to change. Note that the number is based on the first row you selected. For example, if your selected range is from C3:E19 and your header is in row 3 then that will be the first row in your selected range and you would make the row reference 1.
3. Search Matrix (2d array): the 2d array of values that you will use to update your Matrix array.
4. Search Column or Row (number): If your search matrix has headers running across the row or across the column (more on this next) select which column or row you will be using to compare to the Matrix to update it.
5. Same Format (boolean)(optional): This last optional arguments, allows you to transpose your Search Matrix if the search matrix has its header reference running down a column instead of across a row like the Matrix. If the Search Matrix has headers running across the row then you can either select true or leave it empty. Otherwise, you can mark it false if your headers run down the column.

The addRemoveColByRowWithArray_() will return a matrix of updated values for you to set back into your Google Sheet.

Some limitations

This function is pretty handy but there are some limitations that are outside the scope of this simple tutorial.

First, you will need to have a relatively similar Matrix and Search Matrix that is roughly sorted in the same order. This is because when new columns are added from the Search Matrix it will reference the previous matching headers between the two matrices. If the two headers are too far out of order, then it will not do a good job adding in the new columns.

Secondly, this approach only takes in values and returns values. If you have formulas in your Matrix, they will be removed. Having said that, you can always run this function on regions that don’t have formulas in them.

 

That’s about all you will need to get started quickly. If you are interested in seeing an example or getting under the hood, read on!

An Example

Let’s say I have a jibberish Google Sheet with rows containing a category item of three-letter words:

• A short id for these words
• A number assignment row
• Some randomly assigned greeting words that have not yet been completely filled out
• Some overly enthusiastic “go” words in each of the row’s cells.

Something like this:

Essentially, a list of category items in each column with a set of related data underneath each item.

We’ll call this Google Sheet tab our ‘Data’ sheet.

Occasionally we get updates to our list of categories and we need to update this sheet by adding in new categories and deleting some redundant category columns.

This updated data might be displayed in the same format with categories running horizontally across a single row or the categories might be listed vertically:

We’ll call this data we want to search, our ‘Key’ Sheet. This data could come from any other Google Sheet workbook, but for this example, we will put it in the same workbook.

Let’s use the category items to compare the two sheets. If there is a category item not in the ‘key’ sheet we will remove it and its entire column from the ‘data’ sheet. Likewise, if there is a category item in the ‘key’ sheet but not in the ‘data’ sheet then we will insert that in the correct location in the ‘data’ sheet along with the id. Users can later fill out the rest of the information.

Let’s move over to the Google Apps Script editor and create a runsies() function to collect our data, run it through the add and remove process and update the ‘data’ sheet.

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.

runsies()

Take a look at the code. Note, I have made it overly explicit for the benefit of multiple levels of learners.

Globals

From row 4 to 20 we set up all the variables we need for this task.

Any variable with the suffix SEARCH is the one with the new list of headers. Variables that contain UPDATE reference to the original data that will be updated.

Lines 5 and 6 calls up the two spreadsheets. We do this by using Google Apps Scripts SpreadsheetApp Class. Where possible I generally prefer to grab a spreadsheet by its ID.

We do this by using the openById(paste ID here) method. If both your SEARCH and UPDATE data are on the same Google Sheet workbook, then you can simply reference SEARCH_SS when creating UPDATE_SS. Just like I have done.

Next, we use getSheetByName(Sheet tab name here) to locate the correct Google Sheet tab you are on (Lines 8 & 9). In this example, my SEARCH matrix is in the Keys sheet tab and my UPDATE matrix is in the Data sheet tab.

Now we need to get the range of both of our SEARCH and UPDATE matrices with the getRange() method (Lines 11 & 12). The getRange method can take a numerical range or a range based on A1 notation. I’ve chosen the latter here.

The final global variables we need is to set which ether column or row we will be using as our search column or row.

Let’s say our SEARCH data in our Key sheet runs vertically, we would then select the column. For us, the column we want to compare is column A. The first column in our range, so we would set our SEARCH_COL_ROW to 1.

For our Data or UPDATE range, it will always be displayed with headers running across the to of the sheet.  We will set our UPDATE row to 1.

Run addRemoveColByRowWithArray_()

Once you have all your Global variables in place, it’s time to run the code. Scroll up to the Quick Use Guide for a breakdown, but it should make sense now that you have an understanding of the variables.

addRemoveColByRowWithArray_() will return the values of the updated matrix in the new_matrix variable.

Returning the Updated Matrix back to the sheet

In the final stage of the runsies() function, we need to return the updated data back to the sheet.

Unfortunately, we cannot use the old set of getRange() parameters from our original UPDATE_RANGE variable because the column range is likely to have increased or decreased in size. Instead, we will get the start row and column of the range using the getRow() and getColumn() Google Apps Script methods respectively (Lines 28 & 29).

Then we can get the row length and column length of our new_matrix (Lines 30 & 31).

Remember, I mentioned earlier, that the getRange() method can take either a numerical value or A1 notation? Well, this time we will use the numerical value arguments. The getRange() method in this format takes values as follows:

With our new range, we now simply use setValues() to display the updated matrix on our Google Sheet (Line 3).

addRemoveColByRowWithArray_()

This is where all the magic happens. In our example, this is called from the runsies() function. You should put it anywhere in your project where you will use it.

Parameters

The addRemoveColByRowWithArray_() function uses 5 parameters:

1. matrix (2d array): This is the 2d array of data we need to check. In the example, this will come from UPDATE_MATRIX in the Data sheet tab.
2. rowRef (number): The Data sheet row we will be comparing against the Keys sheet row or column reference. This comes from UPDATE_ROW in runsies().
3. searchMatrix (2d arrray): This is the array we will use to update the Data matrix. It is drawn from SEARCH_MATRIX in the Keys sheet tab.
4. searchColOrRow (number): The row or column to reference in the SEARCH_MATRIX.
5. sameFormat (boolean): If the matrix and searchMatrix both have their references running across the Google Sheet in a row then this is true. However, if the searchMatrix reference runs down a column, like in our example, then this is marked, false.

Setting up the constants

The first thing we need to do is check if the searchMatrix  is the same format as the matrix. If the searchMatrix runs down a column instead of across rows, then it will need to be transposed.

To do this we use a helper function created called formatSearchArray_(). This takes 3 arguments:

1. matrix
2. searchMatrix
3. sameFormat

TheformatSearchArray_() will then transpose the searchArray if sameFormat is false. It will then fill out any extra rows of data with an empty text value in each cell so that it matches the number of rows of the matrix 2d array variable before returning it to the newSearchMatrix variable.

We then use this newSearchMatrix to find the searchArray (Line 16). We will use this as our reference row to compare against our matrix reference row.

On line 17, we grab the matrix array that we will reference in the arry constant variable.

Remove any column not in the search array

Finally, we can get down to get our task done!

Lines 19 – 24, remove any column from the matrix that is not present in the searchArray.

For example, our current searchArray contains these values:

[Cat, Fat, Sit, Bin, Bog, Bat, Zap, Zen, Kin, Boy]

And, our arry contains these values:

[Cat, Fat, Bin, Bah, Bat, Zap, Kin, Boy, Toc]

We can see that the searchArray does not contain, Bah and Toc, so those columns need to be removed from the matrix.

Javascript’s map and filter to the rescue here.

We first use map to iterate through each row of data in the matrix. While in each row we use filter to get the index (location) of each item in the row.

Within filter, we want to check the searchArray using the includes method to see if the current index of the arry variable is included in the searchArray. If it is, then that current rows item will be kept and returned in the matrixFiltered variable.

This process moves through each item in the row of the matrix. Once the row is complete, it moves onto the next row and completes the filter process again until all rows are done.

Add any column from the searchArray into the Matrix

In the next, stage of the function, we want to add any column to the newSearchMatrix that does not appear in the arry but is in the searchArray.

To do this we are going to stretch the use of map (abuse?).

We are not actually going to return anything from map, which is the common practice. (I can feel the anger bubbling in some readers, but, you know… hackity hack hack).

All our changes will update the matrixFiltered array we created earlier.

First, we map through the searchArray. We will need to note both the value and the index of each item in the array. If the value in the searchArray does not match the first row of the matrixFiltered array at the same index, then we need to add that column.

To do that, we map through matrixFiltered taking its row, and the index of that row (r_index). On the assigned row, we splice in the new column item:

row.splice(index,0,newSearchMatrix[r_index][index])

Splice takes 3 arguments:

1. Start index of the array. For us, this is the currently iterated index of the searchArray.
2. The number of items to delete. This will be zero. We are not deleting anything here.
3. The elements to add to the array. This will be the  value in the row and column of the newSearchMatrix.

 

Finally, matrixFiltered is returned back to the runsies() function.

On to the helper functions!!!

formatSearchArray_()

The formatSearchArray_() function modifies the searchMatrix by transposing it if the reference data is running down a column and then fills out the array with empty text (“”) to match the matrix array’s length.

The formatSearchArray_() uses 3 parameters:

1. matrix (2d array): This is simply used to get the row length we need to add to the updated version of the search array.
2. searchMatrix (2d array): This is the array we will use to update the matrix.
3. sameFormat (boolean): If true, nothing changes, if false, then we transpose the searchMatrix to match the matrix.

First, we check if the searchMatrix needs to be transposed.  This is done with a ternary operator (Line 16). It says that if sameFormat is true, proceed with the searchMatrix. However, if the sameFormat is false, run the transpose_() helper function on searchMatrix.  This will be returned as the ary variable.

Then we check the length of rows of the ary variable and if it is less than the length of rows of the matrix, we need to add a few blank rows into the searchMatrix (Line 18). Otherwise, when we move on to pasting the final result back into Google Sheets we are going to get a row mismatch error.

To add any extra rows, we must first find the difference in length between the two matrices (Line 19). Once we have that, we are going to make use of a little helper function we used in another project newArrayFill().

newArrayFill() creates a blank array of n values. This function has two arguments. The length of the array, and what value you want to prefill the array with.

For us, we will want to add the length (number of columns) in the row and set the elements to an empty text (“”).

Next, we will use a while loop to add a row to ary equal to the difference  (dif) in rows between the matrix and the searchMatrix.

We then return the ary to addRemoveColByRowWithArray_().

transpose_()

The transpose_() function is a pretty standard coding problem that is often used in job interviews from what I hear. There are a number of solutions, but this one is my favourite. You can check out more on this here.

This function takes a 2d array and rotates it clockwise and returns it.

We use the reduce method here. First, we set the prevRow and nextRow parameters. Don’t forget to add an empty array as an initial value at the end (Line 13) to be referenced first. Then, for every row we run map. If there is an element in a previous row’s current column index, then we use concat to join row at the same index as the col.

Conclusion

This is a pretty fast way to update column base on a modified key set in Google Sheets using Google Apps Script.

Upon reflection, I almost wrapped all the helper functions into one method along with the primary addRemoveColByRowWithArray_(). Though  I am not always certain that is a good approach in smaller projects. Big projects, for sure; you don’t want a heap of function names floating around causing the greater potential for conflict.

Anyway, tell me what you think.

This project was a fun opportunity for me to try out Google Apps Script’s new V8 runtime and some of the juicy modern Javascript that comes with it. Please excuse me if I went a little overboard in my excitement.

Happy coding!

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.

~Yagi