set a timestamp in the first blank cell in a row

user_12638061 0
Viewed 72

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());    
  }
  }
google-apps-script google-sheets
3 Answers

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());
      break;
    }
  }
user_21921150

IMHO is a very bad idea to call an onEdit function (a simple installable trigger) from an onSelectionChange function (another simple installable trigger) as there might be cases when an edit will occur immediately after a selection change occurs.

If you are looking to avoid repeating the timestamping code on two simple triggers, then "promote" the timestamping code into a function and call it from both triggers.

function onSelectionChange(){
  // Some code here
  timestamping();
}

function onEdit(){
  // Some code here
  timestamping();
}

function timestamping() {
  // timestamping code here
}

Regarding setting the timestamp in first blank cell in a row, using indexOf you might get the column, you might use something like this:

var sheet = e.range.getSheet();
/** Get the edited row values */
var rowValues = sheet.getRange(e.range.rowStart,1,1,sheet.getLastColumn()).getValues()[0];
/** Get the column of first empty cell */
var column = rowValues.indexOf('') + 1;
/** First evaluate if there was found and empty cell, if so, add the timestamp, otherwsie do nothing */
if(column > 0) attendanceSheet.getRange(row, column).setValue(new Date());
user_15954510

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.

user_142654690
Related