CODEWITHSUNDEEP
javascriptdategoogle-apps-script
Artemon
Artemon

Reputation: 1

Google App Script Adding 180 days in a loop using if/else conditional statements

The problem I am trying to solve in Google Sheets is as follows:

add 180 days to whichever date passes my conditional test to every single row in a certain column based on 3 dates.

Google Sheets Example

I came up with the following script:

function adjustDates() {

  var app = SpreadsheetApp;
  var activeSheet = app.getActiveSpreadsheet().getActiveSheet();

  for(var i=2; i<5; i++) {

    var updatedCell = activeSheet.getRange(i, 2).getDate;
    var removedCell = activeSheet.getRange(i, 4).getDate;
    var implementedCell = activeSheet.getRange(i, 1).getDate;

    if (updatedCell == null && removedCell == null){
      activeSheet.getRange(i, 3).setValue(implementedCell+180);
    } else if (updatedCell != null){
      activeSheet.getRange(i, 3).setValue(updatedCell+180);
    } else {
      activeSheet.getRange(i, 3).setValue(removedCell+180);
    }
  }
}

I think I nailed down the logic, but I can't figure out why I am getting #NUM! error in all of my rows. I don't need to worry about leap years so this was a brute force solution on my part.

I am very new to JavaScript so please be gentle.

Thanks!

Upvotes: 0

Views: 176

Answers (2)

Cooper
Cooper

Reputation: 64072

    new Date(date.getFullYear(),date.getMonth(),date.getDate() + 180)

Upvotes: 0

NightEye
NightEye

Reputation: 11204

There is no such thing as getDate in class range.

You need to get the value first via getValue, and then as Matriarx mentioned, set the date using date.setDate(date.getDate() + 180)

var implementedCell = activeSheet.getRange(i, 1).getValue();

// we make sure to cast the date properly to avoid unexpected errors
var iCellDate = new Date(implementedCell);
iCellDate = iCellDate.setDate(iCellDate.getDate() + 180)
activeSheet.getRange(i, 3).setValue(iCellDate);

Apply to all instances.

Upvotes: 1

Related Questions