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();
}

CodePudding user response：

• 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;
}

CodePudding user response：

So when I got to work this morning I had the team try and use the sheet and found out the THEMASTERs solution works when run manually from Apps Scripts but when my team is scanning in serial numbers in the SHEET the script doesn't pull over the Model information. I've been trying to figure this one out most of the afternoon and I am at loss. During the onEdit()I have it updating a cell with the date/time the scan was made and this works as it should. So why isn't the "DB Dip", I'd love to understand. I've tried moving the const returnValues around to the first IF but no luck.

  var row = s1.getActiveCell().getRow();

  if( s1.getName() == "Sheet1" ) { 
    var c = s1.getActiveCell();
    var timeCell = c.offset(0,12);
    if( c.getColumn() == 1) {
      timeCell.setValue(new Date());
    }
  }
 var modelCell = c.offset(0,4)
 const returnValues = getModelInvType(String(c.getValue()));
  console.info({returnValues})
  modelCell.setValue(returnValues)
}

function getModelInvType(c){
  const dbUrl = 'jdbc:google:mysql://ConnectionName/DBName';
  const user = '*******';
  const userPwd = '*********';
  const output = []
  const conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
  const stmt = conn.prepareStatement('SELECT Model FROM ProductDetail WHERE Serial = ?');
  stmt.setString(1, c);
  const resultSet = stmt.executeQuery();
  const numCols = resultSet.getMetaData().getColumnCount();
  while(resultSet.next()){
    tmpCols = numCols;
    const row = [];
    while(tmpCols--) row.push(resultSet.getString(numCols-tmpCols));
    output.push(row)
  }
  stmt.close();
  resultSet.close();
  return output;  //return output
}```