Insert timestamp when value copy/paste or inserts/edits whole row in Column C in google sheets, only insert to blank cell

user_17931829 0
Viewed 353

  • I'm trying to insert a timestamp into a cell in column B that has the same row index as the edited or pasted value in column C. Also, I only want to insert a timestamp in an empty cell in column B, while the skip cell already has a timestamp.

  • This code works correctly whether I edit a cell in column C or paste a value in column C when all cells in column B are empty.

  • This code has issues with overwriting or not writing values when pasting multiple cells. Kindly help me in editing it.

  • Here is sample sheet: insert timestamp - multi cell - check blank cell

function insert_timestamp(e) 
{
  var s = SpreadsheetApp.getActiveSheet();
  var cell = s.getActiveCell();
  var r = e.range;
  var firstRow = r.getRow();
  var numRows = r.getNumRows();
  var firstCol = r.getColumn();
  var lastCol = r.getLastColumn();
  var datecell = s.getRange(cell.getRowIndex(), 2);
  if((firstCol == 3 || lastCol == 3) && s.getName() == 'test' && datecell.isBlank()) {

    var destRange = s.getRange(firstRow, 2, numRows);
    var dates = new Array(numRows).fill([new Date()]);
    destRange.setValues(dates).setNumberFormat("MM/dd/yyyy hh:mm:ss");
    
  }
}

google-apps-script google-sheets
1 Answers

I believe your goal is as follows.

  • You want to put the date to column "B" when column "C" is edited.
  • In this case, you want to put the date, when the column "B" is empty.
  • Function of insert_timestamp is installed as OnEdit trigger.

In order to check the values of columns "B" and "C", I retrieved the values from the columns of edited rows. And, I thought that the event object can be used for your situation. From these points, how about the following sample script?

Sample script:

function insert_timestamp(e) {
  var range = e.range;
  var sheet = range.getSheet();
  if ((range.columnStart == 3 || range.columnEnd == 3) && sheet.getSheetName() == 'test') {
    var values = range.offset(0, -1, range.rowEnd - range.rowStart + 1, 2).getDisplayValues();
    var rangeList = values.reduce((ar, [b, c], i) => {
      if (b == "") ar.push("B" + (i + range.rowStart));
      // if (b == "" && c != "") ar.push("B" + (i + range.rowStart));
      return ar;
    }, []);
    sheet.getRangeList(rangeList).setValue(new Date()).setNumberFormat("MM/dd/yyyy hh:mm:ss");
  }
}
  • If you don't want to put the date to the column "B" when the column "C" is empty, please use if (b == "" && c != "") ar.push("B" + (i + range.rowStart)); instead of if (b == "") ar.push("B" + (i + range.rowStart));.

Note:

  • This sample script is run by the OnEdit trigger. So when you directly run this function, an error occurs. Please be careful this.

Reference:

user_71086530
Related