Google Sheets脚本求助：复制数据至历史表并添加格式化日期列

Hey there! Let's work through this together. Since you’re using the IMPORTHTML function in your "Download" sheet, I’ll focus on a Google Apps Script solution first (that’s the most common tool for automating Google Sheets tasks), and I’ll also include an Excel VBA version in case you’re working with Excel instead.

Google Apps Script (for Google Sheets) #

This script will grab all the populated data from "Download", add today’s date in DD/MM/YYYY format to each row, and append it to the "Trade History" sheet starting at column A (so your original data lands in column B onwards).

function copyDataWithDate() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const downloadSheet = ss.getSheetByName("Download");
  const tradeSheet = ss.getSheetByName("Trade History");
  
  // Grab all populated data from the Download sheet (handles variable rows, fixed columns)
  const dataRange = downloadSheet.getDataRange();
  const dataValues = dataRange.getValues();
  
  // Exit if there's no data to copy
  if (dataValues.length === 0) {
    SpreadsheetApp.getUi().alert("No data found in Download sheet!");
    return;
  }
  
  // Format today's date to DD/MM/YYYY
  const today = new Date();
  const formattedDate = Utilities.formatDate(today, Session.getScriptTimeZone(), "dd/MM/yyyy");
  
  // Add the date as the first entry in each data row
  const dataWithDate = dataValues.map(row => [formattedDate, ...row]);
  
  // Find the next empty row in Trade History
  const nextEmptyRow = tradeSheet.getLastRow() + 1;
  
  // Paste the combined date + data into Trade History
  tradeSheet.getRange(nextEmptyRow, 1, dataWithDate.length, dataWithDate[0].length).setValues(dataWithDate);
  
  // Optional: Uncomment below to clear the Download sheet after copying
  // downloadSheet.clearContents();
}

How it works: #

• getDataRange() automatically captures all filled cells in "Download"—no need to hardcode row numbers even if the row count changes.
• Utilities.formatDate() ensures the date uses your script's time zone (you can replace Session.getScriptTimeZone() with a specific zone like "America/New_York" if needed).
• The map() function adds the date to the start of each row, so when we paste, it fills column A and your original data goes into columns B, C, etc.
• We append the data to the bottom of "Trade History" using getLastRow() + 1 to avoid overwriting existing records.

Excel VBA (for Microsoft Excel) #

If you’re using Excel instead of Google Sheets, this macro will do the same job:

Sub CopyDataWithDate()
    Dim downloadSheet As Worksheet
    Dim tradeSheet As Worksheet
    Dim dataRange As Range
    Dim lastDownloadRow As Long
    Dim nextTradeRow As Long
    Dim todayDate As String
    
    ' Set references to your worksheets
    Set downloadSheet = ThisWorkbook.Worksheets("Download")
    Set tradeSheet = ThisWorkbook.Worksheets("Trade History")
    
    ' Find the last populated row in Download sheet
    lastDownloadRow = downloadSheet.Cells(downloadSheet.Rows.Count, "A").End(xlUp).Row
    
    ' Exit if no data exists
    If lastDownloadRow < 1 Then
        MsgBox "No data found in Download sheet!", vbExclamation
        Exit Sub
    End If
    
    ' Define the full data range (all columns with data)
    Set dataRange = downloadSheet.Range("A1:" & downloadSheet.Cells(lastDownloadRow, downloadSheet.UsedRange.Columns.Count).Address)
    
    ' Format today's date to DD/MM/YYYY
    todayDate = Format(Date, "dd/mm/yyyy")
    
    ' Find the next empty row in Trade History
    nextTradeRow = tradeSheet.Cells(tradeSheet.Rows.Count, "A").End(xlUp).Row + 1
    
    ' Paste data to column B of Trade History
    dataRange.Copy Destination:=tradeSheet.Cells(nextTradeRow, "B")
    
    ' Fill column A with today's date for all pasted rows
    tradeSheet.Range(tradeSheet.Cells(nextTradeRow, "A"), tradeSheet.Cells(nextTradeRow + dataRange.Rows.Count - 1, "A")).Value = todayDate
    
    ' Optional: Uncomment below to clear the Download sheet after copying
    ' downloadSheet.Cells.ClearContents
End Sub

How it works: #

• We find the last row with data in "Download" using End(xlUp) to handle variable row counts.
• Format(Date, "dd/mm/yyyy") creates the required date string.
• We paste the original data starting at column B, then fill column A with today’s date for the same number of rows as the copied data.

内容的提问来源于stack exchange，提问作者Trent Lindop