CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstimestampspreadsheet
shou
shou

Reputation: 25

Static timestamping in Google Sheets

I am trying to add STATIC timestamp to my data whenever it is imported or pasted in the sheets.

I am using this formula now

(=ARRAYFORMULA( IFS(I1:I="","",L1:L="",NOW(),TRUE,L1:L)))

but, whenever I open the sheet again the time gets changed automatically to the current time as i am using the now() function. I tried on-Edit in the script, but it's only working when the data is manually entered.

Is there any other way I can use to static timestamp when data is being pasted or imported?

Upvotes: 0

Views: 2874

Answers (4)

doubleunary
doubleunary

Reputation: 18784

You cannot get a permanent timestamp with a spreadsheet formula, even with a named function or an Apps Script custom function, because formula results refreshed from time to time. When the formula gets recalculated, the original timestamp is lost.

The easiest way to insert the current date in a cell is to press Control + ; or ⌘;. See the keyboard shortcuts help page.

You can also use an onEdit(e) script to create permanent timestamps. Search this forum for [google-apps-script] timestamp to find many examples.

Upvotes: 0

Kor
Kor

Reputation: 48

Not sure have your question got a solution. I had the same struggle as yours over the year, especially with pasted data, and I found a solution that works for my case nicely (but not by formula, need to run in Apps Script).

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:

First Column Value 7th Column Timestamp

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~!

Upvotes: 0

fullfine
fullfine

Reputation: 1461

Answer

You can use a custom function to return the actual date with the method new Date() and the Properties Service. Open Apps Script and paste the following function:

Code

function getTimestamp(reset) {

  // update the timestamp
  if (reset == 1) {
    setTime()
  }

  // try-catch structure in order to set the time in the first execution
  try {
    var time = ScriptProperties.getProperty('time')
  }
  catch (err) {
    setTime()
    var time = ScriptProperties.getProperty('time')
  }

  return time

}

function setTime() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var time = new Date()
  ScriptProperties.setProperty('time', time)
}

How it works

Now, you can use it in any cell like another Sheet function. Call the function with =getTimestamp(0). On the first execution, it tries to get the saved property time, but as the property does not exist it generates a timestamp and saves a new property in the project with the key time and the value of the timestamp.

In the following executions, the value obtained by the function when it is recalculated is the same, since the property is not overwritten unless the function is called with a 1 input: =getTimestamp(1). In this case, the timestamp is updated, but if it is not set back to =getTimestamp(0), every time the function is recalculated (which happens automatically every so often) the timestamp will change.

In conclusion, always use =getTimestamp(0). When you want to update the value, change it to =getTimestamp(1) and go back to the original formula.

update

I have updated the answer to explain how to update the timestamp when new values are added:

  1. Use a cell as input to the function, e.g. =getTimeStamp(A1) 2.
  2. Create an onEdit trigger
  3. Check that the range of the e event belongs to new values.
  4. Update the value of A1 to 1 and then to 0 if you have detected new values.

example:

function onEdit(e){
  var range = e.range
  var cell = SpreadsheetApp.getActiveSpreadsheet().getRange('A4')
  if (range.columnStart > 1 && range.rowStart > 10){
    cell.setValue(1)
    SpreadsheetApp.flush()
    cell.setValue(0)
  }
}

If new values are added from column 1 and row 10, A1 is updated to 1 and then to 0, thus updating the value of the timeStamp function and saving it permanently until the trigger is executed again.

References:

Upvotes: 1

Rodrigo Biffi
Rodrigo Biffi

Reputation: 428

Instead of NOW() on the formula, do it via script using new Date().

The NOW() function updates the timestamp every time the spreadsheet is open or something changes in it, while the new Date() gives you a full (date and time) and static timestamp.

Also, as I've seen on the comments of your question, there really is no way to use onEdit() through automated scripts and macros.

Upvotes: 1

Related Questions