需要一种将特定数据从Firebase RealtimeDB提取到Google表格的方法 [英] Need a way to extract specific data from Firebase RealtimeDB to Google Sheets

问题描述

我正在尝试使用AppScript将Firebase RealtimeDB数据传输到Google表格中.

I'm trying to transfer Firebase RealtimeDB data into Google Sheets using AppScript.

我需要一种从数据库中提取ID，部门和姓氏字符串的方法，同时它是从下面的日志中接收到的.

I need a way to extract ID, Department, and Surname strings from the DB while it is received as such from the Logs below.

我使用.childByAutoID()对数据库具有以下作用.

I use .childByAutoID() which has the following effect on the DB.

我的数据库:

电子表格中所需的结果:

Desired result in spreadsheet:

function writeSheets() {
var firebaseUrl = "<my-database>.firebaseio.com/Attendees";
var base = FirebaseApp.getDatabaseByUrl(firebaseUrl);


var data = base.getData();

  console.log(JSON.stringify(data));




var ss = SpreadsheetApp.openById("<my-spreadsheet>");
var sheet = ss.getSheetByName("Sheet1");

var num = 2;

range = ss.getRange("A"+num+":C"+num+"");

for(var i in data) {
  var values = [[data[i][0], data[i][1], data[i][2]]];

  range.setValues(values);
  num += 1;
  range = sheet.getRange("A"+num+":C"+num+"");
  } 


}

base.getData()读取为:

base.getData() reads it as:

     {
           "-M4PTaIESKhQZhreHSE6":
               {"Department":"HR",
                        "ID":"1009",
                   "Surname":"Blanc"},

           "-M4PTgaNIE8BDqAcMr5y":
               {"Department":"Accounting",
                        "ID":"1002",
                   "Surname":"Sandler"},

           "-M4PTmOxxNge0Xfe_ez0":
               {"Department":"Creative",
                        "ID":"1009",
                   "Surname":"Tolkien"}
 }

I 

日志: [20-04-09 00:11:31:653 HKT] {"-M4PTaIESKhQZhreHSE6":{部门":"HR"，"ID":"1009"，姓":空白"}，-M4PTgaNIE8BDqAcMr5y :{"部门:"会计，" ID:" 1002，"姓氏:"桑德勒}，"-M4PTmOxxNge0Xfe_ez0:{"部门:"广告素材，" ID:" 1010， "Surname":"Tolkien"}}

Logs: [20-04-09 00:11:31:653 HKT] {"-M4PTaIESKhQZhreHSE6":{"Department":"HR","ID":"1009","Surname":"Blanc"},"-M4PTgaNIE8BDqAcMr5y":{"Department":"Accounting","ID":"1002","Surname":"Sandler"},"-M4PTmOxxNge0Xfe_ez0":{"Department":"Creative","ID":"1010","Surname":"Tolkien"}}

推荐答案

使用 Object.values 和 Array.map :

const data = {
       "-M4PTaIESKhQZhreHSE6":
           {"Department":"HR",
                    "ID":"1009",
               "Surname":"Blanc"},

       "-M4PTgaNIE8BDqAcMr5y":
           {"Department":"Accounting",
                    "ID":"1002",
               "Surname":"Sandler"},

       "-M4PTmOxxNge0Xfe_ez0":
           {"Department":"Creative",
                    "ID":"1009",
               "Surname":"Tolkien"}
 };
const out = Object.values(data).map(Object.values);
//or to change order: Object.values(data).map(({Department:dp,ID,Surname:sn})=>[ID,dp,sn]);
console.info(out);

这篇关于需要一种将特定数据从Firebase RealtimeDB提取到Google表格的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！