Wednesday, October 21, 2020

Mysql data get and show in google sheet




Step 1: Create a spreadsheet within your Google Drive.

Step 2: Insert script in newly created spreadsheet.


Step 3.1: To insert the script in Google spreadsheet go to Tools > Script Editor.

Step 4.2: On opening script editor you will find a Google Apps Script pop-up Close it.


Step 5: save file and run the script


 // Replace the variables in this block with real values.

    var address = '192.xxxxx';

    var user = 'xxxxx';

    var userPwd = 'xxxxx@@121';

    var db = 'xxxxx';

    var dbUrl = 'jdbc:mysql://' + address + '/' + db;


    // 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();

      // Read up to 1000 rows of data from the table and log them.

      // stmt.setMaxRows(1000);

      var results = stmt.executeQuery('SELECT * FROM hub_consulation_form');


      var sheet = SpreadsheetApp.getActiveSpreadsheet();

      var cell = sheet.getRange('A1');

      var numCols = results.getMetaData().getColumnCount();

      var row =0;


      while (results.next()) {

        var rowString = '';

        for (var col = 0; col < numCols; col++) {

          rowString += results.getString(col + 1) + '\t';

          cell.offset(row, col).setValue(results.getString(col +1 ));

        }

        row++

       Logger.log(rowString)

      }


      results.close();

      stmt.close();

      conn.close();


      var end = new Date();

      Logger.log('Time elapsed: %sms', end - start);

    }

    

No comments:

Post a Comment