VBA到GAS脚本转换 [英] VBA to GAS script conversion

问题描述

我对Excel和Spreadsheets中的宏和东西真的很陌生.我有一个朋友，他在Excel中创建了此宏，可以完成所需的工作:

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?

推荐答案

我会做这样的事情.每当您添加新值时，onEdit(e)就会触发. 然后，您获取值并将实际日期放置在另一个sheet中的同一单元格中.如果您不想每次都不要执行此代码行，则还可以添加条件.

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);
  }
}

此处是正确的文档格式化您的Date.

Here is documentation to properly format your Date.

这篇关于VBA到GAS脚本转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！