Preventing Timestamp Formulas from Recalculating in Google Sheets

Question

We are a company that has multiple call centers nationwide. In an attempt to track who is offline and when (letting us know how many are scheduled v working logged in) we would like to track conference calls.

2 parts to this, we will use this in Real time to figure out who is offline at the current moment and then eventually to use the numbers collected to be able to help us staff better in the future

The Use: I would like our remote team to be able to type in an "x" in column B or Column C, then the sheet (or script running on the sheet) would do an auto timestamp for start or end time in column G or H.

The issue: The timestamp script i have been using for some reason gets triggered to run a few times a day and resets all start times and end times to the current time, losing all of our duration data for those times that were stamped. The team that uses this remotely is VERY un-tech savvy. So simple is best.

Perfect world Result: When a timesstamp is in a field, it would not re-calculate the formula ever.

Question: is there any type of script someone could help me with to do a Copy and Paste Special (values only) in any cell in Column G or H that contains a formula result (but leaving the formulas in the blank cells?)

or

Any way to prevent the sheet from re-calculating, i cannot figure out what is causing this sheet to re-calcualte since the times vary.

Any help is appreciated. Here is a link to the sheet:

https://docs.google.com/spreadsheets/d/1hbTatQFlZNBPsoKp--jbT3AFhJlR-8t42olaS8cDw/edit?usp=sharing

JPV JPV · Accepted Answer · 2015-03-27T08:43:04

I added this script to your spreadsheet:

function onEdit(e) {
if (e.source.getActiveSheet()
    .getName() === 'WFM View' || e.range.columnStart !== 2 && e.range.columnStart !== 3 ||
    e.range.rowStart < 4 || e.value.toUpperCase() !== 'X') return;
e.range.offset(0, 5)
    .setValue(new Date());
}

The script should work on all sheets except the first one, and will write a timestamp in col G or H when an X (case-insenstive) is entered in B or C.

NOTE:

I've commented out the other scripts you had.
Make sure to remove these type of formula's in col G and H:

=IF(B4="","",TIMESTAMP())
After removing the formulas try the script by entering 'X' in col B or C and see if this works ?

Preventing Timestamp Formulas from Recalculating in Google Sheets

1 Answers