Arrays for Days: Let the Party Begin

Automating sheets efficiently with arrays

NOTE: If you are a paid subscriber, there is a link at the bottom to the shared Google Drive folder where the sheet in the video and all the code lives.

About Spreadsheets & Arrays

This is as good a place to start as any.

This may seem advanced for some but the goal of The Google Automator is NOT to teach you code, from your first variable, to structuring data. I am assuming my reader has some knowledge. I will, at times, cover beginning code, advanced code, problem solving, and utilitarian snippets.

Also, I’ve included this video that demonstrates some of the code I’ve included on a Google Sheet that I provide for paid subscribers.

That sheet has several simple, but useful, code snippets. 

Paid subscribers are added as a read-only viewer on the Google Drive where the actual code examples live. This will allow you to copy the code and corresponding sheets and documents to your own Google Drive. From there you can analyze them, edit them, and run them.

And via this substack, you will also be notified of any sheet updates.

Okay - on to arrays and spreadsheets.

The Relationship Between Spreadsheets and 2-dimensional arrays

In short, data on a spreadsheet, in its simplest form, is a two-dimensional array. It is a grid and that is an effective way to visualize a two-dimensional array. Specifically one that has an equal number of elements in each sub-array. Meaning, an equal number of rows (first array element) and columns or cells (second array element).

One-Dimensional Arrays

A one or single dimensional array is a list (or series) of values, referenced by their location in the array.

NOTE: Remember, arrays are zero-based in javascript (and most other languages as well) so:

thisArray = [“Jim”,”Fred”,”Mary”,”Donna”,”Fido”] produces the following.
thisArray.length() = 5 (five names)
thisArray[0] = “Jim” (first value, referenced as the zero element)
thisArray[3] = “Donna” (fourth value, referenced as the third element)
And so on [1] = “Fred”, [2] = “Mary”, [4]=”Fido”. 

Two-Dimensional Arrays

A two dimensional array is shaped like this.

thisArray =  [
   [“Jim”,”Donna”,”Jane”],
   [“Mary”,”Fido”,”Fred”],
   [“Snyder”,”Catie”,”Allen”]
]

Note how the outer brackets encapsulate the inner brackets and sub-arrays.

thisArray.length() = 3
thisArray[0] = [“Jim”,”Donna”,”Jane”] (returns the entire 1st sub-array)
thisArray[0][0] = “Jim” (Jim is the first value in the first sub-array)
thisArray[1][2] = “Fred” (Fred is the third value in the second sub-array)

The array elements are similar to the row and then column of a spreadsheet but the first row is 0 and the first column is 0.

Sheet/Cell References and Array Equivalents

Generally, when we are working with spreadsheets natively - not in code but actually just typing values and formulas into the spreadsheet, when we reference cells using A1 notation.

A1 notation is where we refer to the column letter and then the row number. So the first cell in a sheet is “A1”.

However, programmatically, it is more common to refer to a cell using its row and column location.

This makes “A1” equal to (1,1). Row 1, Column 1.

When you return (pull) the sheet data into an array - example below, the data in “A1” will now be equal to array element [0][0]. This is the equivalent of (1,1) on a sheet.

mySheet.getDataRange().getValues()

Here is a code snippet that will grab all of a Google Sheet data and turn it into a two-dimensional array that corresponds to the shape and data on the sheet.

const ws = SpreadsheetApp.getActiveSpreadsheet();
const mySheet = ws.getActiveSheet();
let myArray = mySheet.getDataRange().getValues();

Note: I could do this in a single line. It is desirable to break the creation of objects down a little bit as I may want to refer to the spreadsheet (ws) later in code and certainly will want to refer to the individual sheet (mySheet) at some point.

In a single line, it would be:

let myArray = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getDataRange().getValues();

However, later, if/when I want to refer to and take action on the sheet, I would need to refer to it again using the full chain of references rather than mySheet.

Plus it just looks clumsy and ugly.

Sheet and Array Equivalents

Note the following sheet references. I’ve placed their row, column in parentheses and their array element equivalents in [][].

Cell: “B4” = mySheet.getRange(4,2).getValue() = myArray[3][1]
Cell: “E5” = mySheet.getRange(5,5).getValue() = myArray[4][4]

If you have questions about this, please leave a comment and I will attempt to clarify.

IMPORTANT: Arrays are faster than working directly with sheet objects

Here is why this matters - or should matter. 

You can write Google Apps Scripts to navigate your spreadsheets, referring directly to sheet objects, such as rows, columns, and cells.

However, doing so is far less efficient than working with arrays. 

Due to the mathematical/structural relationship between a sheet of data and a 2-dimensional array it is desirable to convert (read) a sheet of data into an array, programmatically move through the array, and then take action on the sheet using your location in the array to determine which cells and columns to modify in on your spreadsheet.

Demonstrating Sheet & Array equivalents

If you take this code and run it against a sheet of data, it will demonstrate the mathematical/logical equivalence of a sheet and an array.

Advice: DO NOT RUN AGAINST A LARGE SHEET. A sheet with just a few rows and columns will demonstrate this fine and run much faster.

Better yet, put it in debug mode, set a break point inside the second or inner for… loop, and you can watch the values change and match your spreadsheet.

function arraySheetIterator() {
  // a function encapsulates code and often returns a value. 
  // for the very basics of Javascript, I recoommend FreeCodeCamp.com
  const ws = SpreadsheetApp.getActiveSpreadsheet();
  const mySheet = ws.getActiveSheet();
  let myArray = mySheet.getDataRange().getValues();

  for (let r = 0; r < myArray.length; r++) {
    // this will iterate through each first level array element.
    let row = r + 1;

    for (let c = 0; c < myArray[r].length; c++) {
      // this will iterate through each column/cell in the the sub-array of the current first level array element
      let col = c + 1;
      let sheetCellRef = mySheet.getRange(row, col).getA1Notation();
      let cellValue = mySheet.getRange(row, col).getValue();
      let arrayValue = myArray[r][c];
      let logMsg = sheetCellRef + " = row: " + row + ", column: " + col + " =(" + cellValue + ") or array element [" + r + "][" + c + "] =(" + arrayValue + ")";
      Logger.log(logMsg);

    }

  }
}

This function, along with several other functions is available on the Google Automator shared drive space, which is available for paid subscribers only.

WAS ANY OF THIS HELPFUL?

If so, please consider sharing this on your social media and/or any appropriate websites or forums where coders meet.