I am learning right now scripts functionally in Google Sheet, however, can't twist my head around constructing a very simple App script.

I have the following table (Snoopi Tab) https://docs.google.com/spreadsheets/d/1l6nYBAqB1GWoMkIOwlykhiuMpaXdWHTo7UhgZdq6hT8/edit?usp=sharing

I want it to do this simple action:

EXAMPLE: If today is not Sunday or Saturday and the date is 14.2.14 and cell BF5 is ---> go down 3 rows and paste current time "Clocking in" working-shift

When button "IN" is clicked:
If (TODAYDATE = Value in cell in row 5) & (row 3 ==!"S") both true

    Set current time in (same column just row 8) 

Same with "OUT" button, but this I'll try to figure by myself.

CodePudding user response：

function myFunction() {
  var actualDate = new Date(new Date().setHours(0, 0, 0, 0)).getTime();
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getRange("E3:NE6").getValues();

  for(var i = 0; i < data[0].length; i  ) {
    if (data[2][i].getTime() === actualDate) {
      if (data[0][i] !== "S") {
        sheet.getRange(8, (5 i)).setValue(new Date().getHours()   ":"   new Date().getMinutes());
      }
      break;
    }
  }
}