title: How to use SUBSTRING in Google App Script?
body: <p>I have a script that uses the JDBC service to query a MySQL database. The script will run the query and then print the results in a google sheet. </p>
  
  <p>The SQL table has 23 column and the data contained in column 4 contains multiple string values separated by a comma. For example, "Solar,diesel". </p>
  
  <p>At the moment, the query will pull everything out of the column but I only need the part of the string before the comma, so only "Solar".</p>
  
  <p>I understand that 'SUBSTRING' can do the job but I can't seem to find how to use it in GAS. This is the GAS script below:</p>
  
  <pre><code>function ProjectSelect(){
  
  //defining variables
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetTab = sheet.getSheetByName('Project Selector')
  
  //defining inputs to SQL statement
  
  //conditionals
  var location = sheetTab.getRange(3,2).getValue();
  var country = sheetTab.getRange(5,2).getValue();
  var opStatus = sheetTab.getRange(7,2).getValue();
  var gridStatus = sheetTab.getRange(9,2).getValue();
  var fuel = sheetTab.getRange(11,2).getValue();
  var technology = sheetTab.getRange(13,2).getValue();
  var ownership = sheetTab.getRange(15,2).getValue();
  
  
  //column headers
  var data = sheetTab.getRange(2,4,1,23).getValues();
  for (i in data) {  
  var row = data[i];
  var column1 = row[0];
  var column2 = row[1];
  var column3 = row[2];
  var column4 = row[3];
  var column5 = row[4];
  var column6 = row[5];
  var column7 = row[6];
  var column8 = row[7];
  var column9 = row[8];
  var column10 = row[9];
  var column11 = row[10];
  var column12 = row[11];
  var column13 = row[12];
  var column14 = row[13];
  var column15 = row[14];
  var column16 = row[15];
  var column17 = row[16];
  var column18 = row[17];
  var column19 = row[18];
  var column20 = row[19];
  var column21 = row[20];
  var column22 = row[21];
  var column23 = row[22];
  
  //SQL statement build &amp; write
  var test = conn.createStatement();
  
  if(column2 == '') {var col2 = ''} else {var col2 =',' + column2}
  if(column3 == '') {var col3 = ''} else {var col3 =',' + column3}
  if(column4 == '') {var col4 = ''} else {var col4 =',' + column4}
  if(column5 == '') {var col5 = ''} else {var col5 =',' + column5}
  if(column6 == '') {var col6 = ''} else {var col6 =',' + column6}
  if(column7 == '') {var col7 = ''} else {var col7 =',' + column7}
  if(column8 == '') {var col8 = ''} else {var col8 =',' + column8}
  if(column9 == '') {var col9 = ''} else {var col9 =',' + column9}
  if(column10 == '') {var col10 = ''} else {var col10 =',' + column10}
  if(column11 == '') {var col11 = ''} else {var col11 =',' + column11}
  if(column12 == '') {var col12 = ''} else {var col12 =',' + column12}
  if(column13 == '') {var col13 = ''} else {var col13 =',' + column13}
  if(column14 == '') {var col14 = ''} else {var col14 =',' + column14}
  if(column15 == '') {var col15 = ''} else {var col15 =',' + column15}
  if(column16 == '') {var col16 = ''} else {var col16 =',' + column16}
  if(column17 == '') {var col17 = ''} else {var col17 =',' + column17}
  if(column18 == '') {var col18 = ''} else {var col18 =',' + column18}
  if(column19 == '') {var col19 = ''} else {var col19 =',' + column19}
  if(column20 == '') {var col20 = ''} else {var col20 =',' + column20}
  if(column21 == '') {var col21 = ''} else {var col21 =',' + column21}
  if(column22 == '') {var col22 = ''} else {var col22 =',' + column22}
  if(column23 == '') {var col23 = ''} else {var col23 =',' + column23} 
  
  //Define Conditional 
  var conditional = 
  sheet.getSheetByName('Config').getRange(2,22).getValue();
  Logger.log(conditional);
  
  
  //SQL statement build &amp; write
  var query = 'SELECT '+ column1 + col2 + col3 + col4 + col5 + col6 + col7 + col8 + col9 + col10 + col11 + col12 + col13 + col14 + col15 + col16 + col17 + col18 + col19 + col20 + col21 + col22 + ' FROM unit '+conditional    
  var sql = conn.createStatement();
  var results = sql.executeQuery(query);
  var numCols = results.getMetaData().getColumnCount();
  
  //starting cell
  var cell = sheetTab.getRange(2,4);  
  
  var numCols = results.getMetaData().getColumnCount();
  var numRows = sheetTab.getLastRow();
  var headers;
  var row = 0;
  
  //clear current dataset
  sheetTab.getRange(3,4,numRows, numCols).clearContent();
  
  
  //write sql statement data into sheet
  for(var i = 1; i &lt;= numCols; i++){
    headers = results.getMetaData().getColumnName(i);
    cell.offset(row, i-1).setValue(headers);
  }
  
  while (results.next()) {
    var rowString = '';
    for (var col = 0; col &lt; numCols; col++) {
      rowString += results.getString(col + 1) + '\t';
      cell.offset(row +1, col).setValue(results.getString(col +1 ));
    }
    row++
    Logger.log(rowString)
  }
  }
  results.close();
  test.close();
  
  }    
  </code></pre>
  
  <p>The 'col2', 'col3' etc are defined above this and are columns headers defined in the sheet already.</p>
  
  <p>I read that you can't run more than one SQL statement on one line in GAS and that the query will need to be broken up and then joined back together again. </p>