Cacher is the code snippet organizer for pro developers

var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var lastColumn = sheet.getLastColumn();
var productName = sheet.getRange(lastRow + 1, 2);
var skuName = sheet.getRange(lastRow + 1, 3);
var currency = sheet.getRange(lastRow + 1, 4);
var price = sheet.getRange(lastRow + 1, 5);
var summary = sheet.getRange(lastRow + 1, 6);
var description = sheet.getRange(lastRow + 1, 7);
var status = sheet.getRange(lastRow + 1, 8);
var productType = sheet.getRange(lastRow + 1, 9);
var taxable = sheet.getRange(lastRow + 1, 10);
var link = sheet.getRange(lastRow + 1, 11);

function onOpen(e) {
  SpreadsheetApp.getUi()
  .createMenu('Характеристики')
  .addItem('Выбрать характеристики', 'showDialog')
  .addToUi();
}

function showDialog() {  
  var html = HtmlService.createTemplateFromFile('Page').evaluate();
  SpreadsheetApp.getUi()
  .showSidebar(html);
}

var valid = function(){
  try{
    //return SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues()[0].getValues();
    return(SpreadsheetApp.getActiveRange().getDataValidation().getCriteriaValues());
  }catch(e){
    return e;
  }
}
function fillCell(e){
  var s = [];
  for(var i in e){
    if(i.substr(0, 2) == 'ch') s.push(e[i]);
  }
  if(s.length) SpreadsheetApp.getActiveRange().setValue('{' + s.join(', ') + '}');
}

function newProduct() {
  currency.setValue('RUB');
  price.setValue(0);
  status.setValue(1);
  taxable.setValue('НДС');
  productType.setValue(sheet.getRange(1, 1).getValue());
  for(var i = 12; i < sheet.getLastColumn() + 1; i++) {
    addProductFeature(lastRow + 1, i, sheet.getRange(1, i).getValue());
  }  
}

function newVariation() {
   var ui = SpreadsheetApp.getUi();
   var response = ui.prompt('Сколько вариаций добавить?');

 // Process the user's response.
   if (response.getSelectedButton() == ui.Button.OK) {
       for(var i = 0; i < response.getResponseText(); i++) {
         var sourceRange = sheet.getRange(lastRow, 1, 1, lastColumn);
         var targetRange = sheet.getRange(lastRow + 1, 1, 1, lastColumn);
         //price.setValue(i);
         sourceRange.copyTo(targetRange);
         lastColumn++;
         lastRow++;
         addProductType();
       }  
   } else {
     Logger.log('Возникла ошибка, обратитесь к Вадиму IT.');
   }
  /*
  var sourceRange = sheet.getRange(lastRow, 1, 1, lastColumn);
  var targetRange = sheet.getRange(lastRow + 1, 1, 1, lastColumn);
  sourceRange.copyTo(targetRange);
  addProductType();*/
}


function onEdit(e) 
{
  var range = e.range;
  var thisRow = range.getRow();
  var thisCol = range.getColumn();
  //var featureName = sheet.getRange(1, thisCol).getValue();
  
  var editRange = { // H2:H500
    top : 1,
    bottom : 1,
    left : 1,
    right : 1
  };
  
  if (thisCol == 11) {
   sheet.getRange(lastRow, 11).setValue(translit(range.getValue()));
  }
  
  // Exit if out of range
  
  if (thisRow < editRange.top || thisRow > editRange.bottom) return;
  if (thisCol != editRange.left) return;
  
  var productType = range.getValue();
  loadFeatures(productType);
}

function addProductFeature(thisRow, thisCol, featureName) {
  var option = new Array();
  var featureValueRange = sheet.getRange(thisRow, thisCol);
  option = getFeature(featureName); 
 
  var rules = featureValueRange.getDataValidations();
  var dv = sheet.getRange(featureValueRange.getRow(),featureValueRange.getColumn()).getDataValidation();
  var dv = SpreadsheetApp.newDataValidation();
  dv.setAllowInvalid(true);  
  dv.requireValueInList(option, false);
  sheet.getRange(featureValueRange.getRow(),featureValueRange.getColumn()).setDataValidation(dv.build());
   
  
}


function addProductType() {
  var option = new Array();
  var myRange = sheet.getRange(1, 1);
  var featuresSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Тип товара");
  var values = featuresSheet.getDataRange().getValues();
  for (var i = 0; i < values.length; i++) {
    option.push(values[i]);
  }
  var rules = myRange.getDataValidations();
  
  for (var i = 0; i < rules.length; i++) {
    var dv = sheet.getRange(myRange.getRow(),myRange.getColumn() + i).getDataValidation();
    Logger.log(myRange.getColumn() + i);
    var dv = SpreadsheetApp.newDataValidation();
    dv.setAllowInvalid(false);  
    dv.requireValueInList(option, true);
    sheet.getRange(myRange.getRow(),myRange.getColumn()).setDataValidation(dv.build());
  }
}

function loadFeatures(sheetName) {
  var featuresSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var values = featuresSheet.getDataRange().getValues();
  var featureRange = sheet.getRange(1, 12, 40, 40).setValue('');
  var featureValueRange = sheet.getRange(2, 12, 50, 40);
  
  for (var i = 0; i < values.length; i++) {
    sheet.getRange(featureRange.getRow(),featureRange.getColumn() + i).setValue(values[i]);
  }
}


// Read up to 1000 rows of data from the table and log them.
function getFeature(feature) {
  var address = '80.78.246.234';
  var user = 'zvadim';
  var userPwd = 'spectrsync11';
  var db = 'bd_magic';
  var dbUrl = 'jdbc:mysql://' + address + '/' + db + '?characterEncoding=utf8&useUnicode=true';
  var result = new Array();
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);
  
  var stmt = conn.createStatement();
  stmt.setMaxRows(1000);
  var sql = "SELECT value FROM shop_feature_values_varchar WHERE feature_id IN (SELECT id FROM `shop_feature` WHERE `name` = '" + feature +"')";
  
  var results = stmt.executeQuery(sql);
  var numCols = results.getMetaData().getColumnCount();

  
  while (results.next()) {
    for (var col = 0; col < numCols; col++) {
      result.push(results.getString(col + 1));
    }
  }
  return(result);
  results.close();
  stmt.close();
}