Google Apps Script – Get Headers and Values

I Thought I would share with you a small function that I made for Google Apps Scripts. Basically this will take 2 passed values, an Object that must be a sheet within your spreadsheet and a row number. This will then go and create and associative array of column headers and their values. It will use the headers notes field for the key names unless there are no notes for that header then it will use the cell value. This assumes your header row is row 1

So if you have a Google Sheet as follows

It would return an array that look like this:

Array (
    'Date'       => '2012/11/16',
    'First Name' => 'Bob',
    'Last Name'  => 'Dylan',
    'Account Number' => '4533FA'
)

Please note above that because the ‘Account’ column has a note set on it it used that name instead. This is great for issues like Google Forms that force the column header names to be the entire question so this allows you to set a name that works better for you.

////////////////////////////////////////////////////////////////////////
// 
// This function will take a google sheet object and a ROW 
// and return all the headers and there values as an assortative array
//
// sheet => object - Must be a sheet within a spreadsheet we want to get the columns and values from it
// row   => number - Will contain the row we want to pull values from
function getHeadersAndValues(sheet,row) {

  var returnValues = {}; // Initialize our associative array that we are goign to return when finished the function
  var lastColumn = sheet.getLastColumn(); // Get the last column that has data in it
  var headerName = ''; // 
  var cellValue = '';

  // Loop around the column headers and get their values then store the rows value
  for (var i = 1; i < lastColumn; i++) {
    
    // If the note field has no value use the column header
    if (sheet.getRange(1, i).getNote() == '') {
      
      // Get the value of the cell
      headerName = sheet.getRange(1, i).getValue();
    
    } else {
    
      // Get the value of the Note for the Cell
      headerName = sheet.getRange(1, i).getNote();
    
    } // End of if
    
    // Get the value for the cell in the row we are editing
    cellValue  =  sheet.getRange(row, i).getValue();
    
    // Take the HeaderValue and set it as the Key and the row we are on as the value
    returnValues[headerName] = cellValue;

  } // End of for

  // Return the array of values
  return returnValues;

}

Once you have this you can use the following loop to iterate over the array:

var row = 2;
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')
var emailBody = '';

// Call the function
var headersAndValues = getHeadersAndValues(sheet,row);

for (var key in headersAndValues) {
  if (headersAndValues.hasOwnProperty(key )) {
    emailBody += (key + ': ' + headersAndValues[key] + '\n');
  }
}

// If you echoed the variable emailBody you would have
// Date: 2012/11/16\n
// First Name: Bob\n
// Last Name: Dylan\n
// Account Number: 4533FA\n
Signature

© Caspan 2020