I am just a beginner here, so I hope you can be patient with me :)

I manage to sync my Firebase Database to my Google Sheet and display the data in my Sheet but the problem is, instead of row, I want to display the data in column.

Here's the actually outcome:

enter image description here

and this is what I want to achieve:

enter image description here

And here's the code in app script:

function getAllData() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var sheet = ss.getActiveSheet();

  var firebaseUrl = "firebase url";

  var base = FirebaseApp.getDatabaseByUrl(firebaseUrl);

  var dataSet = [base.getData()];

  var rows = [],
      data;

  for (i = 0; i < dataSet.length; i  ) {

    data = dataSet[i];
    var in_time = Utilities.formatDate(new Date(), "GMT 8", "HH:mm:ss" );
    var in_date = Utilities.formatDate(new Date(), "GMT 8", "yyyy-MM-dd" );
    rows.push([data.PUMP, data.Tank1, data.Tank2, data.Tank3, data.Valve1, data.Valve2, data.Valve3, data.RandomMain, data.RandomNumber1, data.RandomNumber2, data.RandomNumber3, data.RandomNumber4 , in_date, in_time]);

  }

  dataRange = sheet.getRange(2, 1, rows.length, 14).setValues(rows);
  
}

Hope you help me with this and thank you for your future response!!

CodePudding user response：

Your command

sheet.getRange(2, 1, rows.length, 14).setValues(rows);

is set to fill rows, ie start from the cell (2,1), fill number of rows that is equal to length of the array and 14 columns in the same time. You will have to set it to:

sheet.getRange(1, 16, rows.length, 1).setValues(rows);

so it will start from the cell (1,16) and fill only one column with number of rows that is the same as the length of the array.

Is there some reason to assign it to some dataRange variable, because you can setValues directly?

Documentation.

CodePudding user response：

When your script is modified, how about the following modification?

Modified script:

function getAllData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // var sheets = ss.getSheets(); // This is not used.
  var sheet = ss.getActiveSheet();
  var firebaseUrl = "firebase url";
  var base = FirebaseApp.getDatabaseByUrl(firebaseUrl);
  var dataSet = [base.getData()];

  // I modified the below script.
  var header = ["PUMP", "Tank 1", "Tank 2", "Tank 3", "Valve 1", "Valve 2", "Valve 3", "Random from Main", "Random from Tank 1", "Random from Tank 2", "Random from Tank 3", "Random from Tank 4", "Date", "Time"];
  var rows = [header], data;
  var in_time = Utilities.formatDate(new Date(), "GMT 8", "HH:mm:ss");
  var in_date = Utilities.formatDate(new Date(), "GMT 8", "yyyy-MM-dd");
  for (i = 0; i < dataSet.length; i  ) {
    data = dataSet[i];
    rows.push([data.PUMP, data.Tank1, data.Tank2, data.Tank3, data.Valve1, data.Valve2, data.Valve3, data.RandomMain, data.RandomNumber1, data.RandomNumber2, data.RandomNumber3, data.RandomNumber4, in_date, in_time]);
  }
  rows = rows[0].map((_, c) => rows.map(r => r[c]));
  dataRange = sheet.getRange(2, 16, rows.length, rows[0].length).setValues(rows);
}

• When this script is run, the values are put from the column "P" including the header column.

• If you don't want to include the header column, please test the following script. In this case, it supposes that the header column has already been set to the column "P". Please be careful about this.

  function getAllData() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    // var sheets = ss.getSheets(); // This is not used.
    var sheet = ss.getActiveSheet();
    var firebaseUrl = "firebase url";
    var base = FirebaseApp.getDatabaseByUrl(firebaseUrl);
    var dataSet = [base.getData()];
  
    // I modified the below script.
    var rows = [], data;
    var in_time = Utilities.formatDate(new Date(), "GMT 8", "HH:mm:ss");
    var in_date = Utilities.formatDate(new Date(), "GMT 8", "yyyy-MM-dd");
    for (i = 0; i < dataSet.length; i  ) {
      data = dataSet[i];
      rows.push([data.PUMP, data.Tank1, data.Tank2, data.Tank3, data.Valve1, data.Valve2, data.Valve3, data.RandomMain, data.RandomNumber1, data.RandomNumber2, data.RandomNumber3, data.RandomNumber4, in_date, in_time]);
    }
    rows = rows[0].map((_, c) => rows.map(r => r[c]));
    dataRange = sheet.getRange(2, 17, rows.length, rows[0].length).setValues(rows);
  }
  

Reference:

• map()