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

Note! This course requires a rudimentary understanding of very basic Javascript concepts and of what an array is. Nevertheless, if you want to come in blind you should be able to pick things up with a bit of Googling. Alternatively, if you just want the neat code snippets for your own project, simply skip the course stuff and copy and paste away. 

~Yagi

Introduction: 2D Array Data Transformation for Google Sheets

One of the major regular tasks that Google Apps Scripts can do for Google Sheets is to automate the transformation of data from one state to another.

Sometimes this task can simply be accomplished by building dynamic templates using the vast functionality of Google Sheets. Other times automation via Google Apps Script is a better tool for the job.

When working with Google Sheets data in Google Apps Script you are immediately going to be met with the 2D array. Take a look at this simple range of Google Sheet data from A1:B2:

If we grab this data in Google Apps Script, it will appear like this:

As you can see, we are looking at a nested array that is two levels deep commonly known as a 2-dimensional array.

Remember that while our spreadsheets rows and columns start at 1 our arrays start at 0.

Spreadsheet data is transformed into a 2d array first by row and then by column data. In the above example, we can see that row 1 is the zeroeth element of our array and that zeroeth item contains column A (‘Goats’) and column B (‘are’) data items. Then in the 2nd row or array element 1, we get those two column values of ‘awesome’ for column A and ‘creatures’ for column B.

Now we have the basics out of the way, let’s dive into transforming this data.

In this course, we are going to take a bunch of survey data in our Google Sheet and get the total count of each selection from the survey data responses using Google Apps Script and then redisplay it back into our Google Sheet.

Stages of the course

Each part of the course will gradually increase in complexity from the previous one:

• Part 1: Get the total count of each selected result from a single question item from some choice data.
• Part 2: Get the total count of each selected result from a set of rating survey data from a single question.
  • Level up: Requiring to sort data.
• Part 3: Get the total count of each selected result from a set of rating survey data from multiple questions.
  • Level up: Applying what we learned to multiple questions.
• Part 4: Get the total count of each selected result from a set of rating survey data from multiple questions and multiple groups.
  • Level up: Applying what we learned to display multiple group total count data.

To help us better get a feel of transforming data we will apply two ways of displaying the count of the data. This will become more useful from Part 3 onwards but it will be an important concept to grasp in Part 1, so stay with it.

Count Transformation 1: item question header, subheader choices, horizontal extensions

What on earth are you talking about, Yagi?!

It a mouthful. I know.  Let’s take a quick look at a single item-single question form data total count:

In this option, we have set our header to the question item. On the next line, we have the subheader choices. Finally, on the last line, we have the count for each choice.

If we were to expand on this count format for more question items, then subsequent questions would be appended to the right along with their choices.

This is pretty handy for displaying large amounts of data on a sheet that the user can scroll across and view. If our result data is further divided by groups. We could then add a group column to the far left and add each group count for each choice on a row.

count transformation 2: Choices header, column A – questions, vertical extensions

In this count data transformation, our main header is the choices. Our column A then contains the question item.

If we want to add more questions, we will add them below each other. However, we must assume that all questions in the form survey have the same choices.

Adding more data by groups here becomes a little messy. We’ll achieve this in Part 4 by adding a group header and then the whole set of question items and their count for each group as a block; one after the other. Nevertheless, this data structure could be useful for setting group reports on separate Sheets.

Workflow and reusability

When working on a project its also a good idea to break things down into reusable parts. We’ll try and build functions in a way that we will be able to reuse them down the track as we expand into the more complex parts.

As we explore these smaller reusable parts, have a think about how you might be able to implement them in your own Google Apps Script project.

We’ll also try and work on our layout in our project and put our Functions in separate *.gs (Google Apps Script) files. This will help us find different components of our script better and help others follow along if they need to read your script in the future.

One of the benefits of Google Apps Script is that we don’t need to import other *.gs files across our project. Essentially, your whole project’s Google Script is one big file. The separate file indicators in your Script Editor are simply just a neat way of organizing your functions.

Finally, we’ll talk you through different aspects of workflow and the things we were considering at the time. Everyone looks at a project differently and its always nice to get inside someone’s head while viewing their project.

Let’s Go!

It’s time to get cracking! Click on the link and dive into Part 1 of this course:

Get the total count of each selected result from a single question from some choice data.

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

Part 1 >>