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

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

<<Part 3                                         <<Intro>>

In Part 3 of our 2D array data transformation course in Google Apps Script, we worked out how to get the count of each choice of each question item from the survey results in a Google Sheet.

This time we are going to add a final element to our mix. Let’s say we have multiple questions and multiple groups. We want to find out the count for each choice for each question for each group.

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

That really is a mouthful.

We are going to use our same set of survey questions that asks goat companions to rate their goat on 3 question items. We are now using the same 3 sets of questions for a Goat 101 course that has 4 separate sections identified by:

1. 101-01
2. 101-02
3. 101-03
4. 101-04

So now we will want to get the count of each choice for each question for each group.

This time around, our survey also requests the user’s group before returning the user’s choice selection for each question. Take a look at the raw data below:

You can grab the Google Sheet data to follow along with your own script below:

Part 3: 2d array count data

We will be using much of our old code in Part 3 to show you how we can recycle and expand on our code for other projects.

We will be using our two example count data displays. Here, you will start to see some of the benefits of the horizontal approach emerge over the vertical approach to displaying the data.

Take a look at the end result of each of the count data displayed by group.

Item question header, subheader choices, choice count with groups by rows

Now you can really see the usefulness of having a horizontal structure when displaying count results by group. Each group result count is stacked under the other with the section identified on the far left column.

This format makes it easy to see at a glance, the different count of each choice for each question and compare them group to group. It wouldn’t take much here to add a sum or average row to the bottom of this data in Google Apps Script.

Choice header, Column A – Question, Block bundled by group

Here we are still using our vertical question approach in our first column, but we are bundling them into blocks by section. This is still quite aesthetically pleasing, but it does make it more difficult to compare results in each group. Particularly when more groups are added.

Recycle and Refit

The good news is that we can build on much of the code we have written in Part 3. For the vertical count display of questions all we really need to add is a group management function and then use our old code to create the count vertically for each group.

For our horizontal count display we will need to do some reshuffling so that we can create all the header details for all the groups before we get the count data for each group. This will be a good opportunity for you to see how we review our code and reapply it for future tasks.

We will also be adding in a sorting function to sort our data by groups. However, apart from that, we won’t have to make too many changes.

You can grab the old code from Part 3 of you can copy the old code below and paste it into your project.

Part 3 files

Code.gs – Old

Trans.gs – Old

TOOLBOX.gs – Old

The Code

Code.gs

Runsies()

The only thing we need to add to our Runsies() is data related to groups. Before we can continue with running our count we need to sort our data by groups and grab each groups start and end row.

Global Variables

On our Google Sheet, we will need to select the range of all our data. We will need our RESULTS_RANGE to include the group column this time around. This will be from column B through to column E in our example (Line 3).

We will also need to add a sort column. This will be referenced later when we use a helper function to sort the data set by column (Line 5). The sort column (SORT_COL) refers to the column of group data once it has been collected in Google Apps Script and NOT the column in Google Sheets.

You will also need to update your source sheet name and destination sheet name (Lines 9 and 10).

The highlighted areas are what we have updated or changed: 

Sorting by Groups

Underneath the resultsVals and questionVals data we will sort the dataset by group and store it in the results variable.

var results = sortByColumn(resultsVals, SORT_COL);

The sortByColumn function is a custom function we will create in the ToolBox.gs file. It essentially sorts a 2d array by a column. It takes the dataset as the first argument and the sort column as the second argument. It returns the same set of data, only sorted by group.

Group row start and end

We want to be able to grab one group’s worth of choice data at a time to get the count data for each item in each question. To do this, we will need to find out the group name, the start row for that group and the end row so that we can isolate that set of data later.

var groupData = getGroupData(results,SORT_COL);

This getGroupData function takes the sorted results dataset and the sort column as arguments. It returns a 2d array:

This function is fairly unique to this project, so I will probably just keep it in the Code.gs file, but it would be just as logical to put it in the ToolBox.gs file.

We will go into details on this one later.

Updating Display Results

In parts 1 to 3 our displayResults variable called a function that dealt directly with getting the count values for each choice of each question. We still want to do this but we should probably put an intermediary function in to deal with collating all the group’s data. This intermediary function will then call the functions that will create the count values.

Note: The displayItemChoiceCountByGroup_Hor function is currently commented out. Remember you can switch between these two to create your two different types of count data transformations just remember to update the global variables. 

Both the functions for the vertical and horizontal display of count data now take an extra argument at the end so that we can apply our groupData start and end ranges.

Both of these functions are stored in the Trans.gs file.

Here is what runsies() will look like now:

getGroupData()

The getGroupData() function has 2 parameters:

• results – the data array that was previously sorted.
• col – the group column of the data. In this case, col zero.

This function is called from the main runsies() function. The function will return a 2d array containing the group name, start column and end column for each group.

On line 12, we set a few variables. The data variable will be our array of groups and their start and end points.

We will then assign a helper counter variable that we will call dataCount and assign it a zero.  This counter variable is the place marker for the new data array. Let’s say we have found the start row of one group. The data for that group will be added to the correct location in the array and the dataCount will increase by one. Now when we find the start of the next group, that data will be placed in the array at the location of the new dataCount.

We will want to check the previous row value against the current value. If there is a change, then we will know the start and end point of each section. We will use the prev variable to store this value during each iteration over the rows.

Line 14, commences our loop through each row of our results data.

The first thing we want to ask is: if the results value of the currently iterated row of the group column don’t match the prev-ious row’s group, then we have found a new group (Line 15). We then store the group name and the row count in our data array. We then add one to our DataCount and start looking at the next row.

Alternatively, if the previous row’s group name matches the current row’s group name we will update the end row of the previous DataCount‘s array.

Finally, just before the end of each iteration, we will update the prev-ious variable with the current group so it can try and match it against the next row’s group once the loop starts again.

Trans.gs

As we mentioned previously, we will add an intermediary function for each style of our count (vertical or horizontal) results to deal with putting together all the group results. This function will also prepare any necessary headers. It will also loop through each section sending the currently iterated section’s data to the count accumulator function. The accumulated count results for that group will then be returned back to the intermediary function to be combined into a single 2d array of count results.

Let’s get started with the vertical display of count data.

displayItemChoiceCountByGroup_Vert( questions, data, choices, groupData)

You can pop the code block out as a separate window and follow along with the description.

Parameters

The displayItemChoiceCountByGroup_Vert() function is called from the runsies() function. It takes 4 parameters:

1. questions – the list of question items at the top of the survey results.
2. data – the choice results data for each question for all groups.
3. choices – the choice items. E.g. 1. Weak, 2. Below Average etc.
4. groupData – the 2d array containing information on each group’s start and end location in the data array.

Empty Spacer Row

First off, we want to create an array to store all the count data for each group. We’ll creatively call this results(Line 48).

Next, we want to be able to separate each set of group data by an empty row to make things easier to read.  We do this with our trusty newArrayFill() helper function that we created back in part 2 of this course. Our column length will be the number of choices plus the group column at the start. We will pack this row inside another array (Line 50):

var emptyArray = [newArrayFill(choices.length+1, "")];

Just the choice data

We then want to prepare our data so that we only have our choice data column and not the sections. We remove the sections column using map and shift.

The map method creates a new array by iterating through the old array and modifying it in some way. In our case, we want to remove the zeroeth value of each row and then return the rest of the choice data. We can easily remove the zeroeth item in an array with the shift method.

Loop through the groups

In our next stage, we will loop through all the groups using our groupData variable. Remember this variable contains a 2d array with a Group name column, group start column and group end column.

Our first task is to create the header. This will contain the group name in the first column and a bunch of empty cells equal to the length of choice items.

Next, we want to just take all the choice data for the current group only. We will add this into a new variable called byGroup. To get just the current group data we will use the slice method. The slice method makes a copy (Albeit shallow – but that is fine because we are not changing the values) of a selected range of an array.

We will get the group’s start and end rows by referencing the current groupData array information.

var byGroup = choiceData.slice(groupData[group][1],groupData[group][2]);

Now, we get to run that group data into our old singleGroup_SingleQuestionVert_Rating() function we built in Part 2. This function takes our list of questions as our first argument, our newly created byGroup variable of choice results, and our list of choices.

It will return all the count data for each choice for each question and store it in our transData variable.

var transData = singleGroup_SingleQuestionVert_Rating2(questions,byGroup,choices);

Lastly, we will want to combine our header for each group, the group count data and a spacing row and store it in our results variable.

results = results.concat(groupHeader,transData,emptyArray);

Finally

Finally, we return the accumulated results of all the group back to the runsies() function.

singleGroup_MultiQuestionVert_Rating()

Guess what? You don’t have to change anything about this function. It will perfectly fine just as is from what you created in Part 3. What a time saver. Ya big legend!

Don’t forget this function also makes use of the itemsCount() function we created earlier in the course.

Well, our vertical count data display was easy. Let’s take a look at the one.

displayItemChoiceCountByGroup_Hor(questions, data, choices, groupData)

In this intermediary function to display the count results horizontally, we will need to make some changes and take some of the roles that the singleGroup_MultiQuestionHor_Rating function() was doing back in Part 3. We don’t need a header or subheader before every group, so we will remove it and bring that up into this intermediary function, displayItemChoiceCountByGroup_Hor().

Let’s take a look at the code:

The Parameters

displayItemChoiceCountByGroup_Hor() has 4 parameters:

1. questions: A 2d array of question items.
2. data: A 2d array of question item choice results.
3. choices: An array of choice options.
4. groupData: A 2d array of each group name, start row and end row.

Main Variables

First, we need to know the total number of questions items. This will allow us later to iterate through each set of question choice total counts and stack them horizontally.

var numOfQuestions = questions[0].length;

Next, we will set up our header and footer row arrays. Remember we only need one of these each for the whole documents. We will need a blank space in the first item in the array because that column is dedicated to the section names (Lines 42-43).

Lastly, we will need a variable to store all our count data from all the choices for all the questions for all the groups  (There are a lot of for ‘alls’ there). We will store this in our choiceData variable (Lines 50-54).

We can already populate this 2d array with all our group names for each row. We do this by mapping the groupData and returning only the zeroeth item in each row – which is the group name.

At this stage, our choiceData will look like this:

Lets also set up an emptyArray that will be the length of the choices minus 1. This will be added to the row after each header.

Looping through the questions

To build our count data this time around we will first loop through each question item set and then add it to the right of the previous one (Line 61).

for(var q = 0; q < numOfQuestions; q++)

Prep the header

On each iteration, we need to first set up the header for that question item data set. We will grab the question from the question parameter. We will then add on the empty array to complete the header using the concat method ( Lines 63 and 64).

Prep the subheader

For our subheader, we are simply adding the array of choices for that question item set.

Get the current question’s choice data

Next, we will assign the variable questionResults for all the choice items for the current questions. Remember, the choice items are found in the data map.

Get the count data

The next stage is to get the count data by group. This seems like a rather self-contained set of things to do so instead of packaging it into the current function, I have put it into a separate one for clarity and readability of the code.

We will call our getResponseCountbyItemAndGroup() which will transform all our data to a total count of each choice for the current question item by group. We’ll send off 4 arguments with this function:

1. questionResults: Our set of choice item data just for that question.
2. choices: The five choices users can make.
3. groupData: a 2d array containing the group name, start row and end row for each group.
4. itemsCount: The main storing variable that will accumulate the count data results by group.

The getResponseCountbyItemAndGroup()function will return an updated version of the itemsCount containing a 2d array of the count of all choices for each group for the currently iterated question.

The final package

Finally, we package all the headers and the count data and return it back to the runsies() function.

getResponseCountbyItemAndGroup( questionResults,choices,groupData ,itemsCount)

The getResponseCountbyItemAndGroup() is called from the displayItemChoiceCountByGroup_Hor() function. Its main aim is to loop through each section and send a single group’s choice count data to the itemCount() function we created in Part 3 of this course. It then joins all the choice count data for each group back together before returning it back to displayItemChoiceCountByGroup_Hor().

The Parameters

getResponseCountbyItemAndGroup() takes 4 parameters:

1. questionResults: choice item results for the current question
2. choices: the 5 choices
3. groupData: a 2d array containing the group name, start row and end row for each group.
4. itemsCount: The current set of accumulated group data from previous questions.

The group loop

Before we start the group loop we will create a storing array variable for all our results called…erh…results (Line 36).

Our loop will run the length of the groupData – each row containing a group.

for(var group = 0; group < groupData.length; group++)

Just the group

Next, we will use some of our code you may recognize from our vertical count functions.  Grabbing our question results raw data we will use our groupData start and end values to slice out just the current group (Line 40).

Send away for counting

We will then send our raw choice item data byGroup off to be counted with our trusty itemCount() function we created back in Part 3 of the course. The itemCount() function takes two arguments:

1. choices: the list of choices for the question item.
2. byGroup: the newly created raw choice data for the current group.

itemCount() will then return the count for each choice for the current question and group.

Putting it all together

Finally, we will create a rowData variable that will contain the row array. Remember that itemsCount (Not to be confused with itemCount) contains all the current count data up to the current iteration of the question item from the parent function displayItemChoiceCountByGroup_Hor(). For example, if we are on the second time around the loop, itemsCount will have stored all the count data for the first question of all the groups.

We want to add on the current set of data to each row of our items count we do this here by concatenating the transData to the current group row of itemsCount.

var rowData = [itemsCount[group].concat(transData)];

Finally, we append our rowData to our results and update our results.

results = results.concat(rowData);

 

Once outside the loop, the results array is sent back to displayItemChoiceCountByGroup_Hor().

itemCount()

Hey! You’ve already created this in Part 3. Go ahead and copy and paste it in your Trans.gs file.

ToolBox.gs

sortByColumn(results, col)

I know it’s been a long journey, but we are almost there.

If you remember all the way back to the start of this part of the course, one of the first things we needed to do was to sort all the data by section.

Sorting data by a column is a pretty common thing to do with Google Sheets and Google Apps Script. This is why we put this in our ToolBox.gs file. If our project gets more complex we may be able to make use of it again for something else.

Parameters

The sortByColum() function takes two arguments:

1. results: this is the 2d data set that will be sorted.
2. col: this is the column that will be used to sort the dataset.

Sorting the dataset

We’ll use the Javascript sort method to sort our data. The sort method changes the array it has been assigned to directly. Sort can take an optional function with two parameters that we will make use of in our code.

In the first iteration of this sort function, parameter:

• a : will reference row 1 of the results.
• b : will reference row 0 of the results.

When the sort function iterates through items in an array we can check 3 conditions:

1. a === b : This is a match. We don’t want anything to change so we should return 0 (Zero).
2. a < b : a is less than b. It needs to be positioned lower than b so we should return -1.
3. a > b : a is greater than b. It needs to be positioned higher than b so we should return 1.

Once we have assigned a 0, -1 or 1 to each comparison these values are fed back to the sort method to help put your data in order. There is quite a bit more that goes into the sort method behind the scenes but this a bigger discussion outside the scope of this course.

The rest

You will still need to keep:

• pasteResults()
• newArrayFill()

We are still using them. We just don’t need to change them.

Conclusion

The Complete Code

code.gs

Trans.gs

ToolBox.gs

The Last Bit

Far out! That was a journey and a half wasn’t it?

If I’ve managed to keep you moving through this course all the way up to this point. I am super happy and if you have enjoyed the course and got something out of it, please click the like button and share the course far and wide.

In part 4 we added groups as an extra element to consider while getting the count for each choice for each question. We really started to see the benefit of the horizontal display of our choice count by group and also found some limitations for our vertical display.

When we built-in the groups to the project we added an intermediary function to handle collating all the group data. We discovered that we could still use all of the code from part 3 in our vertical display of count data but we had to make some modifications for our horizontal display.  In the Toolbox.gs, we added a sort helper function and then in the Code.gs, we added a groupData helper function to track the dataset by group.

The whole aim of this course was to show you how to transform data from Google Sheets in Google Apps Script and return it back to the Google Sheet. Our secondary aim was to show you how we can recycle functions and old code for new projects and set up our project environment in a somewhat logical manner.

Thank you so much for following along over the 4 parts of the course. It was quite a journey to write, build and prepare and I hope you learnt a lot along the way.

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.

~Yagi