跳到主要內容

如何在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。 然後返回到工作表，現在，在修改“訂單”列中的數據時，當前時間戳會自動插入到“日期”列中與修改後的單元格相鄰的單元格中，請參見屏幕截圖：

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

增強您的 Excel 技能 Kutools for Excel，體驗前所未有的效率。 Kutools for Excel 提供 300 多種高級功能，可提高工作效率並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905

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