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();
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
.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
.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
.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.