Skip to content

Instantly share code, notes, and snippets.

Last active Jan 28, 2018
What would you like to do?
Retrieving Values By Header Title for Spreadsheet

Retrieving Values By Header Title for Spreadsheet

This is a sample script for retrieving values by header title for Spreadsheet. This is created by Google Apps Script. The main script is as follows.

Main script :

When the instance is retrieved, all data of the sheet is analyzed. So when the each value is retrieved, the speed is fast.

function GetValueByKey(sheetname) {
    return new getValueByKey(sheetname);

(function(r) {
  var getValueByKey;
  getValueByKey = (function() { = "getValueByKey";

    function getValueByKey(sheetname) {
      var alldata, e, header, ss;
      try {
        ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname);
      } catch (error) {
        e = error;
        throw new Error("Error: No sheetname (" + sheetname + ").");
      alldata = ss.getDataRange().getValues();
      header = alldata[0];
      this.manageddata = (function(header, alldata) {
        var i, j, k, key, len, len1, result, temp, value;
        result = [];
        for (j = 0, len = alldata.length; j < len; j++) {
          value = alldata[j];
          temp = {};
          for (i = k = 0, len1 = header.length; k < len1; i = ++k) {
            key = header[i];
            temp[key] = value[i];
        return {
          values: result,
          headerLength: header.length,
          dataLength: alldata.length
      })(header, alldata);

    getValueByKey.prototype.getValue = function(index, key) {
      return this.manageddata.values[index][key];

    getValueByKey.prototype.getAllValues = function() {
      return this.manageddata;

    return getValueByKey;

  return r.getValueByKey = getValueByKey;


Following sheet is a sample sheet for this.

Sample 1 :

The start of index is 0.

function main(){
   var gvbk = GetValueByKey("### sheet name ###");
   var res = gvbk.getValue(5, "Header5");

>>> value_e6

Sample 2 :

Retrieve all values as JSON.

function main(){
   var gvbk = GetValueByKey("### sheet name ###");
   var res = gvbk.getAllValues();
  "values": [
      "Header1": "value_a1",
      "Header2": "value_b1",
      "Header3": "value_c1",
      "Header4": "value_d1",
      "Header5": "value_e1",
      "Header6": "value_f1",
      "Header7": "value_g1",
      "Header8": "value_h1"
      "Header1": "value_a2",
      "Header2": "value_b2",
      "Header3": "value_c2",
      "Header4": "value_d2",
      "Header5": "value_e2",
      "Header6": "value_f2",
      "Header7": "value_g2",
      "Header8": "value_h2"
      "Header1": "value_a3",
      "Header2": "value_b3",
      "Header3": "value_c3",
      "Header4": "value_d3",
      "Header5": "value_e3",
      "Header6": "value_f3",
      "Header7": "value_g3",
      "Header8": "value_h3"
      "Header1": "value_a4",
      "Header2": "value_b4",
      "Header3": "value_c4",
      "Header4": "value_d4",
      "Header5": "value_e4",
      "Header6": "value_f4",
      "Header7": "value_g4",
      "Header8": "value_h4"
      "Header1": "value_a5",
      "Header2": "value_b5",
      "Header3": "value_c5",
      "Header4": "value_d5",
      "Header5": "value_e5",
      "Header6": "value_f5",
      "Header7": "value_g5",
      "Header8": "value_h5"
      "Header1": "value_a6",
      "Header2": "value_b6",
      "Header3": "value_c6",
      "Header4": "value_d6",
      "Header5": "value_e6",
      "Header6": "value_f6",
      "Header7": "value_g6",
      "Header8": "value_h6"
      "Header1": "value_a7",
      "Header2": "value_b7",
      "Header3": "value_c7",
      "Header4": "value_d7",
      "Header5": "value_e7",
      "Header6": "value_f7",
      "Header7": "value_g7",
      "Header8": "value_h7"
      "Header1": "value_a8",
      "Header2": "value_b8",
      "Header3": "value_c8",
      "Header4": "value_d8",
      "Header5": "value_e8",
      "Header6": "value_f8",
      "Header7": "value_g8",
      "Header8": "value_h8"
      "Header1": "value_a9",
      "Header2": "value_b9",
      "Header3": "value_c9",
      "Header4": "value_d9",
      "Header5": "value_e9",
      "Header6": "value_f9",
      "Header7": "value_g9",
      "Header8": "value_h9"
      "Header1": "value_a10",
      "Header2": "value_b10",
      "Header3": "value_c10",
      "Header4": "value_d10",
      "Header5": "value_e10",
      "Header6": "value_f10",
      "Header7": "value_g10",
      "Header8": "value_h10"
  "headerLength": 8,
  "dataLength": 10
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment