MySQL How to fill a Google Drive Spread Sheet - Como preencher o Planilhas do Google Drive
// This app is made in JDBC
// Replace the variables in this block with real values.
var address = 'localhost:3306';
var user = 'USER';
var userPwd = 'PWD';
var db = 'DATABASE';
var dbUrl = 'jdbc:mysql://' + address + '/' + db;
//Gets and Sets the Current Spreadsheet as Active
var doc = SpreadsheetApp.getActiveSpreadsheet();
doc.setActiveSheet(doc.getSheetByName('Sheet1')); // nome da sheet
//No meu caso tenho a nome da coluna na linha 1: [E-mail] na coluna A1 e [Contato] na coluna B1
var lastRow = doc.getLastRow();
var lastRow_plus = lastRow + 1; // pula uma linha
var cell = doc.getRange('A'+lastRow_plus); // acha a ultima linha preenchida da coluna A
// Read up to 1000 rows of data from the table and log them.
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 DISTINCT email, contato FROM `SUA-TABELA` WHERE status_envio=0 order by email');
var numCols = results.getMetaData().getColumnCount();
var row = 0;
while (results.next()) {
var rowString = '';
for (var col = 0; col < numCols; col++) {
cell.offset(row, col).setValue(results.getString(col+1));
}
row++;
Logger.log(rowString);
}
var stmt1 = conn.prepareStatement('update SUA-TABELA set status_envio=1');
stmt1.addBatch();
var batch = stmt1.executeBatch();
results.close();
stmt.close();
var end = new Date();
Logger.log('Time elapsed: %sms', end - start);
}
Comentários
Postar um comentário