I have a function in a simple App Script project that reads data from a MySQL table. I had been using a Google Sheet as the storage, but am in the process of making some updates. My SQL is new to me. The code here works, as it is based on the code example in Googles documentation.

The problem I have is that it gives me the answer as a comma separated list of individual columns:

number , email , date , number , email , date , ...etc

which if fine, but I would prefer it as an object where the columns from each row are grouped together like this:

name email date , name email date , ...etc

I believe you can output as an object like this, but I am running into new territory with this every time I try something.

[

{
name: name
email: email
date: date
}
]

Here is the function I am using.

function readFromTable() {
  var conn = Jdbc.getConnection(dburl, user, userPwd);

  //var start = new Date();
  var stmt = conn.createStatement();
  stmt.setMaxRows(1000);
  var results = stmt.executeQuery("SELECT proj_num, user, date FROM rams_sign WHERE user = '"  email  "'")
 

  var numCols = results.getMetaData().getColumnCount();
  
  var userRams = '';
  while (results.next()) {
    
    for (var col = 0; col < numCols; col  ) {
      userRams  = results.getString(col   1)   ',';
    }
    Logger.log(userRams);
    
    
  }

  return userRams

};

I have looked through the documentation and I know it can be done, but I am having trouble figuring out how. I really only would like to be pushed in the right direction, Thanks in advance for any advice

CodePudding user response：

Not sure if I understand your input and output. And how your 'number' becomes 'name' etc. How do you know how many rows a column has?

Just a guess. Perhaps you want something like this:

var s = 'number , email , date , number , email , date';
var rows = 3; // just a guess
var arr = s.split(' , ');
var result = [];

for (var a = 0; a < arr.length; a  = rows) {

  var object = {};

  for (var r = 0; r < rows; r  ) {
    object[arr[r a]] = arr[r a];
  }

  result.push(object)
}

console.log(result);

// Output:
// [
//   { number: 'number', email: 'email', date: 'date' },
//   { number: 'number', email: 'email', date: 'date' }
// ]

<iframe name="sif1" sandbox="allow-forms allow-modals allow-scripts" frameborder="0"></iframe>

It splits the input string (do you have a string as input?) and repack it into the array of objects.

Or, probably it should be like this:

var s = 'number1 , email1 , date1 , number2 , email2 , date2';
var rows = 3;
var arr = s.split(' , ');
var result = [];

for (var a = 0; a < arr.length; a  = rows) {

  var object = {};

  object.number = arr[a];
  object.email  = arr[a 1];
  object.date   = arr[a 2];

  result.push(object)
}

console.log(result);

<iframe name="sif2" sandbox="allow-forms allow-modals allow-scripts" frameborder="0"></iframe>

Short variant of the second algorithm with array.shift() method:

var s = 'number1 , email1 , date1 , number2 , email2 , date2';
var arr = s.split(' , ');
var result = [];

while (arr.length)
  result.push({number: arr.shift(), email: arr.shift(), date: arr.shift()})

console.log(result);