1

I have a sheet. What I am trying to do is when I click on a new row to automatically set the first column in the row to =now().

I tried setting =Now() in the cell but it appears to be updating it all time time.

So I tried doing some kind of if statement. IF the next cell is empty then I set the first cell to now. If its not then I leave it to the original value.

=IF(ISBLANK(CONCAT("b",ROW())), NOW(), CONCAT("a",ROW()))

This didn't work as it becomes a circular reference.

Is there a way to configure my sheet so that the first column is always filled out with the current date? the time stamp of when the row was created.

5
  • so you are trying to add datetime stamp based on your statement or based on new row creation? - there is a difference. also... NOW(), TODAY() is a volatile type of function which means it gets updated constantly unless you freeze it with a script or sophisticated formula statements – user0 Aug 27 '18 at 12:53
  • Exactly I just want new rows to be time stamped. I tried adding a script but i dont want to have to run it all the time i would rather it just work as i type. – DaImTo Aug 27 '18 at 12:59
  • how about script that will populate first column with time stamp only each row you edit - would that satisfy you? – user0 Aug 27 '18 at 13:03
  • yup. But can you set scripts to run on edit? – DaImTo Aug 27 '18 at 13:05
  • 1
    yeah, why not :) let me find the script you need... – user0 Aug 27 '18 at 13:08
1
function onEdit(e) {
  e.range
   .getSheet()
   .getRange(e.range.getRow(), 1, e.range.getNumRows())
   .setValue(new Date())
   .setNumberFormat('dd.MM.yy, hh:mm:ss');
}

demo sheet

or try this:

function onEdit(e) {

  var range = e.range;
  var sheet = range.getSheet();
  var row = range.getRow();
  var rows = range.getNumRows();
  var date = new Date();

  var dates = sheet
      .getRange(row, 1, rows)
      .getValues()
      .map(function(row) {return [row[0] || date];});

  sheet
   .getRange(row, 1, rows)
   .setValues(dates)
   .setNumberFormat('dd.MM.yy, hh:mm:ss');
}
3
  • WOW. Ok i am curious. Are there other triggers like onEdit? It must automatically fire any scripts attached to the sheet? – DaImTo Aug 28 '18 at 6:22
  • one question if you have time. Any way to get it to only update if its not already set to a date. I only want it set when the row is first created not every time i edit the row. – DaImTo Aug 28 '18 at 7:32
  • 1
    @DaImTo you can check: developers.google.com/apps-script/guides/triggers onEdit trigger will fire only for those scripts that are onEdit when you edit your sheet – user0 Aug 28 '18 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.