CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstimestamp
weizer
weizer

Reputation: 1127

How to prevent the NOW() function in google sheet to update everytime when there is a change in the cells in the sheet?

I want to print the timestamp in column C when the status shows "Complete". However, when I use the NOW() function, it will keep updating the cells when there is a change in other cells. My formula in Cell C4 is =IF(B4="Complete",NOW(),""). This formula are not able to solve the problem of auto-updating. May I know is there anyway to handle this situation? If the Google App Script is the only way (hopefully not), how should I code it (first time deal with JavaScript)?

Upvotes: 2

Views: 12104

Answers (3)

user14915635
user14915635

Reputation: 396

I was able to figure out how to keep the timestamp from changing by using a formula that someone had shared with me a while back. I haven't figured out how to get it to work though using ARRAYFORMULA, but it does work if you just drag down the formula to all the cells in Column B that you want it to work for. You can test it out by typing in "Complete", starting in A5, and you'll see that it works. Here's the formula I used...

=if(A5="Complete",lambda(x,x)(now()),)

Here's a link to make a copy of the Google Sheet to test it out.

One other thing...if you'd like this to work where it provides the timestamp when a box is checked, you can use this formula, and it will freeze the timestamp.

=if(A5,lambda(x,x)(now()),)

Don't forget to drag down the formula to all cells in the column where you want the timestamp to appear.

Upvotes: 0

Rakesh Singh
Rakesh Singh

Reputation: 1

Your formula is correct but need to change little things. You should write this formula in this way if(C4,C4,if($B4="Complete",NOW(),"")). and go to the file menu then spredsheet settings then calculation and then recalculation and at last its change to "On Change".

Upvotes: 0

Wicket
Wicket

Reputation: 38435

In Google Sheets there is no way to stop NOW() from recalculating other than replace it by it's value.

The best way to achieve what you are looking for is to use Google Apps Script, more specifically you will have to use an on edit trigger. Below is an example based on what is shown in the screenshot included in the question:

function onEdit(e){
  if(e.range.columnStart === 2 && e.range.rowStart >= 4 && e.value === 'Complete'){
     e.range.offset(0,1).setValue(new Date());
  }
}

Please read Extending Google Sheets with Apps Script. This is a very brief guide to get started with using Google Apps Script with Google Sheets and will point you to the resources to learn the basics of JavaScript.

NOTE: JavaScript and Java are two different programming languages.

Related

Upvotes: 1

Related Questions