防止时间戳公式在Google表格中重新计算 [英] Preventing Timestamp Formulas from Recalculating in Google Sheets

问题描述

我们是一家在全国设有多个呼叫中心的公司.为了跟踪谁处于离线状态以及何时(让我们知道预定的工作时间和登录的时间)，我们希望跟踪电话会议.

其中有两个部分，我们将实时使用它来找出当前谁离线，然后最终使用收集到的数字来帮助我们将来更好地工作人员

用途:我希望我们的远程团队能够在B列或C列中输入"x"，然后工作表(或工作表上运行的脚本)将为开始时间或结束时间做自动时间戳记在G或H列中.

问题:由于某种原因，我一直在使用的时间戳脚本被触发每天运行几次，并将所有开始时间和结束时间重置为当前时间，从而丢失了我们标记的所有持续时间数据.远程使用此功能的团队非常精通技术.这么简单才是最好的.

完美世界结果:当时间戳记位于字段中时，它将永远不会重新计算公式.

问题:有人可以帮助我在包含公式结果的G列或H列中的任何单元中进行特殊复制和粘贴(仅值)脚本(但将公式保留在空白单元格中)吗? )

或

以任何方式阻止工作表重新计算，由于时间变化，我无法弄清楚是什么导致该工作表重新计算.

感谢您的帮助.这是工作表的链接:

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

解决方案

我将此脚本添加到了您的电子表格中:

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());
}

该脚本应能在除第一个工作表之外的所有工作表上工作，并且在B或C中输入X(不区分大小写)时，它将在G或H列中写入时间戳.

注意:

• 我已经注释掉了您拥有的其他脚本.

• 确保删除G和H列中的这些类型的公式:

  = IF(B4 ="，"，TIMESTAMP())

• 删除公式后，在B列或C列中输入"X"来尝试脚本，看看是否可行?

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

解决方案

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 ?

这篇关于防止时间戳公式在Google表格中重新计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！