I have a product sheet with product code in column A, price in sterling in column C and price in euro in column D. On the quotation sheet when I enter the product code in column A and enter currency in column c, I require the code to get the price value from the appropriate currency column in the products sheet. The code I have wrote is getting the price value from the products sheet okay but I am unable to get the code to select the correct price value based on currency. Can anyone please assist? Thanks

function updateData(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var quotationsSheet = ss.getSheetByName('QUOTATION');
  var productSheet = ss.getSheetByName("PRODUCTS");
  var lastProd = productSheet.getLastRow();
  var priceList = productSheet.getRange(2,1,lastProd,4).getValues();
  var drg = quotationsSheet.getRange(2, 1, quotationsSheet.getLastRow() - 1, 4);

  var newPrice = quotationsSheet.getLastRow();
  quotationsSheet.getRange(newPrice,2).setValue("Not found");
  var code = quotationsSheet.getRange(newPrice,1).getValue();
  var vs = drg.getValues();
  vs.forEach((r,j) => {
  let currency = r[2];
  for (j = 0;j <priceList.length;j  ){
      
      if (priceList[j][0] == code && currency !='STERLING' ){
        var description = priceList[j][1];
        var price = priceList[j][2];
      if (priceList[j][0] == code && currency !='EURO' )
        var description = priceList[j][1];
        var price = priceList[j][3];  
        quotationsSheet.getRange(newPrice,12).setValue(description)
        quotationsSheet.getRange(newPrice,13).setValue(price)
      } 
    }
    });}

CodePudding user response：

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var quotationsSheet = ss.getSheetByName('QUOTATION');
  var productsSheet = ss.getSheetByName("PRODUCTS");
  var lastprod = productsSheet.getLastRow();
  var productList = productsSheet.getRange(2,1,lastprod,4).getValues();
  var currencyType = quotationsSheet.getLastRow();
  var ct = quotationsSheet.getRange(currencyType,3,1,4);
  var gs = ct.getValue();
  var ge = ct.getValue();

  var newProd = quotationsSheet.getLastRow();
  quotationsSheet.getRange(newProd,2).setValue("Not found");
  var code = quotationsSheet.getRange(newProd,1).getValue();

  for (j = 0;j <productList.length;j  ){
  //for (i = 0;i <gs.length;i  )
      if (productList[j][0] == code && gs == 'STERLING'){
        var description = productList[j][1];
        var price = productList[j][2];  
        quotationsSheet.getRange(newProd,6).setValue(description)
        quotationsSheet.getRange(newProd,7).setValue(price)
      } } 
   for (j = 0;j <productList.length;j  )   
     if (productList[j][0] == code && ge == 'EURO'){
        var description = productList[j][1];
        var price = productList[j][3];  
        quotationsSheet.getRange(newProd,6).setValue(description)
        quotationsSheet.getRange(newProd,7).setValue(price) 
     }
   }```