Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

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);
      }
    }
  }
}
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading