Google-sheets – Timestamping purchase entries

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

I’ve created myself a budget tracker where I can track what I bought and how much I spent across a few categories. I specified column B (purchase entries are rows) to be the date and time at which I made a purchase using a conditional formula and today() that populates the cell with the date as for when an entry is made in that row.

I soon found out that today() auto refreshes when the worksheet is modified. Is there another way to write this to fit my needs for more of an automatic timestamp? I’m reluctant to hand jamming the date because I already am using Google sheets on my phone to enter most entries and it’s time-consuming enough.

Best Answer

  • add this script to your sheet:

    function onEdit() {
      var s = SpreadsheetApp.getActiveSheet();
      if( s.getName() == "Sheet1" ) {
        var r = s.getActiveCell();
        if( r.getColumn() == 1 ) {
          var nextCell = r.offset(0, 1);
          if( nextCell.getValue() === '' )
            nextCell.setValue(new Date(), "GMT+2", "MM/dd/yyyy hh:mm:ss");
        }
      }
    }

  • if( s.getName() == "Sheet1" ) { - change Sheet1 if your sheet has a different name
  • if( r.getColumn() == 1 ) { - change 1 if your non-date data are not in A column
  • var nextCell = r.offset(0, 1); - change 1 to move date stamp further than next column
  • nextCell.setValue(new Date() "GMT+2"); - change GMT+2 to mach your timezone
Related Topic