// MySQL to Google Spreadsheet By Pradeep Bheron
// Support and contact at pradeepbheron.com

function myMySQLFetchData() {

  var conn = Jdbc.getConnection('jdbc:mysql://127.0.0.1:3306/employee_db', 'username', 'pass'); // Change it as per your database credentials

  var stmt = conn.createStatement();
  var start = new Date(); // Get script starting time

  var rs = stmt.executeQuery('SELECT order_id, firstname AS first_name, lastname AS last_name, email, date_added AS order_date, CONCAT("£", FORMAT(total,2)) order_value, payment_address_1 AS billing_address_1, payment_address_2 AS billing_address_2, payment_city AS billing_city, payment_postcode AS billing_postcode, payment_country AS billing_country, items_purchased FROM (SELECT order_id, firstname, lastname, email, date_added, total, payment_address_1, payment_address_2, payment_city, payment_postcode, payment_country FROM ocbw_order GROUP BY order_id) AS A JOIN (SELECT order_id AS product_order_id, GROUP_CONCAT(name SEPARATOR ", ") AS items_purchased FROM ocbw_order_product GROUP BY product_order_id) AS B ON A.order_id=B.product_order_id WHERE date_added >= DATE_SUB(NOW(),INTERVAL 1 YEAR) AND firstname != ""'); // It sets the limit of the maximum nuber of rows in a ResultSet object
  //change table name as per your database structure

  var doc = SpreadsheetApp.getActiveSpreadsheet(); // Returns the currently active spreadsheet
  var cell = doc.getRange('a1');
  var row = 0;
  var getCount = rs.getMetaData().getColumnCount(); // Mysql table column name count.
  var tempArray = []; // Create array to hold mysql data

  // get row and column count for later

  var colCount = getCount;
  rs.last();
  var rowCount = rs.getRow();
  rs.beforeFirst(); // resets rs cursor

  // Build TempArray using MySQL data
  for (var i = 0; i < getCount; i++){
      tempArray[0][i] = rs.getMetaData().getColumnName(i+1);
     //cell.offset(row, i).setValue(rs.getMetaData().getColumnName(i+1)); // Mysql table column name will be fetch and added in spreadsheet.
  }

  var row = 1;
  while (rs.next()) {
    for (var col = 0; col < rs.getMetaData().getColumnCount(); col++) {
      tempArray[row][col] = rs.getString(col + 1);
      //cell.offset(row, col).setValue(rs.getString(col + 1)); // Mysql table column data will be fetch and added in spreadsheet.
    }
    row++;
  }

  // Fill Spreadsheet from tempArray
  for (var row = 0; row < rowCount; row++) {
    for (var col = 0; col < colCount; col++) {
      cell.offset(row, col).setValue(tempArray[row][col + 1]);
    }
  }

  rs.close();
  stmt.close();
  conn.close();
  var end = new Date(); // Get script ending time
  Logger.log('Time elapsed: ' + (end.getTime() - start.getTime())); // To generate script log. To view log click on View -> Logs.
}