Google sheet function into Google script function

Question

I'm using Google Sheets function for analyze some data, but even if i have not huge database, the sheet is lagging with my function. The function is:

=ARRAY_CONSTRAIN(ARRAYFORMULA(SUMIF(IF(A2:A10000="Received",ROW(A2:A10000),""), "<="&ROW(A2:A10000), B2:B10000)+G1-SUMIF(IF(A2:A10000="Given",ROW(A2:A10000),""), "<="&ROW(A2:A10000), B2:B10000)),COUNTA(B2:B10000),1)

Is it possible to use this function via Google script so as not to overload the sheet?

Example sheet: https://docs.google.com/spreadsheets/d/1UeIXFVsP5hevC20D04juTstBbfViYhWUIp6VRst_Nu4

Answer 1

Try this script. It worked correctly in my copy. The purpose is to take previous value and add or subtract new value depending on the condition in column A

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var previous = s.getRange('G1').getValue(); //ger vaule from last month
  var valuecount = s.getLastRow()  ; // defines number of rows
  
  for (let i = 1; i < valuecount; i++) { //starts loop

   var direction = s.getRange(i+1 , 1).getValue(); 
   if (direction == 'Received') {
   previous = previous + s.getRange(i+1 , 2).getValue() ;
   }
    else 
    {previous = previous - s.getRange(i+1 , 2).getValue()  }
  s.getRange(2,4,valuecount).getCell(i,1).setValue(previous);
  }
}