quinta-feira, 2 de junho de 2016

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);
}


Nenhum comentário:

Postar um comentário