CODEWITHSUNDEEP
datetimegoogle-apps-scriptgoogle-sheetsscriptingtimestamp
user16549510
user16549510

Reputation:

Google Sheets: How to input timestamp to every changes made in one cell value field?

I’m not an expert in Google Sheets but can anyone can help me on how to put a timestamp on every updates made on one certain cell, please?

Please check link:

enter image description here

Let’s say that B2 is the total amount of B4-B7 and I want to update the Acct1 from 100.00 to 600.00 that auto updates the B2 to 1500.00. My question is, how do I keep track the updates that has been made on B2 i.e., putting timestamps to another sheet or somewhere in the active sheet. Thank you in advance!

Upvotes: 0

Views: 1069

Answers (3)

google geek
google geek

Reputation: 3

You can actually do this using just google sheets...I got it off a youtube video.

Basically =ifs(A1="","",B1<>"",B1,1*1,now())

It prompts a circular dependency error so just turn that on (files-->settings-->calculation and set to 1. It's awesome...I use it for time stamping my bills/todo list.

Screenshot

Upvotes: 0

Inclu Cat
Inclu Cat

Reputation: 402

Create another sheet for logging. And in onEdit, you can log the changed information to the sheet.

Following is an example code.

function onEdit(e) {
  try{
    var dataSheetName = "Sheet1";
    var logSheetName = "Sheet2";

    if ( SpreadsheetApp.getActiveSheet().getName() !== dataSheetName ){ // check it is the target sheet name
      return;
    }
    var range = e.range;
    var row = range.getRow();
    var col = range.getColumn();
    if ( row < 3 || col !== 2 ){ // check it is Amount cell.
      return;
    }

    var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
    var dataSheet = spreadSheet.getSheetByName(dataSheetName);
    var logSheet = spreadSheet.getSheetByName(logSheetName);
    var nextRow = logSheet.getLastRow() + 1;

    // set timestamp
    logSheet.getRange(nextRow, 1).setValue(new Date());
    // copy data
    var srcRange = dataSheet.getRange(row, 1, 1, 2);
    var dstRange = logSheet.getRange(nextRow, 2, 1, 2);
    dstRange.setValues(srcRange.getValues());
  }
  catch(e){
    Browser.msgBox(e);
  }
}

I made an example spreadsheet, feel free to make a copy and check the behavior. https://docs.google.com/spreadsheets/d/1g0pL7hwuzaS5Yr7Dh7hm_SyRG5TYxwwrlND2Zx3Bo7w/edit?usp=sharing

Upvotes: 1

Cooper
Cooper

Reputation: 64032

I've some people keeping them in notes

function onEdit(e) {
  e.range.setNote(e.range.getNote() + '\n' + new Date());
}

Upvotes: 2

Related Questions