CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Stefan Slavov
Stefan Slavov

Reputation: 25

set a timestamp in the first blank cell in a row

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

Upvotes: 2

Views: 121

Answers (3)

SAAD
SAAD

Reputation: 789

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;
    }
  }

Upvotes: 1

Wicket
Wicket

Reputation: 38470

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());

Upvotes: 1

Yuri Khristich
Yuri Khristich

Reputation: 14537

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.

Upvotes: 0

Related Questions