CODEWITHSUNDEEP
jsongoogle-apps-scriptgoogle-sheetsfirebase
coddie
coddie

Reputation: 33

Google App Script - Using column headers as name in JSON key/value pair

I have the following code, which I am using with the 'FireBaseApp Library Resource' to update FireBase.

function writeDataToFirebase() {
      var ss = SpreadsheetApp.openById(".......mySheetRef.........");
      var sheet = ss.getSheets()[0];
      var data = sheet.getDataRange().getValues();


      var dataToImport = {};
      for(var i = 1; i < data.length; i++) {
        var EventName = data[i][0];
        var Description = data[i][1];

        dataToImport[EventName] = {
          EventName:EventName,
          Description:Description,
          EmailAddress:data[i][2],
          who:data[i][4],
          client:data[i][5],
          partners:data[i][6],
          EventDate:data[i][7]
        };
      }
      var firebaseUrl = "https://myFireBase.firebaseio.com/";
      var secret = "secret_here";
      var base = FirebaseApp.getDatabaseByUrl(firebaseUrl, secret);
      base.setData("", dataToImport);

    }

All works perfectly, except ...

To add scalability to the project, I would like to dynamically add the Key "Name" from the Row 1 Column headers. So if the column name changes or more columns are added the script wont need to be rewitten each time.

so, instead of hard coding ...

EmailAddress:data[i][2],
I would like
"row 1 column header value":data[i][2],

I can't seem to make that bit work :-(

Any help greatly appreciated.

UPDATE: I have tried adding ....

    -> var Headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
 Logger.log(Headers);

  var dataToImport = {};

for(var i = 1; i < data.length; i++) {
  var EventName = data[i][0];
  var Description = data[i][1];

  dataToImport[EventName] = {
    EventName:EventName,
    Description:Description,
  -> Headers[2]:data[i][2],
    who:data[i][4],
    client:data[i][5],
    partners:data[i][6],
    EventDate:data[i][7]
  };
}

But this creates a "Missing : after property ID. " error !

So I am still looking for a way to capture the value of the column headers dynamically.

Upvotes: 2

Views: 2485

Answers (2)

Fausto R.
Fausto R.

Reputation: 1334

The following code might work for you as it does for me, with any columns configuration. You need to assign the uniquecolumn header (unique in the example below)

var sheetsid = "your-google-sheet-id-here";
var fbpath = "https://your-app-here.firebaseio.com/fromsheet";
var fbsecret = "your-secret-here";
function sheet2FB() {
  var sheetname = "Data";
  var sheet = SpreadsheetApp.openById(sheetsid).getSheetByName(sheetname);
  var uniquecolumn = "unique";
  var objectsarray = xsLibSheets.getRows(sheet);
  var fbready = objectsarray.reduce(function(p,v){ p[v[uniquecolumn]] = v; return p; }, {});
  var fb = FirebaseApp.getDatabaseByUrl(fbpath, fbsecret);
  fb.setData("", fbready);
  Logger.log(JSON.stringify(fbready,null,2));
  return;
}

The function getRows() get the data from a sheet as a json objects array using the headers as keys. You can find the library code on this link

Upvotes: 2

Alan Wells
Alan Wells

Reputation: 31300

The code has a new variable, row1_Col2_HdrVal that always gets data from index zero, which will be row one, IF you get the range with getDataRange().

var dataToImport = {},
    row1_Col2_HdrVal = data[0][2],
    innerObject = {};

for(var i = 1; i < data.length; i++) {
  var innerKeyName = data[i][0];

  var j=0;
  for (j=0;j<length;j+=1) {
    innerObject[innerKeyName] = data[i][j];
  };

  dataToImport[EventName] = innerObject;
}

I don't know if this is what you need. If not, give feedback in the comment section.

Upvotes: 0

Related Questions