Google Apps Script – Get Headers and Row Values

I Thought I would share with you a small function that I made for Google App Scripts. This function will take 3 passed values

  • sheet – A sheet object from Google Sheets
  • headerRow – The row number of the header values
  • row – The row number that contains that values you want to pull

If you have a Google Sheet as follows

This function would build an object that looks like this:

object = {
    'Date' : '2012/11/16',
    'First Name' : 'Bob',
    'Last Name' : 'Dylan',
    'Account Number' : '4533FA'

Please note above that because the ‘Account’ header column has a note set on it, the note value is used instead of the cell value. This is great for situations like Google Forms that save the text form the question as the header value in Google Sheet, these header values can be very lengthy to help with input but you might not want this when retrieving the values for an email. This allows you to set a value you want to use instead.

 * This function will construct an object using the header's cell value
 * or the header's note value (A note value will supersede a cell value) 
 * @param {object} sheet A Google Sheet Object
 * @param {string} headerRow The row that contains your headers
 * @param {string} row The row that you want to take values from
function getHeadersAndValues(sheet, headerRow, row) {

  // Loop around each column header
  for (var i = 1; i <= sheet.getLastColumn(); i++) {
    // If the column header has a value
    if (sheet.getRange(headerRow, i).getValue()) {

      // If the column header's note field has a value
      if (sheet.getRange(headerRow, i).getNote()) {
        // Use the header's note value
        var headerValue = sheet.getRange(1, i).getNote();

      } else {

        // Use the header's cell value
        var headerValue = sheet.getRange(headerRow, i).getValue();


      // Create a new proptery name value as the headerValue and set the proptery's value to be the row's cell value
      this[headerValue] = sheet.getRange(row, i).getValue();



Here is an example of how to call the function:

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  values = new getHeadersAndValues(sheet,2,1);

  Logger.log( values['First Name'] ); // Bob
  Logger.log( values['Last Name'] );  // Dylan

© Caspan 2021