How do I get the results from A1 Notation and use it in a formula?-CodePudding

I need to divide a number by 500 but the cell location always changes so I have a loop set up to find the column based on the product's item number "4482741" Here is my loop and what I have so far.

    function fixCount(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("foo");
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();

  for (var i = 0; i < values.length; i  ) {
    var row = "";
    for (var j = 0; j < values[i].length; j  ) {     
      if (values[i][j] == "4438784") {
        row = values[i][j 1];
        var cellLocation = sheet.getRange(i 1,2,1,1).getA1Notation();
        var cellValue = sheet.getRange(cellLocation).getValue();
        sheet.getRange(cellLocation).setFormula("=(cellLocation)/500");
        Logger.log(cellLocation);
      }
    }    
  }  
}

In this case, the A1 Notation is B5. I basically want to overwrite B5 with value of B5/500.

I know sheet.getRange(cellLocation).setFormula... is wrong.

CodePudding user response：

Try it this way

function fixCount() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("foo");
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();
  for (var i = 0; i < values.length; i  ) {
    var row = "";
    for (var j = 0; j < values[i].length; j  ) {
      if (values[i][j] == "4438784") {
        sheet.getRange(i   1, 2).setValue(values[i][j 1]/500);
      }
    }
  }
}