0

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();
}
Robert
  • 1
  • 2

1 Answers1

0
  • 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;
}
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • @Robert see https://developers.google.com/apps-script/guides/triggers/installable use a installable edit trigger https://stackoverflow.com/a/62791834 https://stackoverflow.com/a/68384162/ – TheMaster Mar 28 '22 at 20:13
  • @Master I will look at doing an installable trigger and see how it works. Thanks for the guidance. – Robert Mar 29 '22 at 14:01
  • Question...When other people use the original file during testing everything works as it should after allowing permissions. But when I make a copy of the file the DB Dip is throwing an "Exception: Failed to establish a database connection. Check connection string, username and password." I have confirmed all is the same between the two Projects. Did notice the installable trigger wasn't copied and thought that might be the issue. So I recreated it on the copy but to no avail. Any suggestions on where to start? – Robert Mar 30 '22 at 12:53
  • @Robert Ask a new question. But it's highly unlikely that everything is the same between both projects. Did the same person create the installable trigger in the copy? – TheMaster Mar 30 '22 at 16:24