Retrieving Values By Header Title for Spreadsheet

submit.md

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.name = "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];
      alldata.shift();
      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];
          }
          result.push(temp);
        }
        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;
})(this);

Demo

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
}