VBA to GAS script conversion [closed]

问题:

I am really new to macros and stuff in both Excel and Spreadsheets. I had a friend who created this macro in Excel which does the needed job:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim day As String

    If Target.Row > 2 Then        
        day = Date
        Sheets("CFbase").Cells(Target.Row, Target.Column).Value = day
    End If

End Sub

In short: I need to enter a text in my first sheets and the today's date to be generated in the equivalent cell on another sheet.

I need this on order to later on use the dates for conditional formatting later on using the date to color each cell depending if it was updated today or in the last days.

Can this macro be converted to a script for spreadsheets?

回答1:

I would do something like this. The onEdit(e)triggers any time you add a new value. Then you grab the value and place the actual date in the same cell in the other sheet. You can also add conditions if you don't wan't this code lines to execute every time.

function onEdit(e)
{
  // condition to make sure you are not entering value in the date sheet
  if(e.source.getActiveSheet().getName() != "CFbase" && e.range.rowStart > 2)
  {
    //grab the sheet where you wan't the date to be inserted.
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CFbase");
    //grab the cell where the date will be inserted 
    var cell = sheet.getRange(e.range.getRow(), e.range.getColumn());
    //create the actual date
    var now = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy");
    //set the value of the cell with the date
    cell.setValue(now);
  }
}

Here is documentation to properly format your Date.