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”

How to Automatically Navigate to the First Empty Row in a Google Sheet using Google Apps Script

Google Sheets, Google Apps Script: SpreadsheetApp, PropertiesService, Binary Search, onOpen, Button

Ever had to open a huge data entry spreadsheet an all you want to do is enter your data and move onto another task? Instead, you have to waste precious time navigating all the way down to the bottom of the page to add your data.

Boo!!!

Wouldn’t it be cool to just jump down to the first empty row and get clicky-clacking away?

Behold, dear biped! This friendly coding goat has a little gift for you.

In this tutorial, we are going to tackle two ways to automatically move down to the first available empty cell. One will be super easy and the other slightly more tricky, fun and versatile (You can thank one of the Google Apps Script outreach gurus for the inspiration for this one).

We’ll show you how to set up this little code so it can be run via a button at the top of your page or when you open the Google Sheet workbook each time.

Note! As always feel free to read the entire tutorial or take what you need. There is a quick start guide for those people who just want to copy and paste into their own projects and a deep dive for those who want to understand the code. 

Contents

Scenario 1: The Super Easy Way

Let’s say we have a simple data set that the user needs to enter a new row of data each time the open the Google Sheet workbook.

Something like this:

Sample File - Google Sheets first empty cell at end of data

Continue reading “How to Automatically Navigate to the First Empty Row in a Google Sheet using Google Apps Script”

%d bloggers like this: