Passing a variable in Apps Scripts to Jdbc

Question

Couple of questions here so Ill try to make them detailed but short.

1.) I am working a Google Sheet that the end user will scan a serial number into column A and an onEdit() function will process a couple of things. The one thing that I am currently stuck on is getting it to query a GCP SQL Database to return the model information of that serial number. I have the DB connection working as it should. But I am unsure how to pass the scanned value that is in the activeCell and use it in the SQL Statement. Below is where I am at. In the SQL statement I can remove the ' + c and input a serial number and the query works as it should. So how to I use the variable in the query.

2.) How in the world am I going to return the results back to the onEdit() Function and have it update the correct cells? I haven't even gotten to this part just yet so the attempt at coding isn't below. But I know that is going to be the next hurdle so some points would be much appreciated. I like to find snippets and figure out how to piece things together to accomplish what I need. So a good push in the right direction is kinda what I'm looking for here.

Thanks in advance.

function onEdit() {
  var row = s1.getActiveCell().getRow();
  var cpRange = s1.getRange('B2:J2');
  var psRange = s1.getRange(row, 2 , 1, 14);
  if( s1.getName() == "Sheet1" ) { 
  var c = s1.getActiveCell();
  var timeCell = c.offset(0,12);
  if( c.getColumn() == 1) { 
  timeCell.setValue(new Date());
  }
  }
  cpRange.copyTo(psRange);
  getModelInvType(c);
}

function getModelInvType(c){
  const dbUrl = 'jdbc:google:mysql://ConnectionName/DBName';
  const user = '*******';
  const userPwd = '*******';

  const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
  const stmt = conn.createStatement();
  var results = stmt.executeQuery('SELECT * FROM ProductDetail WHERE Serial =' + c);


  stmt.close();
  results.close();
}

Answer 1

• c is of type object
• Use c.getValue() to pass a string or a number type
• Just adding the strings using + operator makes your function vulnerable to sql injection. So, use parameterized statements.
• Use return to return a value

Snippets:

  cpRange.copyTo(psRange);
  const returnValues/*capture return values*/ = getModelInvType(/*getValue and pass a string*/String(c.getValue()));
  console.info({returnValues})
}

function getModelInvType(c){
  const dbUrl = 'jdbc:google:mysql://ConnectionName/DBName';
  const user = '*******';
  const userPwd = '*******';
  const output = []
  const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
  //@see https://developers.google.com/apps-script/guides/jdbc?hl=en#write_to_the_database
  const stmt = conn./*create*/prepareStatement('SELECT * FROM ProductDetail WHERE Serial = ?');
  stmt.setString(1, c);
  const resultSet = stmt.executeQuery();
  const numCols = resultSet.getMetaData().getColumnCount();
  while(resultSet.next()){
    let tmpCols = numCols;
    const row = [];
    while(tmpCols--) row.push(resultSet.getString(numCols-tmpCols));
    output.push(row)
  }


  stmt.close();
  resultSet.close();
  /**return the output**/ return output;
}