Automatic timestamp update upon edit

Question

I have a spreadsheet with multiple columns. When any column gets updated, I need it to update the timestamp on column 5 which is our timestamp column:

https://docs.google.com/spreadsheets/d/1zOKwPL0LMtWpcbhji6XDi-8Sx3-WfxRXnboZ_Le0wZo/edit?usp=sharing

but I can't for the life figure out why it won't work.

Answer 1

try this answer from stackexchange

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
   var r = s.getActiveCell();
   if( r.getColumn() == 13 ) { //checks the column
     var nextCell = r.offset(0, 1);
     if( nextCell.getValue() === '' ) //is empty?
       var time = new Date();
       time = Utilities.formatDate(time, "GMT", "HH:mm:ss");
       nextCell.setValue(time);
   };
 };
}

You can try adding a Google Apps Script to capture when a cell is edited and add a timestamp to a different cell

Answer 2

The current script that you have will work (putting timestamp in column I) after a few minor edits AND removal of the Slicer

1. go into the Apps Script and correct the sheet name from:

  if (r.getColumn() < 9 && ss.getName()=='Sheet1') { // 2. If Edit is done in any column before Column (I)  And sheet name is Sheet1 then:

to:

  if (r.getColumn() < 9 && ss.getName()=='Sheet 1') { // 2. If Edit is done in any column before Column (I)  And sheet name is Sheet1 then:

2. while in the Apps Script editor, change the App Script's timezone by using the Legacy editor and clicking File > Project properties > Time Zone from the menu above the script editor.

3. Save the Apps Script, and return to the spreadsheet.

4. Change G-Sheets timezone (to match the timezone you selected in the Apps Script) by clicking File > Spreadsheet settings

5. Remove the Slicer

6. Test the script is working

I have tried several scripts that normally work (without Slicer), but the Slicer interferes with all of them so i can not offer you a script that works with the current Spreadsheet design while it uses the Slicer.

---

For a script that puts timestamp in column E when any edits are made in any other column, you can try this:

  const sh = SpreadsheetApp.getActiveSheet();
  const SHEETNAME = 'Sheet 1'; // SHEET to MONITOR
  const COLTSL = 'E'; // COLUMN LETTER to TIMESTAMP

function onEdit(e) {
  let r = sh.getActiveCell();
  // If Edit is done in any column other than the timestamp column,  And sheet name is Sheet 1 then:
  if (r.getColumn() !== COLTSL && sh.getName() == SHEETNAME) { 
  let celladdress = COLTSL + r.getRowIndex() 
    sh.getRange(celladdress).setValue(new Date()).setNumberFormat("dd/MM/yyyy hh:mm");
  }
}

However, this still has limitation that setValue will not function correctly (some rows work, others do not) while you have the Slicer enabled.

Answer 3

Not sure have your question got a solution. I had the same struggle as yours over the year, and I found a solution that works for my case nicely.

Some background for my case:

• I have multiple sheets in the spreadsheet to run and generate the timestamp
• I want to skip my first sheet without running to generate timestamp in it
• I want every edit, even if each value that I paste from Excel to generate timestamp
• I want the timestamp to be individual, each row have their own timestamp precise to every second
• I don't want a total refresh of the entire sheet timestamp when I am editing any other row
• I have a column that is a MUST FILL value to justify whether the timestamp needs to be generated for that particular row
• I want to specify my timestamp on a dedicated column only

function timestamp() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const totalSheet = ss.getSheets();

  for (let a=1; a<totalSheet.length; a++) {
    let sheet = ss.getSheets()[a];
    let range = sheet.getDataRange();
    let values = range.getValues(); 

    function autoCount() {
      let rowCount;
      for (let i = 0; i < values.length; i++) {
        rowCount = i
        if (values[i][0] === '') {
          break;
        }
      }
      return rowCount
    }

    rowNum = autoCount()

    for(let j=1; j<rowNum+1; j++){
      if (sheet.getRange(j+1,7).getValue() === '') {
        sheet.getRange(j+1,7).setValue(new Date()).setNumberFormat("yyyy-MM-dd hh:mm:ss");
      }
    }
  }
}

Explanation

1. First, I made a const totalSheet with getSheets() and run it with a for loop. That is to identify the total number of sheets inside that spreadsheet. Take note, in here, I made let a=1; supposed all JavaScript the same, starts with 0, value 1 is to skip the first sheet and run on the second sheet onwards
2. then, you will notice a function let sheet = ss.getSheets()[a] inside the loop. Take note, it is not supposed to use const if your value inside the variable is constantly changing, so use let instead will work fine.
3. then, you will see a function autoCount(). That is to make a for loop to count the number of rows that have values edited in it. The if (values[i][0] === '') is to navigate the script to search through the entire sheet that has value, looking at the row i and the column 0. Here, the 0 is indicating the first column of the sheet, and the i is the row of the sheet. Yes, it works like a json object with panda feeling.
4. then, you found the number of rows that are edited by running the autoCount(). Give it a rowNum variable to contain the result.
5. then, pass that rowNum into a new for loop, and use if (sheeet.getRange(j+1,7).getValue() === '') to determine which row has not been edited with timestamp. Take note, where the 7 here indicating the 7th column of the sheet is the place that I want a timestamp.
6. inside the for loop, is to setValue with date in a specified format of ("yyyy-MM-dd hh:mm:ss"). You are free to edit into any style you like
7. ohya, do remember to deploy to activate the trigger with event type as On Change. That is not limiting to edit, but for all kinds of changes including paste.

Here's a screenshot on how it would look like:

Lastly, please take note on some of my backgrounds before deciding to or not to have the solution to work for your case. Cheers, and happy coding~!

Answer 4

no more scripts needed:

=LAMBDA(x, IF(JOIN(, A2:D2)="",,x))(IFERROR(A2:D2/0)+NOW())

more at: https://stackoverflow.com/a/66201717/5632629