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