使用&&的While循环报错：Missing ; before statement问题求助

Let's break down the issue and fix your script step by step:

1. Direct Cause of the Syntax Error #

The immediate problem is case sensitivity in JavaScript: you used While (capital W) instead of the correct lowercase while. JavaScript reserves lowercase while for loop statements, so the capitalized version throws a syntax error.

2. Optimized Fixed Code #

Beyond fixing the syntax, your script has a performance issue: repeatedly calling getRange() and getValue() inside a loop is slow (each call makes a round-trip to Google's servers). Here's a revised version that reads all data at once and processes it efficiently:

function ranking1() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var artistSheet = ss.getSheetByName("Artist");
  var historySheet = ss.getSheetByName("Artist History");
  
  // Read all relevant data from both sheets in one go
  var artistData = artistSheet.getRange(8, 1, artistSheet.getLastRow() - 7, 9).getValues(); // Rows 8+, cols A-I
  var historyData = historySheet.getRange(2, 1, historySheet.getLastRow() - 1, 9).getValues(); // Rows 2+, cols A-I
  
  var n = 0; // Index for artistData (starts at row 8 = index 0)
  var yIndex = 0; // Index for historyData (starts at row 2 = index 0)
  var zOffset = 0; // For z = 8 - zOffset (since z decreases by 1 each loop)
  
  // Loop through data arrays
  while (n < artistData.length && yIndex < historyData.length) {
    var artist1 = artistData[n][3]; // Column D (index 3)
    var artist2 = artistData[n][0]; // Column A (index 0)
    var artist3 = artistData[n][8 - zOffset]; // Column I, starting at row 8 (index 8 of row, minus zOffset)
    
    var history1 = historyData[yIndex][0]; // Column A
    var history2 = historyData[yIndex][8]; // Column I
    var history3 = historyData[yIndex][5]; // Column F
    
    // Check all match conditions
    if (artist1 == history1 && artist2 == history2 && artist3 == history3) {
      // Set value in column B (index 1) of the current artist row
      artistSheet.getRange(8 + n, 2).setValue(artist3);
    }
    
    // Increment counters
    n++;
    yIndex++;
    zOffset++;
  }
}

3. Key Improvements & Explanations #

• Fixed syntax error: Replaced While with while.
• Batch data reading: Read entire ranges into 2D arrays once, instead of calling getRange() in every loop iteration—this drastically improves script speed.
• Clearer variable names: Renamed variables like Artist to artistSheet for better readability.
• Array index access: Used array indices (e.g., artistData[n][3] for column D) to access values directly from the pre-read data.
• Bounds checking: Added checks to ensure we don't go beyond the length of either data array, preventing out-of-bounds errors.

4. Original Syntax Fix Only (If You Prefer Minimal Changes) #

If you just want to fix the original error without full optimization, here's the minimal tweak:

function ranking1() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var Artist = ss.getSheetByName("Artist"); 
  var Historical = ss.getSheetByName("Artist History"); 
  var x = 8, y = 2, z = 8, n = 1; 
  var dList = Artist.getRange("Artist!D8:D").getValues(); 
  var dLast = dList.filter(String).length; 
  var Artist1 = Artist.getRange('Artist!D'+x).getValue(); 
  var Artist2 = Artist.getRange('Artist!A'+x).getValue(); 
  var Artist3 = Artist.getRange('Artist!I'+z).getValue(); 
  var History1 = Historical.getRange('Artist History!A'+y).getValue(); 
  var History2 = Historical.getRange('Artist History!I'+y).getValue(); 
  var History3 = Historical.getRange('Artist History!F'+y).getValue(); 
  Logger.log(Artist1); 
  Logger.log(Artist2); 
  Logger.log(Artist3); 
  Logger.log(History1); 
  Logger.log(History2); 
  Logger.log(History3); 
  // Fixed: lowercase while instead of While
  while ((Artist1 == History1) && (Artist2 == History2) && (Artist3 == History3) && dLast>=n) { 
    Artist.getRange('B'+x).setValue(Artist3); 
    n++; 
    x++; 
    y++; 
    z--; 
    // Update values for next loop iteration (original code missed this!)
    Artist1 = Artist.getRange('Artist!D'+x).getValue(); 
    Artist2 = Artist.getRange('Artist!A'+x).getValue(); 
    Artist3 = Artist.getRange('Artist!I'+z).getValue(); 
    History1 = Historical.getRange('Artist History!A'+y).getValue(); 
    History2 = Historical.getRange('Artist History!I'+y).getValue(); 
    History3 = Historical.getRange('Artist History!F'+y).getValue(); 
  } 
}

Note: The original code also missed re-fetching the Artist1, History1, etc., values inside the loop—so it would have only checked the first row's values repeatedly. The above fixes that too.

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