Google Apps Script语法错误排查求助：表单数据分类脚本报错

Hey Marty, I know those syntax errors can be frustrating—especially when you're adapting code from different sources and relying on voice tools to review. Let's break down the issues in your script and fix them step by step:

Key Errors Causing the Syntax Issue #

Here are the main problems triggering the "missing ;" message and other hidden bugs:

• Case-sensitive variable mistakes: JavaScript cares about capitalization. You used Var instead of var, SourceSheet instead of sourceSheet, Else instead of else, and Default instead of default—all of these break basic syntax rules.
• Incorrect array access: courseChoices(x) should be courseChoices[x] (square brackets are for array indices, not parentheses).
• Broken case statement syntax: Each case line needs to end with a colon : instead of a semicolon ;.
• Wrong loop condition: Your loop for multiple course choices used thisSourceRowValue[7].indexOf(",") as the upper limit—this only gets the first comma's position. You need to loop through the full length of the split courseChoices array instead.
• Comparison vs assignment mix-up: thisTargetRow[7] == courseName uses a comparison operator (==) when you mean to assign a value—use a single = instead.
• Undefined variables: sourceValues should be sourceRowValues (you defined this earlier), rowIndex wasn't declared (it should be i+1 since sheet rows start at 1), and targetRow should be thisTargetRow.
• Invalid sheet reference: When creating a new sheet, you tried to call Target.getRange("A1") but Target is a string—you need to grab the actual sheet object first with ss.getSheetByName(Target).
• Missing curly braces: The for loop for course choices didn't have wrapping braces, so it only executed the switch once instead of for each choice.
• Unimplemented helper function: The copyColumnNumbers function wasn't included in your code— I've added a working implementation below.

Fixed Script #

Here's the fully corrected version of your script, with clear comments to explain changes (optimized for voice tool readability):

function change(e) { 
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sourceSheet = ss.getSheetByName('Form Responses 1');
  var sourceRows = sourceSheet.getDataRange(); 
  var numSourceRows = sourceRows.getNumRows(); 
  var sourceRowValues = sourceRows.getValues(); 
  var rowsTransferred = 0; 

  for (var i = 0; i <= numSourceRows - 1; i++) { 
    var thisSourceRowValue = sourceRowValues[i]; // Fixed variable name (sourceRowValues instead of sourceValues)
    var courseChoices;
    var Target;
    var courseName;

    // Check if course is multiple choice (column 8, array index 7)
    if (thisSourceRowValue[7].indexOf(",") < 0) { 
      // Single course choice: wrap in array to reuse loop logic
      courseChoices = [thisSourceRowValue[7]]; 
    } else {
      // Multiple course choices: split into clean array
      courseChoices = thisSourceRowValue[7].split(",").map(choice => choice.trim()); 
    }

    // Loop through each course choice
    for(var x = 0; x < courseChoices.length; x++) { 
      var currentChoice = courseChoices[x];
      
      switch(true) { 
        case currentChoice.indexOf("Dual") > -1 || currentChoice.indexOf("Childhood") > -1: 
          Target = "Dual Diploma"; 
          courseName = "Dual Diploma in Early Childhood Education and Care"; 
          break; 
        case currentChoice.indexOf("Sector") > -1: 
          Target = "Adv Sector"; 
          courseName = "Advanced Diploma in Community Sector Management"; 
          break; 
        case currentChoice.indexOf("Adv") > -1: 
          Target = "Adv Management"; 
          courseName = "Advanced Diploma in Leadership and Management"; 
          break; 
        case currentChoice.indexOf("Business") > -1: 
          Target = "CertIII Business"; 
          courseName = "Certificate III in Business"; // Fixed typo: Certificcate → Certificate
          break; 
        default: // Fixed Default → default
          Target = "Other"; 
          courseName = currentChoice; 
      }
    }

    // Check if target sheet exists; create if not
    if (!ss.getSheetByName(Target)) { 
      var targetSheet = ss.insertSheet(Target); 
      // Copy and reorder headers from source sheet
      var sourceHeaders = sourceSheet.getRange("A1:N1").getValues()[0];
      var keep_columns = [0,1,2,3,4,5,10,9,8,11,6,7,12];
      var reorderedHeaders = keep_columns.map(index => sourceHeaders[index]);
      targetSheet.getRange(1, 1, 1, reorderedHeaders.length).setValues([reorderedHeaders]);
    } else {
      var targetSheet = ss.getSheetByName(Target);
    }

    var colNumber = sourceSheet.getLastColumn(); 
    var targetRowIndex = targetSheet.getLastRow() + 1;
    var sourceRange = sourceSheet.getRange(i+1, 1, 1, colNumber); // Fixed row index to i+1 (sheet rows start at 1)
    var sourceRowData = sourceRange.getValues()[0];

    // Reorder source columns to match target sheet layout
    var keep_columns = [0,1,2,3,4,5,10,9,8,11,6,7,12];
    var reorderedRowData = keep_columns.map(index => sourceRowData[index]);

    // Check if record already exists in target sheet
    var targetRows = targetSheet.getDataRange().getValues();
    var recordExists = false;

    for (var y = 0; y < targetRows.length; y++) { 
      var thisTargetRow = targetRows[y]; 
      // Match by first name, last name, email (adjust indices if your columns differ)
      if (thisTargetRow[2] == sourceRowData[2] && thisTargetRow[3] == sourceRowData[3] && thisTargetRow[5] == sourceRowData[5]) { 
        // Update existing record's course name
        targetSheet.getRange(y+1, 8).setValue(courseName); // Column 8 = sheet column H (array index 7)
        recordExists = true;
        break; 
      }
    }

    // If record doesn't exist, add new row
    if (!recordExists) {
      targetSheet.getRange(targetRowIndex, 1, 1, reorderedRowData.length).setValues([reorderedRowData]);
      // Set correct course name in the new row
      targetSheet.getRange(targetRowIndex, 8).setValue(courseName);
    }
    rowsTransferred++;
  }
}

// Helper function to copy and reorder columns (replaces original copyColumnNumbers)
function copyColumnNumbers(sourceRange, targetRange, columnIndices) {
  var sourceValues = sourceRange.getValues();
  var targetValues = [];
  
  sourceValues.forEach(row => {
    var newRow = columnIndices.map(index => row[index]);
    targetValues.push(newRow);
  });
  
  targetRange.offset(0, 0, targetValues.length, targetValues[0].length).setValues(targetValues);
}

Quick Accessibility Notes #

• I've simplified repetitive logic and added descriptive comments to help your voice tool parse the code clearly.
• Fixed typos that could have caused confusion during voice reading.
• Standardized variable names to follow consistent casing rules, making the script easier to follow.

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