Google Sheets Timestamp – How to Stop NOW Function from Auto-Updating

google sheetsgoogle-sheets-timestampworksheet-function

I'm using Google Spreadsheet's NOW() function as a shortcut to insert the current timestamp now.

However, the value in the cell changes whenever the spreadsheet is modified in any way, even when the changes have nothing to do with that particular cell.

How do we stop the value in the cell from being automatically updated?

Best Answer

Try Shift+Ctrl+: and Ctrl+;

Those are the keyboard shortcuts for "insert time" and "insert date" respectively.

(based on pnuts' comment)

Related Solutions

Google-sheets – Monitor webpage changes using Google Spreadsheets

Yes, it's possible to do that with a simple script such as this one. What it does:

Check whether the current value is equal to the most recent "archived" value.
If not, then archive the current value, inserting a row for this purpose.

After entering the script into the Script Editor, you should create a trigger for it: see Resources > This project's triggers. Two options are: running periodically, or running when the data changes. The second one is more natural for this task, but in case onChange fails to work for you (some people had issues in the past), timed trigger is an option.

function recordChanges() {
  var url = 'the URL of your spreadsheet';   // put URL here
  var ss = SpreadsheetApp.openByUrl(url);
  var sh = ss.getSheetByName('Sheet1');  // or whatever name
  var range = sh.getRange("A1:A2");    // assuming your data appears in A1 cell
  var values = range.getValues();
  if (values[0][0] != values[1][0]) {
    sh.insertRowAfter(1);
    sh.getRange("A2").setValue(values[0][0]);
  }
}

This can go on until you hit the limit of 200,000 cells in a spreadsheet... if the spreadsheet has one column, then it can have up to 200,000 rows, so even if another row is added every hour, it will last more than 20 years.

Google-sheets – How to set Audio alert in Google spreadsheet that triggers by a specific value

Short answer

At this time it's not possible to trigger an audio alert through an on edit event without opening and HTML sidebar or dialog.

Explanation

The equivalent to Excel VBA is Google Apps Script which is based on JavaScript. Unfortunately they don't include a class for sounds.

The answer by Mogsdad to the referred Stack Overflow question in a comment by user 79865 use the audio tag from HTML5, that could be used in an HTML side bar or dialog but not alone.