uploading hierarchical data to firebase from google sheets

Question

I am trying to upload data to firebase where the structure of my database is hierarchical. How can I manage that structure in google sheets. I have been looking for this on many websites but found nothing.
Please help. Here is the structure of my firebase

update 1

after trying the suggested model #2, i am unable to upload repeated data lines. here is my google script:

var secret = 'in2HGSgx7uMtOvqhpIzUf1tPCI97cwnzGmae5Dg1'

function getFirebaseUrl(jsonPath) {
    return (
        'https://sheetsdemo-8d0e9.firebaseio.com/ ' +
        jsonPath +
        '.json?auth=' +
        secret
    )
}

function syncMasterSheet(excelData)
    var options = {
        method: 'put',
        contentType: 'application/json',
        payload: JSON.stringify(excelData)
    }
    var fireBaseUrl = getFirebaseUrl('TimeTable')

    UrlFetchApp.fetch(fireBaseUrl, options)
}

function startSync() {

    var sheet = SpreadsheetApp.getActiveSheet()
    //Get the number of rows and columns which contain some content
    var [rows, columns] = [sheet.getLastRow(), sheet.getLastColumn()]
    //Get the data contained in those rows and columns as a 2 dimensional array
    var data = sheet.getRange(1, 1, rows, columns).getValues()
    var dataObject = {};

    for (var i = 0; i < data.length; i++) {
        var dataRow = data[i];
        // in cell A I have my item name and in B i have my item code
        var day = dataRow[0];
        var section = dataRow[1];
        var course_id = dataRow[2];
        var time = dataRow[3];
        var course_title = dataRow[4];
        var teacher = dataRow[5];


        // we then create our first property on our data object dataObject.code-     name : { }
        dataObject[section + day] = {
            day: day,
            section: section,
            course_id: course_id,
            time: time,
            course_title: course_title,
            teacher: teacher

        };
        syncMasterSheet(dataObject)
    }
}

What specifically are you struggling with? Is your question: how can I model hierarchical data in a spreadsheet? Or are you having trouble sending the data to Firebase? — Frank van Puffelen, Sep 16 '18 at 14:13
yeah. how can i model the hierarchical data in spread sheet. — , Sep 16 '18 at 16:26
What doesn't work about the code? Did you step through the code in a debugger? Which line doesn't give you the result you expect? — Frank van Puffelen, Sep 17 '18 at 01:59
i am using real time database and it deletes the repeating values — , Sep 17 '18 at 10:08
To append each new row, use `method: 'post'` instead of `put`. — Frank van Puffelen, Sep 17 '18 at 14:29
@FrankvanPuffelen I have tried that but it keeps adding new values without updating the existing one. and it also assign key automatically which i want to assign manually. how can i achieve that? thansk — , Sep 17 '18 at 14:57
If you want to control the key yourself, put that key in the URL you `put` the JSON to. If you want to keep any existing data under the location (so perform an update), use `method: 'patch'`. Note that we went from "how do I model hierarchical data in a table?" (which I answered and you used) to "how do I use the Firebase REST API?" here. I high'y recommend putting these in separate questions in the future, as right now we're stuck adding code in comments, which is never a good use of Stack Overflow. — Frank van Puffelen, Sep 17 '18 at 15:12

score 2 · Answer 1 · answered Sep 16 '18 at 19:33

I know of of two models for storing hierarchical data in a table/spreadsheet:

to indent each level into a next column, or
to add a parent ID to each row.

indent each level into a next column

This is the most direct mapping of what you've shown to a table:

     1         2         3          4       5       6
A                                  TIME    TITLE   TEACHER
B  Monday
C           FA16....
D                      COURSE1     11:00   CCN      OWAIS
E                      COURSE2     11:30   CG       MAM

An advantage of this is that it's fairly easy to read visually. But a disadvantage is that it's easy for the column titles to get out of whack if your data is not completely homogenous.

add a parent idea to each row

In this model your flatten/normalize the data:

    1         2       3          4       5       6    
A  DAY       CODE1    COURSEID   TIME    TITLE   TEACHER
B  Monday    FA16.... COURSE1    11:00   CCN     OWAIS
C  Monday    FA16.... COURSE2    11:30   CG      MAM

This is quite similar to how you'd store the data in a relational database. After all: rows are rows, no matter what tabular structure you store them in.

If courses could be nested, you'd add a ParentID column to hold the reference to the parent course.

thanks for that. but i need to upload the data to firebase and it;s not going there as was expected — , Sep 17 '18 at 01:24
If you're having problems with writing the data to Firebase, edit your question to include the [minimal code/steps that reproduce where you are stuck](http://stackoverflow.com/help/mcve). — Frank van Puffelen, Sep 17 '18 at 01:32
i am trying the 2nd approach you told. i will post it here if i won't be able to do that — , Sep 17 '18 at 01:34

uploading hierarchical data to firebase from google sheets

update 1

1 Answers1

indent each level into a next column

add a parent idea to each row