如何编写Google Sheets宏实现源矩阵复选框匹配值跨表复制？

Got it, let's tackle this Google Sheets macro problem for you! You're trying to copy the row and column labels from checked checkboxes in your source sheet to the destination sheet, right? Let's refine your existing code and break down how it works step by step.

First, let's make some clear assumptions about your sheet structure (you can tweak these to match your actual setup):

Sheet Structure Assumptions

• Source sheet ("Cruces Activo-Amenazas"):
  • Column A (rows 2 to 30): Holds your "Amenazas" (since you set maxAmenazas = 29)
  • Row 1 (columns B onwards): Holds your "Activos"
  • Checkboxes live in the range B2:Z30 (adjust this if your checkbox area is different)
• Destination sheet ("Análisis de Riesgos"):
  • We'll write matched pairs starting from row 2 (assuming row 1 is your header), with "Amenaza" in column B and "Activo" in column C (adjust these columns to match your static columns)

Refined Macro Code #

Here's the completed code with detailed comments to explain each step:

function CalcularCruces() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sourceSheet = spreadsheet.getSheetByName("Cruces Activo-Amenazas");
  var destinationSheet = spreadsheet.getSheetByName("Análisis de Riesgos");
  
  // Total number of Amenazas rows (matches your original variable)
  const maxAmenazas = 29;
  // Define the range containing all checkboxes (adjust start column/end column as needed)
  const checkboxRange = sourceSheet.getRange(2, 2, maxAmenazas, sourceSheet.getLastColumn() - 1);
  // Pull all checkbox states into a 2D array (faster than checking each cell individually)
  const checkboxValues = checkboxRange.getValues();
  
  // Get the full list of Amenazas from column A, flattened into a simple array
  const amenazasList = sourceSheet.getRange(2, 1, maxAmenazas, 1).getValues().flat();
  // Get the full list of Activos from row 1, flattened into a simple array
  const activosList = sourceSheet.getRange(1, 2, 1, sourceSheet.getLastColumn() - 1).getValues().flat();
  
  // Array to store all matched Amenaza-Activo pairs
  const matchedPairs = [];
  
  // Loop through each Amenaza row
  for(var i = 0; i < maxAmenazas; i++) {
    // Loop through each Activo column in the current row
    for(var j = 0; j < checkboxValues[i].length; j++) {
      // Check if the checkbox is checked
      if(checkboxValues[i][j] === true) {
        // Add the corresponding Amenaza and Activo to our pairs array
        matchedPairs.push([amenazasList[i], activosList[j]]);
      }
    }
  }
  
  // Clear existing data in the destination range (adjust columns B:C to match your target)
  destinationSheet.getRange(2, 2, destinationSheet.getLastRow() - 1, 2).clearContent();
  
  // Write all matched pairs to the destination sheet in one go (efficient batch operation)
  if(matchedPairs.length > 0) {
    destinationSheet.getRange(2, 2, matchedPairs.length, 2).setValues(matchedPairs);
  }
}

Key Code Explanations #

• Batch Data Retrieval: Using getValues() to pull all checkbox states and labels at once is way faster than accessing individual cells in loops—critical for keeping your macro snappy.
• Matching Logic: Nested loops check every checkbox; when we find a checked one, we pair its row's Amenaza with its column's Activo.
• Clean Destination: We clear old entries first to avoid duplicates, then write all new pairs in a single batch (another efficiency win over writing row-by-row).

Input/Output Example #

Input (Source Sheet: "Cruces Activo-Amenazas")

Output (Destination Sheet: "Análisis de Riesgos")

Quick Adjustments for Your Setup #

• Checkbox Range: If your checkboxes start at a different row/column, modify the getRange parameters: getRange(startRow, startColumn, numRows, numColumns).
• Target Columns: Change the column numbers in getRange(2, 2, ...)—the second 2 is the starting column (B=2, C=3, etc.).
• Keep Old Data: Remove the clearContent() line if you want to append new pairs below existing entries instead of replacing them.

内容的提问来源于stack exchange，提问作者Javier Alonso Delgado