填写单元格时的自动时间戳 [英] Automatic timestamp when a cell is filled out

问题描述

我有一个非常简单的 excel 公式，它有效，因为我可以限制递归迭代.我不是很精通脚本，但这就是它的本质并且它​​有效.

I have an excel formula that is very simple and it works because I can restrict the recursive iterations. I am not very script savvy, but this is what it is and it works.

=IF(D24="P",IF(E24="",DateStamp,E24),IF(D24="F",IF(E24="",DateStamp,E24),""))

它是一个通过/失败测试表，当有人通过或失败测试时，它会添加一个时间戳.我们又增加了几个人，我想将文档移至 Google 应用，以便 1 人以上同时处理.

Its a pass/fail testing sheet and it adds a timestamp when someone passes or fails the test. We've added a few more people and I want to move the document to google apps to allow more than 1 person to work on it at the same time.

我遇到的唯一问题是这导致的循环引用.在 excel 中，我可以限制选项中的迭代次数，我不再有这种能力了.任何帮助都会很棒.

The only issue i've come in is the circular reference that this causes. In excel I can limit the # of iterations in the options, I dont have this ability anymore. Any help would be great.

我尝试过的.我试图找到一种方法来输入同事创建的对我有用的 VBA 脚本.我不擅长脚本，所以我无法将它变成谷歌应用程序脚本:

What I've tried. I've tried to find a way to input a VBA Script that a coworker created that would work for me. I'm not good with scripting so I'm unable to make this into a google apps script:

VBA 脚本:

    Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 4 Then
If Cells(Target.Row, 5).Value = "" Then

Cells(Target.Row, 5).Value = Now
End If
Else
End If


End Sub

理论上，我尝试创建一个脚本，该脚本将复制一个带有时间戳的单元格，然后尝试特殊粘贴"并将值粘贴到所需的单元格中.这会起作用，除非我无法找到一种使用 google 应用程序脚本粘贴特殊内容的方法.

In theory I tried to create a script that will copy a cell that has a timestamp on it and then try to 'paste special' and just paste the value into the cell needed. This would work except I was unable to find a way to paste special with the google apps scripting.

感谢您的帮助/编辑

推荐答案

Stackoverflow 是一个提出与编程相关的问题的地方，例如你实际上正在努力.并不是真的要求其他人为您开发它，即您甚至还没有开始尝试任何 Apps 脚本代码.我建议您阅读其教程和指南.真的很容易上手.

Stackoverflow is a place to ask questions related to programming, e.g. that you're actually working on. Not really asking for others to develop it for you, i.e. you didn't even started trying any Apps Script code yet. I recommend you reading its tutorials and guides. It's really easy to start.

无论如何，只是为了帮助您入门，我将放弃您所说的所有内容并坚持问题标题:填写单元格时的自动时间戳"

Anyway, just to help you get started, I'll drop everything you said and stick to the question title: "automatic timestamp when a cell is filled out"

我建议您在应用程序脚本上完成所有操作，并完全删除您的公式，例如

I advise you to do it all on apps script, and drop your formulas entirely, e.g.

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if( r.getColumn() == 4 ) { //checks the column
      var nextCell = r.offset(0, 1);
      if( nextCell.getValue() === '' ) //is empty?
        nextCell.setValue(new Date());
    }
  }
}

此代码执行我从您那里了解到的内容，即:如果在 D 列上编辑了某些内容并且 E 列为空，则将当前日期添加到 E.

This code does what I understood from yours, which is: if something is edited on column D and column E is empty, add the current date to E.

这篇关于填写单元格时的自动时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！