How to send hierarchical data from firebase to goo

2019-07-31 15:42发布

问题:

below is the hierarchy of my data present in firebase database. I want to send this data to google sheet but I am unable to do so.

Error stated by google sheet is "TypeError: Cannot read property "rytf1JLXetTIINjHNku0yAvs7su2" from undefined. Details"

Below is the code that I am trying to retrieve data. Kindly let me know the mistake. Thank you!

function writeSheets() {
    var ss = SpreadsheetApp.openById("google-sheet-id");
    var firebaseUrl = "database-url";
    var base = FirebaseApp.getDatabaseByUrl(firebaseUrl);
    var data = base.getData('users/admin');

    Logger.log(data);
    var num = 2;
    range = ss.getRange("A" + num + ":E" + num + "");
    for (var i in data) {
        var values = [
            [
                [
                    [data][data][i] // Error occurs here 
                ].email, [
                    [data][data][i] // Error occurs here 
                ].userName, [
                    [data][data] // Error occurs here 
                    [i]
                ].Password, [
                    [data][data][i]  // Error occurs here 
                ].loginState, [
                    [data][data][i]  // Error occurs here 
                ].uid
            ]
        ];
        range.setValues(values);
        num += 1;
        range = ss.getRange("A" + num + ":E" + num + "");
    }

}

Sample Value of data

{
   rytf1JLXetTIINjHNku0yAvs7su2:
      {
        loginDetail: 
            {
              uid:rytf1JLXetTIINjHNku0yAvs7su2,
              password:123456,
              loginState:true, 
              userName:John,
              email:a@a.com
        }
      }
    }

回答1:

From your question and comments, I thought that you want to do as follows.

  • You want to put the values of "email", "userName", "password", "loginState", "uid" to the Spreadsheet every row.
  • You want to put the value from 2nd row of the 1st sheet in the Spreadsheet.
  • The structure of object data is like as follows. You want to retrieve each value from the object loginDetail.

    {
      "rytf1JLXetTIINjHNku0yAvs7su2": {
        "loginDetail": {
          "uid": "rytf1JLXetTIINjHNku0yAvs7su2",
          "password": 123456,
          "loginState": true,
          "userName": "John",
          "email": "a@a.com"
        }
      }
    };
    

If my understanding is correct, how about this modification?

Modification points:

  • Find loginDetail at the 2nd level, and the object of loginDetail is retrieved.
  • From the retrieved object loginDetail, each value of "email", "userName", "password", "loginState", "uid" is retrieved in order.
  • Values of "email", "userName", "password", "loginState", "uid" are put from the 2nd row of 1st sheet of the Spreadsheet.

Modified script:

function writeSheets() {
  var ss = SpreadsheetApp.openById("google-sheet-id");
  var firebaseUrl = "database-url";
  var base = FirebaseApp.getDatabaseByUrl(firebaseUrl);
  var data = base.getData('users/admin');

  Logger.log(data);

  // I modified below script.
  var keys = ["email", "userName", "password", "loginState", "uid"];
  var res = [];
  for (var i in data) {
    for (var j in data[i]) {
      if (j == "loginDetail") {
        res.push(keys.map(function(e) {return data[i][j][e]}));
      }
    }
  }
  var num = 2;
  ss.getRange("A" + num + ":E" + (res.length + num - 1)).setValues(res);
}

Note:

  • If the structure of object data is changed, it is required to traverse the object. At that time, please tell me.

Reference:

  • getRange(a1Notation)