跳到主要内容

如何在Google表格的另一列中更新数据时自动插入时间戳？

如果单元格范围较大，并且要在另一列中修改或更新数据时将时间戳自动插入相邻的单元格中。 您如何在Google表格中解决此任务？

使用脚本代码在另一列中更新数据时自动插入时间戳记

使用脚本代码在另一列中更新数据时自动插入时间戳记

以下脚本代码可以帮助您快速轻松地完成此工作，请按以下步骤操作：

1。 点击 工具 > 脚本编辑器，请参见屏幕截图：

2。 在打开的项目窗口中，请复制并粘贴以下脚本代码以替换原始代码，请参见屏幕截图：

function onEdit(e)
{ 
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() == "order data") //"order data" is the name of the sheet where you want to run this script.
  {
    var actRng = sheet.getActiveRange();
    var editColumn = actRng.getColumn();
    var rowIndex = actRng.getRowIndex();
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
    var dateCol = headers[0].indexOf("Date") + 1;
    var orderCol = headers[0].indexOf("Order") + 1;
    if (dateCol > 0 && rowIndex > 1 && editColumn == orderCol) 
    { 
      sheet.getRange(rowIndex, dateCol).setValue(Utilities.formatDate(new Date(), "UTC+8", "MM-dd-yyyy")); 
    }
  }
}

备注：在上面的代码中， 订单数据 是您要使用的工作表名称， 日期 是要插入时间戳的列标题，并且 订购产品 是列标题，您要更新的单元格值。 请根据您的需要进行更改。

3。 然后保存项目窗口，并为此新项目输入名称，请参见屏幕截图：

4。 然后返回到工作表，现在，在修改“订单”列中的数据时，当前时间戳会自动插入到“日期”列中与修改后的单元格相邻的单元格中，请参见屏幕截图：

最佳办公生产力工具

🤖 Kutools 人工智能助手：基于以下内容彻底改变数据分析： 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup  |   多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧   |  工作簿和工作表管理器   |  资源库 （自动文本）   |  日期选择器   |  合并工作表   |  加密/解密单元格   |  按列表发送电子邮件   |  超级筛选   |   特殊过滤器 （过滤粗体/斜体/删除线...）...
前 15 个工具集:  12 文本 工具 (添加文本, 删除字符，...）   |   50+ 图表 类型 (甘特图，...）   |   40+ 实用 公式 (根据生日计算年龄，...）   |   19 插入 工具 (插入二维码, 从路径插入图片，...）   |   12 转化 工具 (小写金额转大写, 货币兑换，...）   |   7 合并与拆分 工具 (高级组合行, 分裂细胞，...）   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能，体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述

Office Tab 为 Office 带来选项卡式界面，让您的工作更加轻松

  • 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50％，并减少数百次鼠标单击！
Comments (62)
No ratings yet. Be the first to rate!
betha
betha
This comment was minimized by the moderator on the site
Hi.. Thank you very much,
I was struggling to get the below excel macro script into google sheet script. Can you please help me out.
"Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 10 Then
Application.EnableEvents = False
If Target.Value <> """" Then
Target.Offset(, 6).Value = Environ(""username"")
Else
Target.Offset(, 6).ClearContents
End If
Application.EnableEvents = True
End If

If Target.Column = 11 And Target.Column Mod 1 = 0 And Target.Row >= -8 Then
For Each c In Target
If c.Value = c.Offset(0, -4).Value Then
c.Offset(0, -8).Value = Format(Date, ""DD/MMM/YYYY"")
Else
c.Offset(0, -8).Value = """"

End If
Next c

End If


If Target.Column = 10 And Target.Column Mod 3 = 1 And Target.Row >= 6 Then
For Each c In Target
If c.Value = """" Then
c.Offset(0, 7).Value = """"
Else
c.Offset(0, 7).Value = Format(Time, ""h:mm AM/PM"")
End If
Next c
End If
End Sub



"

Reply
0
0
Geneva
Geneva
This comment was minimized by the moderator on the site
This worked for me perfectly. I had three separate columns that I had to update with static dates. Just made three diff arguments for each column and it worked. Thank you!
Reply
0
0
Michelle
Michelle     Geneva
This comment was minimized by the moderator on the site
Can u show how do you do that?
Reply
0
0
MissBFierce
MissBFierce
This comment was minimized by the moderator on the site
How do i apply this to work in multiple tabs in google sheet?
Reply
Report
0
0
Belinda
Belinda
This comment was minimized by the moderator on the site
I need this to apply in multiple sheets. How do I do that
Reply
0
0
SKD
SKD
This comment was minimized by the moderator on the site
Replace e.source by SpreadsheetApp.getActiveSpreadsheet()
Reply
0
0
manish
manish
This comment was minimized by the moderator on the site
TypeError: Cannot read property 'source' of undefined
onEdit @ Code.gs:3pl. give solution ,,, on google sheet
Reply
0
0
MANISH
MANISH
This comment was minimized by the moderator on the site
TypeError: Cannot read property 'source' of undefined
onEdit @ Code.gs:3
Reply
0
0
Vinay Pal
Vinay Pal
This comment was minimized by the moderator on the site
THANK YOU IT WORKED
Reply
0
0
uma
uma
This comment was minimized by the moderator on the site
Hi all, will this script work for the column populated by a formula or auto populated. depending on a auto populated column how can i generate the the updated date.
Reply
0
0
Allyson
Allyson
This comment was minimized by the moderator on the site
Hello I found this very helpful yet have a additional step in my spreadsheet. My Column A data remains untouched which is what I want. I need Column B when updated by staff date&time stamp Column C. I have this so far BUT I also need Column D when updated to date&time stamp Column E. So B&C interact and D&E interact. Advice?
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations