GSheetsUtils Library for Google Apps Script

Maintained by the team at

What is a Google Apps Script (GAS) Library?

Libraries provide a convenient way for GAS developers to quickly share and re-use methods across projects. In the GAS code editor, Libraries also offer the advantage of having their top-level methods added to the auto-complete code assistant. When a library is well documented using JSDoc notation, the autocomplete becomes especially useful. Using libraries does create slightly slower performance, so source code should be copied and run locally for published Add-ons. Learn more here.

Why would I want to use GSheetsUtils?

This Google Apps Script library provides a set of utility methods for reading, writing, and manipulating row-based data in Google Sheets. In contrast to the default range.getValues() and range.setValues() methods provided by the SpreadsheetApp service, which handle all data as 2-dimensional arrays, GSheetsUtils treats all data as arrays of plain-old Javascript objects (key-value pairs) keyed to the Sheet headers exactly as they appear in the Sheet.

For example the data shown below...

...would be returned by range.getValues() like this...

{@lang javascript}
[
  ["Name", "Pet Type", "Weight (lbs)", "Sex","Status"],
  ["Garfield", "Cat", 38, "Male",""],
  ["Sandwiches", "Cat", 26, "Female",""],
  ["Toots", "Cat", 15, "Male",""]
]

Whereas the GSheetsUtils.getRowsData() method would return it as...

{@lang javascript}
[
  {
  "Name":"Garfield",
  "Pet Type": "The Cat",
  "Weight (lbs)": 38,
  "Sex": "Male",
  "Status":""
  },
  {
  "Name":"Sandwiches",
  "Pet Type": "The Cat",
  "Weight (lbs)": 26,
  "Sex": "Male",
  "Status":""
  },
  {
  "Name":"Toots",
  "Pet Type": "The Cat",
  "Weight (lbs)": 15,
  "Sex":"Female",
  "Status":""
  }
]

We believe this object-based approach to row data offers several distinct advantages:

1. Reading and writing data is column-position-independent: Users can move columns in the sheet without changing the way your code runs. By contrast, code that references Sheets data by array index is highly fragile and prone to user error. Note: there remains some fragility due to the likelihood that a user will modify a header.

2. Code is More Self-Documenting: Referencing data values via object keys that are the actual column headers makes it much clearer what we are working with. Note: Because we use the actual header values as keys, and header values may not always be valid Javasccript variable names, we typically adopt the object['key'] (bracket) notation instead of the more widely adopted object.key (dot) notation for referencing data values. This is unconventional, but we believe it's the right choice when working with human-created Sheets that may contain Form questions, punctuation, leading numbers, etc. -- all of which cannot be within a Javascript name.

3. Moving data between Sheets is easier: Fewer translations between headers and keys means quicker and easier development, and it means that a "Student ID" in one sheet is treated just like a "Student ID" in another, for example.

Installation

1. From the Apps Script Code Editor, select the Resources -> Libraries... menu item.

2. Paste the library key MEtV19nFj3lNeMgJlb6GILfTE84O-IH7F where it says Find a Library and click Select. Choose the latest version and note the Library identifier, which is will be the library's class name in the Script editor. If necessary, the identifier can be changed.

3. Get crackin'

Source Code

...can be gotten here.

While it is not necessary to interact with the source code, you may choose to copy it into another project or fork it into your own published library if you prefer not to have an explicit dependency on the Amplified Labs codebase.