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

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

<<Part 1                                         <<Intro>>

In our previous tutorial, we created a 2d array of count values for each item chosen in a survey form in Google Apps Script. In our survey, we asked users to submit what type of goat they are. We didn’t know what species of goat they identified as so we just needed to count for any goat species that was submitted.

In part 2 of our course, our Google Sheets survey data is a little different. This time around we are asking the human companion of their coding goat to:

Rate your goat’s athleticism.

Respondents then rate their goat’s athleticism on a 5-point scale:

1. Weak
2. Below Average
3. Average
4. Better Than Average
5. Strong

But, Yagi! Can’t we simply use the script in part one?

Sure, you could. However, you might come across a bit of a problem. In Part 1 we generated our choices for our count based on their appearance in the survey. What happens if none of the respondents rated their goat as Weak (This is right and just)? Weak would not be recorded in our 2d count array when we ran our Google Apps Script code.

Further, if the first user in our Google Sheet response data rates their goat as Strong, then the first choice in our 2d count array will be Strong.

That would just look weird for a summary count of a rating survey. We really need to display our count in order from 1.Weak through to 5.Strong.

Goal: Count the Frequency of Each Rating Choice

Our goal for this task is to get the count for each rating option rating our goat’s athleticism.

Here is what the raw response data looks like in our Google Sheet.

You can access the Google Sheet with the data here:

Part 2: Array count data

Either go to File > Make a copy of the Google Sheet or copy and paste the data into your own Google Sheet.

We will need to be able to display our count data with the ratings in the correct order from 1. Weak to 5. Strong.

Again, we will display our data in two ways:

1. Item question header, subheader choices:

2. Choices header, column A – questions:

Reinvent the Wheel?

One of the aims of this course is to show you how you can reuse parts of your old code in new projects to help speed up your coding.

We can safely say that the 2d count array we created in Part 1 is similar to what we want to do in this new project. There is no point looking at the problem from scratch when we have a similar template available to us.

So before we get started with our new project, let’s copy and paste in our Google Apps Script files from Part 1.

Here they are below for your convenience:

Part 1 files

Code.gs – old

Trans.gs – old

Toolbox.gs – Old

As we walk through each file, we’ll identify what we will remove and what we need to add to our script to achieve our rating data count.

The Code

Code.gs

Runsies()

We need to be able to ensure that the rating choices are displayed in the correct order.

One approach to do this is to present the choice items in the code like this:

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

We know that these will be static for this question type and we want them in this particular order so creating a CHOICES variable seems logical.

Down the track, you might want to draw the choice items from a sidebar option or a Notes section in the Google Sheet so that your users can update where necessary.

We’ll also have an extra parameter for choices in our count transformation functions that return our DisplayResults. Add CHOICES as the third argument for these two function options. I’ve also slightly changed the function names. Please go ahead and change them too.

These will be the only changes to our runsies() function. Our code will now look like this:

Trans.gs

singleGroup_SingleQuestionHor_Rating(question,results,choices)

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

This time around, we are going to create our header row and the response count row all set to zero before we create our displayResults main 2d array variable.

The Rows

For our header row, we need to create a row that includes four blank (“”) elements after the question item. Likewise, we need to create 5 new elements in our response count row filled with zeroes. The repetitive task alert should be going off, just like it did for us.

We created a helper function just for this task we named newArrayFill(len, element). newArrayFill takes two arguments:

1. Length of new array: Here, you put in how many elements you want in your array as a number.
2. Element character to be repeated: This is any element that we want to set to be repeated in each element of the array we create.

At the start of our singleGroup_SingleQuestionHor_Rating() function, add two new variables to create our header and our original response count rows.

Next, we will join the header, choices and the count row into our Display results.

For the header, we are using the concat method on the array to join our array of empty elements to it.

Go ahead and update the display results too:

Removing Response Match

We no longer have to keep track of whether the choice has been added or not to the count array. We have all the choices loaded already. This means we can get rid of all the code related to the responseTypeMatch variable.

Comparing the first characters

Quite often rating choices and responses will be saved with a number along with their description. Just like we have in our list:

Here you can see we that “weak” is assigned 1 and “strong” is assigned 5. Our choice data also retains the numbers but capitalises the first letter of the choice.

As you can see, the number is always the first character. To compare the CHOICES with the results to get the count we are simply going to compare the first character for each item in the choices and the results. The first item is the zeroeth item.

Add a zero character reference to both the results[res] [0]and the displayResults[1][resp] loop items. For example:

displayResults[1][resp] => displayResults[1][resp][0]

results[res][0] => results[res][0][0]

To tidy things up, we also created the variable choiceItemNumber for our displayResults item.

Update your function now.

No more pop

Lastly, we no longer need to remove an element from the header. Go ahead and remove the pop method at the end:

 

By now, your new singleGroup_SingleQuestionHor_Rating() function should look like this:

singleGroup_SingleQuestionVert_Rating(question,results,choices)

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

Let’s go through the editing process again with our singleGroup_SingleQuestionVert_Rating(question,results,choices) function.

The Rows

Again, we are going to add a header and a count response row. This time around our choices will be in our main header. We will need to put a space in before we display our header though.

For our response count row, we will need to first add the question, followed by a bunch of zeros(0) elements equal to the number of choices. We’ll use our trusty newArrayFill() to do this. Add these two variables just below the function title:

Again we use the concat method to join the header‘s empty single element array to the choices array and the countRow‘s question to the zeroes array.

For our displayResults array variable, we only have two rows this time around, the header and the countRow. Go ahead and update this variable.

Removing Response Match

Again, we don’t have to worry about anything to do with checking if a response exists. The choices are pre-loaded. Go ahead and remove anything related to the responseMatch variable.

Comparing the first characters

We’ll also be comparing just the first character again too. So we need to add a  [0] to our results and our displayResultswhen we are comparing the two during each iteration.

Here is how we did:

By now, your new singleGroup_SingleQuestionHor_Rating() function should look like this:

ToolBox.gs

newArrayFill()

Remember we used the newArrayFill() function to help create the extra elements for the header and the response count rows. The function creates a new array with a length of n with a desired character or integer. The function has two parameters:

• len: The length of the desired array.
• fillItem: The item to be displayed in each element of the array.

For example, If I wanted to make an array of length 7 filled with chickens. I would call newArrayFill() like this:

var coup = newArrayFill(7,"chicken")

Which would result in:

coup = ["chicken", "chicken", "chicken", "chicken", "chicken", "chicken","chicken"];

Let’s take a look at the code:

First, on line 11, we create an empty array.

On line 12 we use a for-loop to create a new element in the array len times with the desired fillItem.

We then return the final arizzle (That’s what the cool kids call arrays) back to wherever the function was called.

Go ahead and add this helper function to your ToolBox.gs file under the pasteResults() function.

Conclusion

The Complete Code

Code.gs

Trans.gs

ToolBox.gs

Last Bit

This part of the course walked through how to look at your old code and quickly adapt it to a new project. We focussed on what changes would need to be made to get count data on a rating system.

We also added a new helper function to our ToolBox.gs file the newArrayFill() function. You should be able to use this in your other projects.

In Part 3 we will be increasing the complexity of our project by adding multiple question items with the same rating system. We’ll be building on what we have already learnt to create this. At this stage, you will really start to see the two different 2d count arrays diverge and can start to see the benefits of both.

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

~Yagi

<<Part 1                                         <<Intro>>                                         Part 3 >>

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.