Good day everybody, i've encountered a wierd issue with Google Sheets script, where the display and filtering of a MySQL query is not working due to its contents being in Russian language
I will put the whole script code below, but the main part that doesen't work is:
from samplename_order WHERE status = "Оплачен")');
as well as
select (select data_create) as "Дата"
In the second example in the column header renders to "????"
In the first example I get nothing at all except column header name.
The whole code:
function onOpen() {
var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [
{name: 'Sync', functionName: 'readData'}
];
spreadsheet.addMenu('Sync', menuItems);
}
var address = 'xxx:3306'; //ex. '10.1.1.1:1433'
var user = 'xxx';
var userPwd = 'xxxx';
var db = 'sxxxx';
var dbUrl = 'jdbc:mysql://' + address + '/' + db;
function readData() {
var conn = Jdbc.getConnection(dbUrl, user, userPwd);
var stmt = conn.createStatement();
stmt.setMaxRows(1000);
var results = stmt.executeQuery('select (select data_create) as "Дата",(select concat(name," ",sername)) as name,mail,(select concat(" ",phone)) as phone,comments,`status` from sample_order WHERE status = "Оплачен"');
var metaData=results.getMetaData();
var numCols = results.getMetaData().getColumnCount();
var sheet = SpreadsheetApp.getActiveSheet();
sheet.clearContents();
var arr=[];
for (var col = 0; col < numCols; col++) {
arr.push(metaData.getColumnName(col + 1));
}
sheet.appendRow(arr);
while (results.next()) {
arr=[];
for (var col = 0; col < numCols; col++) {
arr.push(results.getString(col + 1));
}
sheet.appendRow(arr);
}
results.close();
stmt.close();
sheet.autoResizeColumns(1, numCols+1);
}
