CODEWITHSUNDEEP
mysqlgoogle-sheetsgoogle-docs
BaconCatBug
BaconCatBug

Reputation: 195

Importing mySQL query to Google sheet automatically?

Basically I am using the Advanced CFO Solutions MySQL Query to pull a table of data from a MySQL server and it works fine, but I would like to automate it to run once per day. There is an alternative add on that can set up scheduling but it costs $50 a month and what I am doing is not important enough to warrant that cost.

Any and all help is appreciated.

Upvotes: 0

Views: 2082

Answers (1)

Andrew
Andrew

Reputation: 1150

After also using the Advanced CFO Solutions mysql Addon and requiring the query to be automated I found another solution to our problem.

Its in the form of a script that you can then setup using triggers to run at a certain times. So this gives one allot more functionality than that Addon.

Access scripts in Google Sheets: Tools> Script Editor.

Copy this function and replace the default one:

function RunQuery() {
   var conn = Jdbc.getConnection("jdbc:mysql://201.54.190.213:3306/database_table_name", "Username", "Password");

 var SQLstatement = conn.createStatement();
 var result = SQLstatement.executeQuery("SELECT NOW() ");


  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet Name");
  sheet.clear();

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


  Logger.log(cell.getA1Notation());
  var row = 0;
  while (result.next()) {
    for (var col = 0; col < result.getMetaData().getColumnCount(); col++) {
      cell.offset(row, col).setValue(result.getString(col + 1));
    }
    row++;
  }

 result.close();
 SQLstatement.close();
 conn.close();   
};

Triggers: In the script editor go to Edit > All your triggers Go ahead and setup a new trigger to run whenever you like.

Credits for the script: https://github.com/jstakich/Fill_Specific_Sheet_Google_Apps_Script_Mysql/blob/master/Code.gs

Upvotes: 1

Related Questions