Inserting Null values to Mysql database from Sheets using Google Apps Script

Question

I'm currently working on a data ingestion add-on using Google apps script. The main idea is that the users of an application can insert data from sheets to a database. To do so, i'm using the JDBC api that apps script provides

The problem i'm currently having is that when I read a cell from the sheet that is empty apps script uses the type undefined, therefore producing an error a the moment of insertion. How could I do such thing?

My current insert function:

function putData(row, tableName) {
  var connectionName = '****';
  var user = '****';
  var userPwd = '*****';
  var db = '******';

  var dbUrl = 'jdbc:google:mysql://' + connectionName + '/' + db;
  var conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);

  var stmt = conn.createStatement();
  var data = row
  var query = "INSERT INTO "+ db + '.' + tableName +" VALUES (" ;
  var i = 0

  //The following loop is just to build the query from the rows taken from the sheet
  // if the value is a String I add quotation marks
  for each (item in row){
    if ((typeof item) == 'string'){
      if (i == row.length-1){
        query += "'" + item + "'";
      } else {
        query += "'" + item + "',";
      } 
    }else {
      if (i == row.length-1){
        query += item;
      } else {
        query += item + ",";
      } 
    }
  i++
  }
  query += ")"
  results = stmt.executeUpdate(query)
  stmt.close();
  conn.close();
}

When I try to insert the word "NULL" in some cases in thinks it is a string and brings out an error on other fields.

I think you can test for it’s truthiness and it’s type. If truthiness is false then insert null. — Cooper

ale13 ale13 · Accepted Answer · 2019-12-30T11:54:13

When trying to get the data from the Spreadsheet, more precisely from a cell, the value will be automatically parsed to one of these types: Number, Boolean, Date or String.

According to the Google getValues() documentation:

The values may be of type Number, Boolean, Date, or String, depending on the value of the cell. Empty cells are represented by an empty string in the array.

So essentially, the undefined type may be an issue present in the way you pass the row parameter (for example, trying to access cells which are out of bounds).

If you want to solve your issue, you should add an if statement right after the for each (item in row) { line:

if (typeof item == 'undefined')
  item = null;

The if statement checks if the row content is of type undefined and if so, it automatically parses it to null. In this way, the content will be of type null and you should be able to insert it into the database.

The recommended way to do what you are doing actually is by using the JDBC Prepared Statements, which are basically precompiled SQL statements, making it easier for you to insert the necessary data. More exactly, you wouldn't have to manually prepare data for the insertion, like you did in the code you provided above. They are also the safer way, making your data less prone to various attacks.

Also, the for each...in statement is a deprecated one and you should consider using something else instead such as the for loop or the while loop.

Furthermore, I suggest you take a look at these links, since they might be of help:

Inserting Null values to Mysql database from Sheets using Google Apps Script

1 Answers