set a timestamp in the first blank cell in a row-CodePudding

I'm trying to do 2 things in a spreadsheet each time a cell in col8 is selected. 1st increase the value of the cell in col7 and set a timestamp in the first blank cell in the row. I'm stuck on the timestamp. Increasing the value works and I can set the timestamp to a specific cell as show in the code or in the last col of the sheet, but I can't find a solution to set it in the first blank cell of the row.

function onSelectionChange(e) {
  const as = e.source.getActiveSheet();
  const col = e.range.getColumn();
  const row = e.range.getRow();
  if (as.getName() == 'Sheet1' && col == 8){ 
        const range = as.getRange(row,7);
        range.setValue(range.getValue() 1);
        e.range.setValue('Present');
  }
  onEdit(e);
}

function onEdit(e) {
  var attendanceSheet = e.source.getActiveSheet();
  var row = e.range.getRow();
  var col = e.range.getColumn();

  if(attendanceSheet.getName() == 'Sheet1' && col == 8) {
    
    if (attendanceSheet.getRange(row, 12).getValue() === '')
    attendanceSheet.getRange(row, 12).setValue(new Date());    
  }
  }

CodePudding user response：

You can try this:

function onSelectionChange(e) {

  const as = e.source.getActiveSheet();
  const col = e.range.getColumn();
  const row = e.range.getRow();
  if (as.getName() == 'Sheet1' && col == 8) {
    const range = as.getRange(row, 7);
    range.setValue(range.getValue()   1);
    e.range.setValue('Present');

    const array = as.getRange(row, 1, 1, as.getLastColumn() 1).getValues()[0];
    const empty_col = array.indexOf('')   1;
    as.getRange(row, empty_col).setValue(new Date());
  }
}

But frankly, I doubt that it's a good idea.

CodePudding user response：

You can search for the empty cell in that specific row:

  const vals = ws.getRange(iRow, 1, 1, iColMax).getValues();
  for(var iCol=0; iCol<iColMax; iCol  ) {
    if( vals[0][iCol] == "" ) {
      //Logger.log(`Cell in Row ${iRow} Col ${iCol 1} is empty`);
      ws.getRange(iRow,iCol 1).setValue(new Date());
    }
  }