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

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:

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. 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

#### 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.

##### 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 `displayResults`when 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

### 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

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? Make an enquiry on my 'Hire me!' page. I occasionally pick up projects. Alternatively, Fiverr’s your best bet to find a skilled Google Apps Script developer to solve your problem quickly and professionally. *

*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.