CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api
apfick
apfick

Reputation: 55

How to count from a looped range

So here is a bit of the code that retrieves data from a list where dates may or may not be inputted. If there is a date, the month will be extracted, and based on what month it is, I want the cell (17,4 in this case) to count how many dates of that month. The loop goes through the page to pull out all the dates but I do not know how to count or add more than one to (17,4).

for(var i =8;i<=j;i++) {
    var dates = oppwon.getRange(i,22).getValue();
    var tiers = oppwon.getRange(i,14).getValue();


  var month = new Date(dates).getMonth();



  switch (true){
      case((month==1)):
          if(tiers==1)
    {summary.getRange(17,4).setValue()+1;}

        break;

Upvotes: 0

Views: 54

Answers (1)

Tanaike
Tanaike

Reputation: 201713

  • You want to count up the value of the cell of "D17" (getRange(17,4)) when month == 1 and tiers == 1.

If my understanding is correct, how about this modification? Please think of this as just one of several answers

Modified script:

Please modify your script as follows.

From: 
for(var i =8;i<=j;i++) {
    var dates = oppwon.getRange(i,22).getValue();
    var tiers = oppwon.getRange(i,14).getValue();


  var month = new Date(dates).getMonth();



  switch (true){
      case((month==1)):
          if(tiers==1)
    {summary.getRange(17,4).setValue()+1;}

        break;
To: 
var range = summary.getRange(17,4);  // Added
var currentValue = range.getValue();  // Added
for (var i = 8; i <= j; i++) {
  var dates = oppwon.getRange(i,22).getValue();
  var tiers = oppwon.getRange(i,14).getValue();
  var month = new Date(dates).getMonth();
  switch (true) {
    case (month == 1):
    if (tiers == 1) {
      range.setValue(++currentValue);  // Modified
    }
    break;
  • At first, the current value from the cell of "D17" is retrieved. Then, new value is put to the cell by adding the number of 1.

Note:

  • To use getValue() in the for loop leads to the high process cost. But I'm not sure about your whole script. So I couldn't propose about this.

References:

If I misunderstood your question and this was not the result you want, I apologize. At that time, can you provide the sample Spreadsheet and your script for replicating your issue. By this, I would like to confirm it.

Upvotes: 1

Related Questions