Google Apps Script, Google Sheets, SpreadsheetApp, 2d arrays
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:
[ 0 1
0 ['Goats' ,'are' ], //row 1
1 ['awesome','creatures'] //row 2
] ^Col 1 , ^Col 2
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.