Is there a way to convert cell values to integer using apps script and display its difference in specific column? I have this sheet database that contains fields in the picture, 1 and using apps script, I wanted to compute columns M and N automatically using apps script. Column M (Remaining Qty) should be the difference between balance_qty column and issue_qty column if balance_qty is greater than restock_point otherwise, column M (Remaining Qty) would be the difference between balance_qty and restock_point. For the column N (Replenish Qty) it should be the difference between issue_qty and balance_qty if balance_qty is less than the issue_qty. I tried to make a script below:

function calc() {

    var ss = SpreadsheetApp.getActive();
    var range = ss.getSheetByName("Database").getRange("datas").getValues();

    for(var x = 0; x < range.length ; x   ){

    var balance = range[x][7];
    var issue = range[x][4];
    var receipt = range[x][12];
    var pr = range[x][13]
    var rPoint = range[x][8];

    if(balance < 0){
 
      pr.setValues(balance.toString());
      receipt.setValues(0)

    }else if(balance < issue){

      var diff = issue - balance;
      var rec = balance - rPoint

      pr.setValues(diff.toString());
      receipt.setValues(rec.toString());

    }else{

      var rem = balance - issue;
      receipt.setValues(rem.toString());

    }
  }
}

but it still missing something.

Note: That the range "data" is cell from A4:N19

CodePudding user response：

If the range has no formulas this code should work:

function calc() {

  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('Database');
  var range = sh.getRange('A4:N19');
  var data = range.getValues();

  data.forEach((row,i) => {

    var issue   = row[4];
    var balance = row[7];
    var rPoint  = row[8];

    if (balance < 0) { 
      data[i][12] = 0; // receipt
    }
    else if (balance < issue) {
      data[i][12] = balance - rPoint; // receipt
      data[i][13] = issue - balance;  // pr
    }
    else {
      data[i][12] = balance - issue; // receipt
    }

  })

  range.setValues(data);
}

It grabs all the cells from the range as a 2d array, modifies it and put back on the sheet. But it can break formulas. If you have formulas in the range the code will need modification.