How do I parse the JSON returned from Firebase into a Google Sheets row?

Question

the code in Script (FB setup is already done)

  var base = FirebaseApp.getDatabaseByUrl(firebaseUrl,secret);
  var data = base.getData();
  var rowNum = 1;
  var range = Sheet.getRange("A"+rowNum+":DH"+rowNum+"");

  for (i in data) {
    Logger.log(data[i]);
    range.setValues(JSON.parse(data[i]))
    rowNum += 1;
    range = Sheet.getRange("A"+rowNum+":DH"+rowNum+"");
  }

the Logger shows the the 112 elements from Firebase just fine, but I can't get the data parsed correctly. the JSON.parse fails saying "Cannot find method setValues(object)".

score 0 · Answer 1 · answered Feb 20 '19 at 06:47

0

The message "Cannot find method setValues(object)" is related to range.setValues() method. It means that JSON.parse() works as expected and returns JSON object, but setValues() argument must be 2D array, not JSON object.

You should convert JSON object to array before using as argument. See an example. In general case, the convertion code depends on the object structure, so we can not write the exact code here. It may appear rather long for 112 elements... But we can append it here after you give more details.

answered Feb 20 '19 at 06:47

Александр Ермолин

1,145
1
10
17

The Firebase entry is a mix of text, Booleans and integers: {'Q001-2805':{final_comment:" ", final_dateTime:"2019.02.06 12:47:33 PM", final_defLast30:false, final_def_Block:false, .... I want the keys (e.g., 'final_comment') as Row1 as header and the values as Row2. The next record would then need ONLY the values in Row3 until all records processed. – Pearland Robotics Feb 22 '19 at 00:32
At first try to split the line `range.setValues(JSON.parse(data[i]))` into 3 lines: `var j = JSON.parse(data[i]);`, `Logger.log(j);` and `range.setValues(j);`. You will meet an error, but you will check Logger output for j. If correct, we can show how to work with j. – Александр Ермолин Feb 22 '19 at 06:16
Does full Firebase response contain many keys, such as 'Q001-2805' with values (each key per data row)? – Александр Ермолин Feb 22 '19 at 06:19
The 'Q001-2805' is the only "key" for this row of data in Firebase, but it is a concatenation of 2 elements of the row that uniquely identify that row. I will try to extract the 3 lines and see what I can figure out from there and get back. Thanks! – Pearland Robotics Feb 22 '19 at 20:54
for (i in result) { var values = [ [result[i].match + ',' + result[i].team_num + ',' + result[i].pre_startPos + ',' + result[i].pre_PlayerSta + ',' + result[i].pre_cargo + ',' + result[i].pre_panel], ]; range.setValues(values); rowNum += 1; // range = Sheet.getRange("A"+rowNum+":DH"+rowNum+""); range = Sheet.getRange("A"+rowNum); } Gives me this in spreadsheet (one column!) Q001,3593,Level 2,2,false,true Q001,5431,Level 1,1,true,false Q001,5960,Level 2,2,false,false – Pearland Robotics Feb 25 '19 at 19:02
I don't want to do this for all 112 entries in FB, but I will if I have to, but why is only one column when I create the 2-dimensional array? – Pearland Robotics Feb 25 '19 at 19:03
Do not concatenate all members into one column: `var values = [ [result[i].match, result[i].team_num, ...]];` and you will have multiple columns. – Александр Ермолин Feb 25 '19 at 19:33

How do I parse the JSON returned from Firebase into a Google Sheets row?

1 Answers1