Apps Script IF columns are filled THEN timestamp, IF columns are Empty then CLEAR-CodePudding

I have column A which is supposed to hold time. If any data is entered in columns B through G then a timestamp should be entered into Column A and then remain static. If ALL of the data in those columns (Column B through G is empty) then the timestamp in A (if it was there) should clear.

Currently the script I have is working except for clearing the row in column A. I know I'm probably missing something silly, but I can't get it right!

function  checkinTimeStamp(e){
  var s = SpreadsheetApp.getActive();
  var range = e.range;
  var column = range.getColumn();
  if (column == 2 || column == 3 || column == 4 || column == 5 || column == 6 || column == 7){
    var timestamp = Utilities.formatDate(new Date, s.getSpreadsheetTimeZone(), "hh:mm");
    var row = range.getRow();
    var value = e.value || '';
    var sheet = e.source.getActiveSheet();
    if(sheet.getRange(row,1).getValue() ==""){
      if(value !== ""){
      sheet.getRange(row, 1).setValue(timestamp);
    }
    //if(sheet.getRange(row,1).getValue() !==""){
    //  sheet.getRange(row, 1).setValue(currentvalue);
    }
   // else{
      if(sheet.getRange(column == 2 && column == 3 && column == 4 && column == 5 && column == 6 && column == 7).getValue() ==""){
      sheet.getRange(row, 1).setValue('');
    }
  }
}

CodePudding user response：

In your script, for example, as a simple modification, how about following modification?

From:

  if(sheet.getRange(column == 2 && column == 3 && column == 4 && column == 5 && column == 6 && column == 7).getValue() ==""){
  sheet.getRange(row, 1).setValue('');
}

To:

if (sheet.getRange(row, 2, 1, 6).getValues()[0].join("") == "") {
  sheet.getRange(row, 1).setValue('');
}

In this modification, the row value is retrieved and compare it with "". By this, when the columns "B:G" of the row are empty, sheet.getRange(row, 1).setValue('') is run.

CodePudding user response：

Hey I tried a different approach using the forEach loop to go through each row to first look at if ColB is empty (clear timestamp if empty) then print a timestamp if there is items filled in ColB. I have shared the image of the before and after running code.

function  checkinTimeStamp(){
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var sheet=s.getSheetByName('Sheet1');
  var data=sheet.getDataRange().getValues();
  var timezone = s.getSpreadsheetTimeZone();
  var timestamp = Utilities.formatDate(new Date(), timezone, "hh:mm");

// Go through each row and if ColB is empty but ColA isn't, clear the timestamp in ColA
  data.forEach(function(row,col){
    if (col == '') return;     //skip row 1 as title
    if (row[0] == '') return;  //skip if ColA is empty
    if (row[1] != '') return   //skip if ColB is NOT empty
    sheet.getRange(col   1,1).clearContent();
  });

// Go through each row and if ColB is not empty put a timestamp in ColA
  data.forEach(function(row,col){
    if (col == '') return;    //skip row 1 as title
    if (row[1]== '') return; //skip if ColB is empty
    sheet.getRange(col   1,1).setValue(timestamp);
  });
}

Before Running Code

After Running Code

My sample sheet here: https://docs.google.com/spreadsheets/d/1IrEG_YNoUnesg78CDjlEwsVqssElZZ8znciqJC63D1Y/edit#gid=0