Google script loop issue

Question

-can anyone help me check on this code because even the condition was met it still setting the value of all the data. The data should only be updated when the condition is met.

var ss = SpreadsheetApp.openById('id');

var sheet = ss.getSheetByName("VL Request");

var range = sheet.getDataRange();

var values = range.getValues();

var rid = [1];

// writes the form data to the spreadsheet

for (var i = 1; i <= values.length; i++) {

    var sel = sheet.getRange(i, 2).getValue();

    if (sel = ReqNum) {

        sheet.getRange(i, 14).setValue(pri);

        sheet.getRange(i, 16).setValue(stat);

        sheet.getRange(i, 18).setValue(rem);
    }
}

This is an assignment sel = ReqNum this would be a comparison sel == ReqNum — Cooper
– Cooper, Commented Sep 20, 2019 at 17:33

Cooper · Accepted Answer · 2019-09-20 20:09:37Z

1

Try this:

function unknown() {
  var ss=SpreadsheetApp.openById('id');
  var sheet=ss.getSheetByName("VL Request");
  var range=sheet.getRange(2,1,sheet.getLastRow()-1,18);
  var values=range.getValues();
  for(var i=0;i<values.length;i++) {
    if(values[i][1]==ReqNum) {//First time through is row 2 column 2
      sheet.getRange(i+2, 14).setValue(pri);//i+2 is the row, 14 is the column
      sheet.getRange(i+2, 16).setValue(stat);//the first time through is row 2, column16
      sheet.getRange(i+2, 18).setValue(rem);
    }
  }
}

edited Sep 20, 2019 at 20:09

answered Sep 20, 2019 at 17:35

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Julius Ticong Over a year ago

Hi Cooper, I've already tried but still not work. However, i am trying to understand this line: "var range=sheet.getRange(2,1,sheet.getLastRow()-1,18);" can you please explain how it works?

Julius Ticong Over a year ago

And just to clarify, values[i][1] is values[rows][column] right?

Cooper Over a year ago

No. values is a two dimensional array whose index starts at zero. Columns and Rows start at one. I started my range at row 2 so I just had to correct the getRange rows to i+2

Julius Ticong Over a year ago

I think I already know what's the issue. Maybe the value of ReqNum has spaces or something that would not be equal on the data. Is there a way to fix that? Because the value of var ReqNum is coming from a form

Cooper Over a year ago

You can use trim() or parseInt()

|

Collectives™ on Stack Overflow

Google script loop issue

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related