Google Sheets中勾选复选框时自动新建园艺工单行的可行性咨询

Absolutely, this is completely achievable! The exact implementation depends on the tool you’re using to manage these work orders (like Google Sheets, Microsoft Excel, or a custom internal system), but here’s a clear breakdown of how to make it happen for common spreadsheet tools:

First, Let’s Clarify Your Raw Data & Rules #

Raw Work Order Dataset #

Core Automation Rules Recap #

• Only trigger a new row for Weekly or Fortnightly orders (skip Once Off when the checkbox is ticked)
• New row date = original date + 7 days (Weekly) or 14 days (Fortnightly)
• New row # value = original # + 1
• New row retains original ID, Name, and Frequency values
• Example: Ticking Emily’s row generates:

  Status	#	ID	Date	Name	Frequency
  ☐	4	121	15/05/20	Emily	Fortnightly

Implementation Steps for Common Tools #

1. Google Sheets (Using Apps Script) #

This works with a simple script tied to the checkbox’s edit trigger:

1. Open your sheet and go to Extensions > Apps Script
2. Replace the default code with this:

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const editedCell = e.range;
  
  // Check if edited cell is a checkbox in the first column (Status)
  if (editedCell.getColumn() === 1 && editedCell.isChecked()) {
    const row = editedCell.getRow();
    const originalData = sheet.getRange(row, 2, 1, 5).getValues()[0]; // Grab #, ID, Date, Name, Frequency
    
    const originalNumber = originalData[0];
    const originalID = originalData[1];
    const originalDate = new Date(originalData[2]);
    const originalName = originalData[3];
    const frequency = originalData[4];
    
    // Skip one-time orders
    if (frequency === "Once Off") return;
    
    // Calculate new date
    let newDate = new Date(originalDate);
    newDate.setDate(newDate.getDate() + (frequency === "Weekly" ? 7 : 14));
    
    // Format date to DD/MM/YY
    const formattedNewDate = Utilities.formatDate(newDate, Session.getScriptTimeZone(), "dd/MM/yy");
    
    // Prepare new row data
    const newRow = [false, originalNumber + 1, originalID, formattedNewDate, originalName, frequency];
    
    // Insert new row below the original
    sheet.insertRowAfter(row);
    sheet.getRange(row + 1, 1, 1, 6).setValues([newRow]);
  }
}

3. Save the script, refresh your sheet, and test by ticking a checkbox for a Weekly/Fortnightly order.

2. Microsoft Excel (Using VBA or Power Automate) #

Option A: VBA Macro

1. Right-click your sheet tab and select View Code
2. Paste this VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Value = True Then
        Dim originalRow As Integer
        originalRow = Target.Row
        
        Dim originalNumber As Integer, originalID As Integer
        Dim originalDate As Date, newDate As Date
        Dim originalName As String, frequency As String
        
        originalNumber = Cells(originalRow, 2).Value
        originalID = Cells(originalRow, 3).Value
        originalDate = Cells(originalRow, 4).Value
        originalName = Cells(originalRow, 5).Value
        frequency = Cells(originalRow, 6).Value
        
        ' Skip one-time orders
        If frequency = "Once Off" Then Exit Sub
        
        ' Calculate new date
        newDate = originalDate + IIf(frequency = "Weekly", 7, 14)
        
        ' Insert new row
        Rows(originalRow + 1).Insert Shift:=xlDown
        
        ' Populate new row
        Cells(originalRow + 1, 1).Value = False
        Cells(originalRow + 1, 2).Value = originalNumber + 1
        Cells(originalRow + 1, 3).Value = originalID
        Cells(originalRow + 1, 4).Value = Format(newDate, "dd/mm/yy")
        Cells(originalRow + 1, 5).Value = originalName
        Cells(originalRow + 1, 6).Value = frequency
    End If
End Sub

3. Save the workbook as a .xlsm (macro-enabled) file and test the checkbox trigger.

Option B: Power Automate (No Code)

If you prefer no-code, set up a Power Automate flow that monitors changes to your Excel table. The flow can detect when a checkbox is ticked, validate the frequency type, calculate the new date, and insert the new row automatically.

Key Notes #

• Ensure your date column is formatted as a date type in your tool—otherwise, date calculations will fail.
• For custom internal systems, replicate this logic using your backend language (like Python or JavaScript) by listening for checkbox change events and executing the row creation logic.

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