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.

Continue reading “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 Course – Part 3: 2D Array Data Transformation of Multiple Question Items Data to Total Count of Rating Choices in Google Sheets

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

<<Part 2                                         <<Intro>>

In our last tutorial, we grabbed the Google Sheets count data from a single rating survey question. We then displayed it two ways; horizontally with the question item at the top, or vertically with the question item to the side.

Google Sheete count data from rating survey

In part 3 of our Google Sheets data transformation with Google Apps Script course we are going to expand on our current work and add two more rating question items from our survey. Each survey will have the same 1. Weak to 5. Strong survey items.

Our new list of question items will include:

  • Rate your goat’s athleticism.
  • Rate your goat’s agility.
  • Rate you goat’s headbutt

All the information any self-respecting human companion for goats would know.

We’ll be updating our code to be able to take any number of item questions so long as they have the same choices.

Continue reading “Google Apps Script Course – Part 3: 2D Array Data Transformation of Multiple Question Items Data to Total Count of Rating Choices in Google Sheets”

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.

Continue reading “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 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:

what type of goat are you - Google Sheets Survey

Continue reading “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 Course – Intro: 2D Array Data Transformation for Google Sheets

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:

A1 to B2 Google Sheet Data

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.

Continue reading “Google Apps Script Course – Intro: 2D Array Data Transformation for Google Sheets”