Google Appscript: Creating a table from a mysql query and charting it using appscript-CodePudding

Really could use some guidance on this one. I am fairly new to the world of Appscript but am pigeonholed into using it due to another google application called Appsheet. Forgive any naivety here as this is a new tool to me.

The ultimate goal I have is to take user inputs in my Appsheeet app, trigger an Appscript function to run a MySQL query using those inputs, chart the results of that query, and upload that chart to my drive wherein my app can grab that drive url and display the image in a dashboard.

I have been working on this in bits and pieces and have most steps done. Yet, I am stuck on the charting the results of a query (hence it is bolded and italicized above). Without making it complicated I have a simple query and want to see how to chart it. Example code below:

function useDB(){
var conn = Jdbc.getCloudSqlConnection("jdbc:google:mysql://my_connection_name","user","password");
 const stmt = conn.createStatement();
 let query = stmt.executeQuery('SELECT * FROM my_table');
 let arr=[];
 while(query.next()){
  arr.push([
    query.getString(1),
    query.getfloat(2)
  ]);
}
Logger.log(arr)
}

Now I thought this would work well as it prints an array of row values example: [[Hello, 3], [World, 7]]

When researching, I saw the Google Charts Tools primarily Charts.newDataTable. However, I noticed that the example given only has the function .addRow() where you proceed to add a singe row at a time manually. Example code I am referring to:

var data = Charts.newDataTable()
    .addColumn(Charts.ColumnType.STRING, "Month")
    .addColumn(Charts.ColumnType.NUMBER, "In Store")
    .addColumn(Charts.ColumnType.NUMBER, "Online")
    .addRow(["January", 10, 1])
    .addRow(["February", 12, 1])
    .addRow(["March", 20, 2])
    .addRow(["April", 25, 3])
    .addRow(["May", 30, 4])
    .build();

I am wondering if I can loop the .addRow over my array to generate the table from my query so I can go ahead and chart it. Any help or thoughts if this is possible is much appreciated. And if this is not possible or there is another way to do this altogether (like using appscript to utilize google visualization), I am wide open to suggestions and happy to explore them on my own.

CodePudding user response：

Was able to get some help and found the answer to be:

var data = Charts.newDataTable()
.addColumn(Charts.ColumnType.STRING, "column_1_name")
.addColumn(Charts.ColumnType.NUMBER, "column_2_name");

arr.forEach(row =>
{data.addRow(row)});
data.build();

running this allowed me to build the table I needed and complete my process