Google Apps Script Course – Part 1: 2D Array Data Transformation of Single Question Item Data to Total Count of Choices in Google Sheets (Updated Feb 2022)

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

<< back to Introduction                                                                            Part 2  >>

Hey, you made it! It’s time to do some coding. You know, the fun stuff.

In this part of the 2-dimensional data transformation course, we are going to get the total count of results of some form data on a Google sheet. The form data contains a single question item with a selection of different unordered choices.

To get the most accurate data we could we surveyed goats online and asked them what type of goat they are. Here is a sample of the results:

You can grade the data for Part 1 by following this link:

Part 1: 2d array count data Google Sheet

 

Goal: Count the frequency of each choice

Our goal is to use Google Apps Script to grab the question and all the choices in column B and then display the total count of each choice. Column A is just the form submission id, so we can ignore that.

Of course, we could do this pretty easily with Google Sheets alone using a COUNTIF function, but that’s not the purpose of this course. This first step is a base for our workflow to expand into more complicated versions of the same count data transformation.

We’ll return the count data back to the Google Sheet in two different ways so that we can get a better understanding of transforming 2d array data.

1. Item question header, subheader choices:

2. Choices header, column A – questions:

Being a list of goat breeds, we don’t really need to order the list. This will simplify things in our first step.

We are going to display this count in the same sheet that our source data is in, but we will set up our code so that we can change that easily; just in case we want to expand to another sheet later.

The Code

The code is split into two main functions. The runsies() function and the count transformation function. The count transformation function will either be the singleGroup_SingleQuestionHorizontal(question,results), or the singleGroup_SingleQuestionVertical(question,results) function depending on which one you choose.

runsies()

This is the main on run function for our code. This function will contain all the variables you will need to bring in from the Google Sheet to transform your data. We will need to collect the range of data and the question item here.

We will also need to call the Google Apps Script SpreadsheetApp class so that we can grab the data and set it back into the sheet once it is transformed.

The runsies() function will also call one of our two count data transformation function.

Let’s take a look at runsies():

Input variables

From lines 9 to 18 we’ve marked out all the input variables we want to use for our project. Down the track, you might like to create a sidebar or input box so that the user can input this data from the Google Sheet. However, for now, we will add in our cell, range and Google Sheet locations here directly into our Google Apps Script.

Having all these input variables in one location makes it easier to make any changes later on. This also makes it easier for other users to see what your main variables are at a glance and modify them to their own use.

Take a look at the Google Sheet image below to see what we are referencing:

As you can see, we select the QUESTION_RANGE item in B2. We also grab all the responses in our RESULTS_RANGE in B3:B26. Then, we set where we want to paste our transformed count data starting from cell E3. The results will be pasted in the same Google Sheet tab this time so the SOURCE_SHEET_NAME and DESTINATION_SHEET_NAME will reference the same sheet tab.

Spreadsheet App Class

Next, we call the SpreadsheetApp class and ask Google Apps Script to look at our active spreadsheet – the one we currently are working on – with the getActiveSpreadsheet() method (Line 20). We will pack this into our ss variable.

Using our ss variable we will ask Google Apps Script to focus on the current sheet with the getSheetByName(SOURCE_SHEET_NAME). You can see here that we are calling in the file name we entered in our input variables earlier. SOURCE_SHEET_NAME will be “Count from Choice Step 1”. (Line 21)

Grabbing the values from the Google Sheet

Next, we are going to bring in the values from our range of results and our question item cell and save them in a variable each. First, we need to get each of the ranges using the locations we provided earlier in our input using the getRange() method.  Once you collect the range of an item you can do all sorts of things with that range. You can grab its values, edit its formatting, change its properties and much more. For us, all we want to do is get the values for each of our ranges.

We use the getValues() method for this. The range values will look a little like this:

Note, that when more than one cell is referenced, getRange produces a 2-dimensional array (see resultsVals above). Each item of the outer array is the row. Inside the inner array contains the column values. Alternatively, when a single cell is referenced, like with questionVals, then a string is stored. (Lines 23-25)

transforming the data to a 2-d count array

Now that we have our data range values, we need to get the count of each choice in our survey. We have two variables here. We commented out the second one so that it won’t be called. Each of the variables calls our functions that will transform our data into the total count of each item selected in the survey form:

Both the variables take two arguments, our question and our results data. Once the function is run it will return the count of each response item in our results as the variable displayResults. (Lines 28-30)

Pasting the count data to the google sheet

Lastly, we need to display our count data back into our Google Sheet. We’ll grab our destination start location range first with the getRange() method.

The final step is sending our transformed count data to our pasteResults() function. The pasteResults() function is a small helper function we created to get the end cell of the data to be pasted and then paste in the data. It takes 3 arguments: the DISPLAY_LOCATION_START cell, the newly created displayResults and the destinationSheet.

singleGroup_SingleQuestionHorizontal(question, results)

When this function is selected, the  singleGroup_SingleQuestionHorizontal(question,results) function gets the count of each item in the results. It then creates a 2d array with the question item as the header and the choice items as the subheader. The count or frequency of each selection is displayed under each subheader item.

Let’s take a look at how our source data array (From) and our final count data array (To) look Google Apps Script-side.

Looking at our final count data 2d array we will need to add some extra spaces to our question row. Otherwise, Google Apps Script will get cranky when we try and paste our data with an unequal range.

This function has two parameters, the question item and the result items. These are derived from the questionVals and resultsVals respectively back in the runsies() function. It will return the count data 2d array.

Take a look at the code:

Note! Feel free to pop out this code in a separate window. It makes it easier to follow along.

 

Display results

The end results of our count 2d array transformation will be packaged in the displayResults variable in this function which will then be returned to the runsies() main function.

We first start by setting up our three rows inside our main parent array (line 18). The first child array is the header, so we may as well add the question item to this now. Now, our first row contains the question item and our second row is empty ready to take the subheading of each choice item our code finds. Our third row is also empty ready to take the count of each item.

looping through the rows

On each row of our data, we want to check to look at the choice item. If we have already listed it in row two of our displayResults then we just want to add 1 to the total count on row 3 of the corresponding result item.

Otherwise, if the response item is not in our new displayResults list in row two, then we want to add it. We then want to create a new column in our main header next to the question and place a 1 in the count on column 3.

Line  21 starts our for loop through our response data variable results.

We want to be able to tell our code that response type already exists in our displayResults or not. We first create  responseTypeMatch , setting it to false before we check the second row of our displayResults (line 24).

Next, we start our loop through our newly created displayResults. Here, we want to check all the column items on row [1], so we reference the length of the child array – displayResults[1].length.

Have we already added the response item?

The first thing we want to check is if we have already added the current response item to our displayResults[1] list (Line 27). If there is a match, then we set the responseTypeMatch to true (Line 28) and add one (1) to the count on row displayResults[2] (Line 31).

What do we do if the response item doesn’t exist?

If we have looped through all our displayResults[1] response items and the item does not exist, we then need to:

1. Expand our header column by 1 to take in the new item
2. Create a new response item in dislpayResults[1]
3. Add the first count to the count row for that new item

Because our responseTypeMatch boolean was not changed to true, we know there is not a match, we can use this value as our if statement.

Every new response item will go at the end of our response row (displayResults[1]), so we can simply use the push method which adds one or more elements to the end of an array. We push an empty string element to the header, the item response to the subheader and a one to the count row(Lines 90-95).

The displayResults[1] will now be ready for the next loop through the results data to catch any response item that matches it.

A little problem to resolve

Once all the items and their count have been added after we have iterated through the results loop, there will be one slight problem. Our header will be longer by 1 element. To resolve this we use the pop() method to remove the extra element on the end of our header row, displayResults[0]. Now we have nice matching columns.

 

Finally, our newly formed 2d count array is returned back to our runsies() function.

Let’s take a look at our alternate count layout…

singleGroup_SingleQuestionVertical(question, results)

When this function is selected, the 2d count array starts with the header of response items starting on the second column. On the second row, the question item is first displayed followed by item count frequency for each response.

This function has two parameters, the question item and the result items. It will return the count data 2d array.

Take a look at the 2d array we need to construct in Google Apps Script:

You can see that we will need to add an empty string as the first element of the header, and then add the question as the first element of the second row array.

Check out the code:

Display results

Again we set our returned results variable to displayResults. We immediately add an empty string element to the start of our header row and the question to the start of our count row. (Line 16).

looping through the rows

We then loop through our rows of results (Line 19). We will be comparing these again with what we have added to our displayResults[0] header row. However, before we do that we need a way to tell our code that the response item already exists. We will do this by creating the responseTypeMatch and presetting it to false.

Have we already added the response item?

This is the same as our previous singleGroup_SingleQuestionHorizontal(question,results) function.  If we find a match, we will add one to the corresponding count row, displayresults[1](Line 25026). responseTypeMatch will also be set to true (Line 29).

What do we do if the response item doesn’t exist?

Again very similar to our previous function. This time around, however, we don’t need to push empty rows to a question header. We simply add the response item to the question row and add 1 to the corresponding count row. (Lines 34 – 37).

 

Finally, the displayResults 2d array will be returned to the runsies() function.

pasteResults(StartLoc, results, sheet)

Back in our main runsies() function at the very end, we used a helper function we named pasteResults(). This function has 3 parameters:

1. The start location cell in A1 notation of where we want to paste the item.
2. The new 2d array we created
3. The new sheet tab location (Note, this is not the sheet name but the sheet called from Google Apps Script using the getSheetByName() method)

This is a pretty versatile tool if you doing a lot of data transformation and want to quickly paste in a new 2d array without having to worry about any formatting.

Let’s take a look at the code:

endloc

Before we can set our new values into a new Google Sheet tab (or the same one in our case) we need to get the A1 notation (e.g. B9) of the last cell. We’ll do this here by using offset, but there are a number of ways of doing this. I just thought this was kinda fun.

Our endloc varialble will be an A1 notation cell position equal to the last row and column in our 2d array when we set it into our sheet. We first need to call the startRange of the cell using the getRange() method inserting our start location as our argument.

Next, we apply an offset method. Our offset method can take a row offset and a column offset. The offset is how many rows and/or column away from the original location we would like to find. Our offset will be the depth of our 2d array rows minus 1 by the width of our columns minus 1. (Lines 12-14)

We can then grab the A1 notation of this offset location and store it in our endLoc variable. (Line 14)

building the new range

We can then create the range of the new location by combining the startLoc parameter with the endLoc variable in a string.(Line 16)

Lastly, we’ll send our range to the Google Sheet. This is the point where Google Apps Scripts writes the range to the Google Sheet and you will see it appear.

Conclusion

Keeping it all neat

We only have a few functions at the moment with this simple project, but we know we are going to build on this in our further stages of this course as we expand the complexity of our project. We should probably start putting our functions in different files so everything is easier to reference. Don’t worry we will keep it all basic for this course.

Create two extra Google Script files. In your script editor go to File > New > Script file.

(Note: in the new apps script you just need to select the plus sign across from file and select script from the pop-up menu)

Create two new files. Call one Trans.gs for your count transformation functions and the other ToolBox.gs for any helper scripts you have.  Put your functions in the following:

• Code.gs
  • runsies()
• Trans.gs
  • singleGroup_SingleQuestionHorizontal(question,results)
  • singleGroup_SingleQuestionVertical(question,results)
• ToolBox.gs
  • pasteResults(StartLoc, results, sheet)

Nice and neat.

The  complete code

Here’s the complete code all together in their respective files:

code.gs

Trans.gs

ToolBox.gs

Last Bit

The current code is pretty useful for when we don’t know what results are going to come in. Our code can just add a response item every time a new one is found. But what if we have a form where we are recording results from a scale, like rating data? We would come across some sorting problems using the same code. Our rating range in our header will be all over the place. We don’t want our response items to be unordered.

In the next tutorial, we will tackle rating data and look at what things in our code we need to change to meet this new problem.

 

<< back to the course Introduction                       ||                 On to part 2 >>

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