CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user6738171
user6738171

Reputation: 1007

Change the value of a cell if another cell has a date that falls within the current week - apps script

In google sheets I have a task table. Included in the table is a column that has a timeline dropdown and a column that includes a do date. I am trying to change the value of the timeline column if the do date falls within the current week.

For example, using my screenshot below:

Today is 2/7/2023 - I would like to see if the do date falls within this current week (2/5/2023-2/11/2023). Because the do date falls within this week (2/10/2023) I would like the Timeline to change to "This Week"

enter image description here

Here is the code I have tried. I was thinking I could get the first day and last day of the current week, and then compare those two dates to the date listed in the do date column.

function onEdit(event){
  var colK = 11;  // Column Number of "K"

  var changedRange = event.source.getActiveRange();
  if (changedRange.getColumn() == colK) {
    // An edit has occurred in Column K

//Get this week
var curr = new Date; // get current date
var first = curr.getDate() - curr.getDay(); // First day is the day of the month - the day of the week
var last = first + 6; // last day is the first day + 6

var firstday = new Date(curr.setDate(first)).toUTCString();
var lastday = new Date(curr.setDate(last)).toUTCString();
var doDateTW = new Date(changedRange.getValue.setDate()).toUTCString();



    //Set value to dropdown 
    var group = event.source.getActiveSheet().getRange(changedRange.getRow(), colK - 5);

if (doDateTW >= firstday && doDateTW <= lastday) {
      group.setValue("This Week");
    }


  }
}

Upvotes: 1

Views: 118

Answers (1)

Tanaike
Tanaike

Reputation: 201553

In your situation, how about the following modification?

Modified script:

function onEdit(event) {
  var { range, source } = event;
  var colK = 11;
  var changedRange = source.getActiveRange();
  if (changedRange.getColumn() == colK) {
    var curr = new Date();
    var year = curr.getFullYear();
    var month = curr.getMonth();
    var first = curr.getDate() - curr.getDay();
    var last = first + 6;
    var firstday = new Date(year, month, first).getTime();
    var lastday = new Date(year, month, last).getTime();
    var doDateTW = range.getValue().getTime();
    var group = source.getActiveSheet().getRange(changedRange.getRow(), colK - 5);
    if (doDateTW >= firstday && doDateTW <= lastday) {
      group.setValue("This Week");
    } else {
      group.setValue(null);
    }
  }
}

  • When you put a date value to the column "K", when the inputted date value is in this week, "This Week" is put into column "F". When the inputted date value is not in this week, the cell of column "F" is cleared.

  • In this modification, I used your script of var first = curr.getDate() - curr.getDay(); and var last = first + 6;.

Upvotes: 1

Related Questions