CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
JoshM
JoshM

Reputation: 95

For loop incrementing other variable too many times

Context: I'm making a script for Sheets that will colour an internal Sheets calendar to show start and end dates based on filling in a task's details elsewhere in the Sheet.

Bizarre situation: For each increasing column I set the start date from, colCount is incremented an extra time (more than I want), as shown in the pictures attached. The colCount keeps track of how many cells should be painted blue (finishing at the cell before end date)

https://i.sstatic.net/oy5Yq.png - Sheet 1 with task details

https://i.sstatic.net/Y1Qzd.png - Sheet 2 with calendar, showing where the painting occurs

Why is colCount being incremented too many times?

for (var k = 2; k < 100; k++){ //iterate over rows (searching tasks)

      if (ss2.getRange(k, 1).getValue() === task){ //check correct row for task

        var colCount = 1;  
        var start;
        for (var i = 2; i < 100; i++){ //iterate over columns (searching dates)
          var dateCell = ss2.getRange(1, i).getValue().getTime(); //this is cell that has date

          if (dateCell == startDate){
            start = i; //set column where start date is
          }
          else if (dateCell != endDate && dateCell != startDate){
            colCount++;
          }
          else if (dateCell == endDate){
            e.range.setNote("4.5: - start " + start + " - colCount " + colCount);
            ss2.getRange(k, start, 1, colCount).setBackground("#4d79ff"); //set blue
            ss2.getRange(k, i).setBackground("#fe4343"); //set red, working fine
            return;
          }
        }
      }
    }

Edit: found an alternative way of achieving what I wanted. I just used the line 

    var cols = ss2.getRange(1, i).getColumn() - start;

to get the number of columns to paint, then use cols instead of colCount.

Upvotes: 0

Views: 77

Answers (1)

Tiffany G. Wilson
Tiffany G. Wilson

Reputation: 573

I think your problem might be in the line

var dateCell = ss2.getRange(1, i).getValue().getTime();

Shouldn't you be getting the range (k,i) since you're on row k?

Upvotes: 2

Related Questions