One of the great things about Google Apps Script is it uses a JavaScript syntax. This means there are lots of existing coding patterns you can use. I find this is particularly true when dealing with data from Google Sheets. Some of these patterns come straight from Stackoverflow, others come from the Apps Script community. I’m going to challenge myself to see how many of these patterns I can share on this blog.
My first patterns comes courtesy of a Google+ thread started by Bruce Mcpherson. Often in Google Sheets you want to grab the header row, this might be in preparation to read/write data, to populate some UI, or something else. Bruce’s tip was to use:
var headings = sheet.getDataRange()
.offset(0, 0, 1)
.getValues()[0];
To unpack this .getDataRange() returns a `range` of all the data in the selected sheet, a bit like a user pressing Ctrl+A to get all the data. Once we have the range the .offset() returns a new range relative from the current range. The .offset() takes up to four parameters, in Bruce’s snippet it is using three of these rowOffset, columnOffset, numRows. With .offset(0, 0, 1) we are specifying a range zero rows from the top-left, zero columns from the top-left and 1 row in height.
If as well as the header you need to read the rest of the data in the sheet then an alternative suggested by Andrew Roberts and others is:
var data = sheet.getDataRange().getValues();
var header = data.shift();
Similar to the previous snippet we are using .getDataRange() to select the entire range of data but rather than modifying the range .getValues() returns an array of all the data in the sheet. To get the header the JavaScript .shift() method is used which removes and returns the first element in the array. As you are reading all the data in the sheet I would recommend only using this method if you need to do something with the rest of the sheet data particularly if you have a lot of data as there is a performance hit.
Hopefully this pattern is useful. In my next post I’ll show some ways to process rows of data.
