Stock market on google sheets

Question

I am trying to make a model stock market on google sheets. Link : https://docs.google.com/spreadsheets/d/1OmOWKfxYAdNRPcF4yfX4jJ0DX3UvUutaHJINHRU8vqo/edit?usp=sharing Columns A-D are random numbers that I've entered for Buy Price, Sell price, buy quantity, Sell quantity. Columns F-I are these variables in sorted order. Cell E13 is the updated price. I want cell E14 to be the updated Sell quantity. Please help!!

Following is the code that I've used:

function myFunction() {
  
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
  
  var Buyprice=sheet.getRange("F2").getValues(); 
  var Sellprice=sheet.getRange("H2").getValues();
  var Buyquantity=sheet.getRange("G2").getValues();
  var Sellquantity=sheet.getRange("I2").getValues();
  if(Buyprice>Sellprice){
    sheet.getRange("E13").setValues(Sellprice);
    var Buyquantity=Buyquantity- Math.min(Buyquantity,Sellquantity);
    var Sellquantity=Sellquantity- Math.min(Buyquantity,Sellquantity);
    sheet.getRange("E14").setValues(Sellquantity);}
  else{ }
}

Upon running this script, I get : The parameters (number) don't match the method signature for SpreadsheetApp.Range.setValues.

Answer 1

You need to change setValues() to setValue()

As you can read in the documentation:

getValues()

Returns the rectangular grid of values for this range

and

setValues(values)

Sets a rectangular grid of values

Now, if you implement logs into your code, e.g.

...
 Logger.log(Sellquantity);
 var Sellquantity=Sellquantity- Math.min(Buyquantity,Sellquantity);
 Logger.log(Sellquantity);
...

the output will be:

[[6.0]]
6.0

In other words, the rectangular grid will be transformed by the substarction of another value to an integer value.

Now, sheet.getRange("E14").setValues(Sellquantity); expects a 2D array instead of a number which will result in an error.

So, use setValue() instead:

setValue()

Sets the value of the range. The value can be numeric, string, boolean or date.

Indeed, the comparison if(Buyprice>Sellprice) might also not work as intended if you compare array elements of the type [[]], so it is better if you also use getValue() instead of getValues().

Sample:

function myFunction() {
  
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  var Buyprice=sheet.getRange("F2").getValue(); 
  var Sellprice=sheet.getRange("H2").getValue();
  var Buyquantity=sheet.getRange("G2").getValue();
  var Sellquantity=sheet.getRange("I2").getValue();
  if(Buyprice>Sellprice){
    sheet.getRange("E13").setValue(Sellprice);
    var newBuyquantity=Buyquantity- Math.min(Buyquantity,Sellquantity);
    var newSellquantity=Sellquantity- Math.min(Buyquantity,Sellquantity);
    sheet.getRange("E14").setValue(newSellquantity);
  }
  else{ } 
}