Write from Google Firebase to Google Sheets using

Question

Trying to retrieve form entries which are stored in google firebase under the node called entries and append to a google sheet using the script editor in google sheets.

I have added the FirebaseApp library to google sheet script editor. Then my code looks like this:

function getAllData() {
  var firebaseUrl = "https://myapp.firebaseio.com/";
  var secret = "pCOCwKCC582jpqdZe2EqPqnW3IAd3UyO9oB4uaEL2";
  var base = FirebaseApp.getDatabaseByUrl(firebaseUrl, secret);
  var data = base.getData();
    Logger.log(data);
}

when I run this nothing happens. Any ideas?

Next I need to add the returned data from firebase to the google sheet. I was using this code to do this via the sheets api, however I'm not sure how this works in the google script editor?

  function addEntries() {
    gapi.client.sheets.spreadsheets.values.append({
      spreadsheetId: '10lyQpQtEA7euCfdU2isrqB_bgPuy-eSbW74h7oDP3ko',
      range: "Sheet1!A1:D100",
      majorDimension: "ROWS",
        "values": [
          ["testa", "testb", "testc", "testd"]
        ],
      valueInputOption: 'USER_ENTERED'
    }).then(function(response) {

    }, function(response) {
      appendPre('Error: ' + response.result.error.message);
    });
  }

Answer 1

I hope this helps someone, this worked for me.

function writeSheets() {
  var ss = SpreadsheetApp.openById("10lyQpQtEA7euCfdU2isrqB_bgPuy-eSbW74h7oDP3ko");
  var sheet = ss.getSheets()[0];
  var firebaseUrl = "https://myapp.firebaseio.com/";
  var secret = "pCOCwKCC582jpqdZe2EqPqnW3IAd3UyO9oB4uaEL2";  // get this from firebase project settings
  var base = FirebaseApp.getDatabaseByUrl(firebaseUrl);
  var data = base.getData();
  var keys = Object.keys(data.entries);
  var sheetRow = [];
  var entryKeys;
  for (index in keys) {
    sheetRow = [];
    entryKeys = Object.keys(data.entries[keys[index]])
    for (i in entryKeys) {
      sheetRow.push(data.entries[keys[index]][entryKeys[i]]);
    }
    //Logger.log(sheetRow);
    sheet.appendRow(sheetRow);                            
  }
}

Note: in order for this code to work, you need to install the firebaseapp library in the script editor as per these instructions, https://sites.google.com/site/scriptsexamples/new-connectors-to-google-services/firebase

Answer 2

I'm using the newest Firebase version. This snippet code works for me.

function getFacturasClientesExistentes() {
var firebaseUrl = "https://test.firebaseio.com/FacturasBLP/clienteExistente";
var base = FirebaseApp.getDatabaseByUrl(firebaseUrl);
var data = base.getData();

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Facturas Clientes Existentes");

var num = 2;
range = sheet.getRange("A"+num+":F"+num+"");
for(var i in data) {
  var values = [
  [ data[i].fecha, data[i].sucursal, data[i].cantidad, data[i].cliente, data[i].correo, data[i].estatus ]
  ];
  range.setValues(values);
  num += 1;
  range = sheet.getRange("A"+num+":F"+num+"");
  } 
}

Some notes:

1. I have previously write the headers for my data in the spreadsheet
2. In the line range = sheet.getRange("A"+num+":F"+num+""); from A to F I have my headers