javascriptloopsgoogle-sheetsgoogle-apps-script

Loop in range and set value


I'm looking for a script to put an X on the value of the cell was 1.
Loop on W4:W28 in sheet Printing
If it meets 1, put an X on its line in column X

I am try this:

function loop() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Impression');   // name of your sheet

  for (var i = 4; i < 29; i++) {
    
    if (sheet.getRange((i,23)).getValue() == '1') {
     ss.getRange(i,24).setValue("X"); 
    }
  }
}

Solution

  • You want to loop through a range; if the value of a cell in the range equals 1, then You want to update the value in an adjacent cell to "X".

    I have created a script that meets your goals. There are several differences to the script in the question:

    Problems in the OP script

    Aspects of the answer


    function loop() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName('Impression');   // name of your sheet
    
      // create some variables
      var startrow = 4
      var startColumn = 23
      var numOfRows = 21
    
      // get the data
      var range = sheet.getRange(startrow,startColumn,numOfRows,1)
      // Logger.log("DEBUG: range = "+range.getA1Notation())
      var values = range.getValues()
    
      for (var i = 0; i < values.length; i++) {
        var value = values[i][0]
        if(value == 1) {
          // value equals 1, so update adjacent cell to "X"
          Logger.log("DEBUG: i:"+i+", value = 1"+", column X range = "+sheet.getRange(+startrow+i,+startColumn+1).getA1Notation())
          sheet.getRange(+startrow+i,+startColumn+1).setValue("X")
        }
        else{
          // not equal to 1, do nothing
          Logger.log("DEBUG: i:"+i+", value <> 1")
        }
      }
    }