Data Validation with MySQL data in Google Spreadsheets App Script

Question

In Google Spreadsheets, I have an app script that grabs data from a MysQL database (results) and inserts it into the lastRow()+1 on a specific sheet. I'd like to run data validation to ensure the first item in the results does not equal the contents of the last cell in column A.

Everything works in the script except for the data validation (it grabs the data and puts it where I want it, it just doesn't do the data check). Any help / guidance would be incredibly appreciated - thank you!

Likely relevant: I expect results[0] to return a date in the format 'YYYY-MM-DD' to match against what's in the lastRow() cell value.

Here's my code:

function cleverlynamedfunction() {

// make the connection
var connection = Jdbc.getConnection("jdbc:mysql://[host]/[database]", "[user]", "[password]");

// perform the query
var SQLstatement = connection.createStatement();
var result = SQLstatement.executeQuery("[some query]");

// choose a range in sheet
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data_dash");
var cell = ss.getRange("A".concat(ss.getLastRow()+1));

// validate the data doesn't already exist in the spreadsheet DOES NOT WORK YET
var test = ss.getRange("A".concat(ss.getLastRow()));
if (test.getValue() == result[0]) {
return; 
}


// loop through result object, setting cell values to database data
var row = 0;
while(result.next()) {
for(var i=0; i<43; i++) {     // 43 fields per record
   cell.offset(row, i).setValue(result.getString(i+1));
}
row++;
 }

// tidy up
result.close();
SQLstatement.close();
connection.close();

}

Serge insas Serge insas · Accepted Answer · 2014-11-11T22:46:44

The value you get from the cell is (I guess) a date while the value you are comparing it to is seemingly a string, the comparison will never be true.

There are many ways to solve this ; either you convert both values to dates or you can convert both to strings.

The second solution is probably easier and less prone to difficulties.

So you should use something like this in your code :

  ...
  var testAsString = Utilities.formatDate(new Date(test.getValue()), Session.getScriptTimeZone(),'yyy-MM-dd');
  if (testAsString == result[0]) {
  return;
}

Data Validation with MySQL data in Google Spreadsheets App Script

1 Answers