Google Apps Script Course – Part 3: 2D Array Data Transformation of Multiple Question Items Data to Total Count of Rating Choices in Google Sheets

Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays

<<Part 2                                         <<Intro>>

In our last tutorial, we grabbed the Google Sheets count data from a single rating survey question. We then displayed it two ways; horizontally with the question item at the top, or vertically with the question item to the side.

In part 3 of our Google Sheets data transformation with Google Apps Script course we are going to expand on our current work and add two more rating question items from our survey. Each survey will have the same 1. Weak to 5. Strong survey items.

Our new list of question items will include:

• Rate your goat’s athleticism.
• Rate your goat’s agility.
• Rate you goat’s headbutt

All the information any self-respecting human companion for goats would know.

We’ll be updating our code to be able to take any number of item questions so long as they have the same choices.

Goal: Count the frequency of each rating choice of each question item.

For this task, we will need to get the total frequency of all the choices for all the questions in our rating survey. Take a look at our response data.

To follow along with your own script, make a copy of the Google Sheet below:

Part 3: 2d array count data

As you can see, all the question types have the same choice item. We’ll be able to take advantage of this to quickly modify our old code and refit it for multiple question items.

We’ll be expanding onto our current display of the choice count data. It’s at this point that you will really start to see the two formats start to diverge. Here’s what you will end up with this time around:

Item question header, subheader choices, Choice count:

Check this monster out! Now you can really see why I’ve been calling this the horizontal arrangement of the choice count. Each question is displayed horizontally.

It doesn’t look the best in a post like this but it’s a nice flat display with all the question items as the main header and then each subheader is a choice item. This will be much more useful in Part 4 of our tutorial when we add groups to our mix, but mastering this stage will make for a smoother stepping stone.

Choices header, column A – questions:

This one is much more conducive to display data in a blog like this. The top header row consists of the choice items, while there is a nice neat column of questions in column A. We’ll discover in Part 4 some of this count transformation’s limitations.

Recycle and Refit

We will be using the core data structures we developed right up to Part 2 of this course. We still need to iterate through each question item and get the frequency count of each choice of that item. It just needs to be done for multiple questions.

We’ll also review some of our Part 2 code for any repetitions to see if we can put that portion of the code into a shared function.

Go ahead and grab your old code from Part 2 or you can copy it from below:

Part 2 files

Code.GS – Old

Trans.gs – OLD

ToolBox.gs – OLD

The Code

Code.gs

Runsies()

I have some glorious news for you! All you will need to change here are your main variables – those ones we conveniently put in uppercase.

As you can see, we need to now grab all the questions in our Google Sheet data. We have three question items in our example so we will grab the range B2:D2. We’ll also need to drag our choice response range over to cover those three columns. We don’t need the item IDs so we will leave them out and start on column B.

Don’t forget to change the display location for where you want to paste the end result from your Google Apps Script to your sheet. We will slightly change the function names here and swap out the SingleQuestion portion for the MultiQuestion. Your two displayResults variables should now look like this:

• singleGroup_MultiQuestionHor_Rating()
• singleGroup_MultiQuestionVert_Rating()

Lastly, make sure you update your source sheet (what you are copying from) and your destination sheet (the sheet you are pasting too).

 

Trans.gs

We are going to shake things up a little bit in our Trans.gs file. After reviewing our old code we discovered a common pattern for our horizontal spread choice count data and vertical spread choice count data. Take a look at the similar patter in the Google Sheet:

If you look at the old Trans.gs code in part 2 you would see that there are two very similar sections of code.

These are essentially identical segments of code. The only difference is that they have different rows. This is just asking to be put in its own helper function and we are happy to oblige. We’ll call that helper function itemCount().

ItemCount(choices,results)

Let’s work a little backwards here. We’ll start off explaining our new universal helper function itemCount() and then see how it works into both the horizontal and vertical choice count functions.

The itemCount() function is called in either the singleGroup_MultiQuestionHor_Rating() or singleGroup_MultiQuestionVert_Rating() transformation functions. It requires two parameters:

1. results: All the results data from a single item question. A single item row will be built before it is received in this function.
2. choices: The choice items found in the primary variables at the top of the runsies() function.

First, we will set our array of count results for each choice to zero (Line 26).

Next, we will start our loop through each of the results.

Then we will loop through the choices and match the result to the choice. When a choice matches, we add one to the relevant column of our countResults.

Finally, we will return all the count of each choice back to our transformation functions.

singleGroup_MultiQuestionHor_Rating()

Our 2d count array needs to look like this once we are done:

Now that we have shuffled off the actual counting of the responses to itemCount(), we will use the singleGroup_MultiQuestionHor_Rating() function to simply set up the headers and create an iterator to loop through each question item.

The Variables

First of all, we will set up some variables:

We are going get the numbers of questions so that we can loop through each question item. Our header, subheader and itemsCount will all be set to zero.

The itemsCount will accumulate the choice frequency data for all the question items as we iterate through each question.

Question Item Loop

Next, we will start our loop through each question item. Remember, our goal here each time we loop through the question item is to add the question item to the end of the row in the header along with some empty spaces the length of the choices minus one. Then in the subheader, we add a new array of our choices under each question item. Lastly, we add the item count for each choice to the end of the previous item count for the previous question item using the itemsCount variable.

Take a look at the Google Sheet image again if you find this confusing. You can see that we need to add our block of data horizontally each iteration through the question items.

The Header

Just like in Part 2, we need to create our header with the question followed by 4 empty spaces to accommodate the 5 choices in the subheader. We’ll use our helper function newArrayFill()we developed in Part 2 that is stored in your Toolbox.gs file. Next, we will concatenate the empty array to our iterated question. For example, our zeroeth question is “Rate your goat’s athleticism.” The header array will look like this:

["Rate your goat's athleticism.","","","",""]

Finally, each time we iterate through the questions we concatenate or add to the end of our current header. This will have the effect of adding each question plus 4 spaces to a single row.

The Subheader

Just like in part 2, our subheader is simply the list of choices. This will look like this:

["1.Weak","2.Below Average","3.Average","4.Better Than Average", "5.Strong"];

After each iteration through the question items, another list of choices will be added to the end of the array with concat.

Getting the item count of each question

Before we can use our dinky little itemCount() helper function, we need to ensure that our function is only looking at the relevant question column. To do this we map the results array to only include the relevant question item column*.

The map method takes a function. It essentially loops through the array and can spit out any transformation array you design. For us, we want to create a new array of all the data of just one question item. We return each row with the iterator “q” being the question item column.

Now that we have our single set of choices for our question we can use our itemCount() function.

Remember our itemCount function will return just the frequency of the choices of the current question in the loop. For example:

[3, 3, 5, 6, 7]

In each loop through the questions, we will concatenate the itemCount to the itemsCount(Note the “s”). The end result will look like this for our example:

[3, 3, 5, 6, 7, 2, 2, 5, 7, 8, 1, 2, 6, 9, 6]

*I sometimes bounce from using loops to map, filter and reduce. I think that in some cases a ‘for’ loop is more readable than a method like map or filter. Also – but not in the case of this course – I will use loops over map, filter and reduce, just to a squeeze a few milliseconds to seconds out of a large data set in the hope to stay under the 6-minute limit for consumer accounts.

Putting it all together

return [header, subheader, itemsCount];

Finally, we return all three rows of data back to the runsies() function.

Take a look at the full function.

singleGroup_MultiQuestionVer_Rating()

Our 2d count array needs to look like this once we are done:

As you can see, this one looks a little neater than the horizontal version. Our main header consists of the item choices, while each question item is listed to the left before the count results are displayed to the right.

The Variables

First off, we are going to check how many questions we have in our list. Back in our runsies() function, we called in our main question item results from our Google Sheet. These question results were stored as a 2d array. The number of questions run across each column of our question row. The column items are one level deep in our row, so we need to call results[0].length instead of results.length.

For our header, we will be using the choices. We need to keep in mind that in subsequent rows, we will be adding the question item to the first column. Add a single empty character element array ( [“”] ) and then concatenate the choices to it. Make sure you nest this inside an outer array. The reason why we are doing this is that we are going to use concat later to join this array to our count data which will also be a 2d array.

We will store all the count result of each choice as we iterate through each question in our itemRows variable. We will create an empty array now to store these results.

Question Item Loop

Once we have all our variables all set up, we will loop through each of our question items. Each question item matches the row of the choices for that question, so we will be able to use this for our column reference.

First, we will set our itemQuestion. In our first iteration, this would be “Rate your goat’s athleticism”.

Getting the item count of each question

Just like in our horizontal view of our question item choice count we will make use of our new itemCount() variable to loop through each choice for each question and get the total frequency of each item selected.

We’ll first create a new 2d array with map that will contain only the variables for that item. Next, we will run our helper function itemCount.

Adding the question item to the count data

Once we get the count responses for the current question, we want to add the current question to the start of the row’s array. Then we want to add the current iteration of the combined question and choice count results to the   itemRows variable. Each question iteration it will add another row to our data.

Putting it all together

Finally, we’ll add the itemRows to the header containing the choices and return it to the main runsies() function.

Conclusion

The Complete Code

Code.gs

Trans.gs

ToolBox.gs – No change

Last Bit

To wrap up, we have now updated our code to accept multiple question items. We have shipped off collecting the frequency count for each choice of each question to the itemCount() helper function for both the horizontal and vertical displays of the count data.

We haven’t made any additions to the ToolBox.gs file, but we still use it to create arrays and submit our end results to the Google Sheet.

In our last part of our Google Apps Script array data transformation course we will transform our frequency count to not only include multiple questions, but also multiple groups. Again, we will be working off the same code and modify it for our new purpose. We will also look at some of the limitations and challenges to simply modifying code as opposed to building it from scratch. Hopefully, this will give you some insight into the decision making you need to prepare your own projects.

Subscribe now (Up the top right) so get notified when Part 4 is out!

<<Part 2                                         <<Intro>>                                         Part 4 >>

Need help with Google Workspace development?

My team of experts can help you with all of your needs, from custom app development to integrations and security. We 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.