请求协助优化Google Sheets脚本：插入IMPORTHTML公式并避免数据重复

Hey there! I get that you’re juggling building a Glide app for your swimming club while not being a full-time dev—let’s get these two script issues sorted so you can focus on the fun parts of the app. Here’s how to fix both problems:

1. Prevent Duplicate Rows in Member Sheets #

The core issue with duplicates is that your current script appends the member’s row every time it runs, no matter if their data already exists in their sheet. We’ll add a check using the unique profile link (more reliable than a name, since names can repeat) to verify if the entry is already present before adding it.

2. Add IMPORTHTML Formula to Column C #

When creating a new member sheet or adding a new entry, we’ll insert the IMPORTHTML formula directly into column C, linked to the profile link in column B of the same row.

Modified Script Code #

function createNewSheets() { 
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); 
  var masterSheet = spreadsheet.getSheetByName('Liste de nageurs'); 
  var firstRow = 1; 
  var firstCol = 1; 
  var numRows = masterSheet.getLastRow(); 
  var numCols = masterSheet.getLastColumn(); 
  var data_range = masterSheet.getRange(firstRow, firstCol, numRows, numCols).getValues(); 

  // Iterate through all rows (skip header row starting at i=1)
  for (var i = 1; i < data_range.length; i++) { 
    var currentRow = data_range[i]; 
    var swimmerName = currentRow[0]; 
    var profileLink = currentRow[1]; // Assumes profile link is in column B (index 1)
    
    // Get or create the member's sheet
    var sheet = spreadsheet.getSheetByName(swimmerName); 
    if (!sheet) { 
      sheet = spreadsheet.insertSheet(swimmerName);
      // Optional: Add matching header row from master sheet (remove if not needed)
      sheet.appendRow(data_range[0]);
    }

    // Check if the profile link already exists to avoid duplicates
    var lastRow = sheet.getLastRow();
    var existingLinks = lastRow > 1 ? sheet.getRange(2, 2, lastRow - 1, 1).getValues().flat() : [];
    var isDuplicate = existingLinks.includes(profileLink);

    if (!isDuplicate) {
      // Append the new row to the member's sheet
      var newRowIndex = sheet.getLastRow() + 1;
      sheet.appendRow(currentRow);

      // Insert IMPORTHTML formula in column C of the new row
      // Adjust "table", 1 if you need to target a different table on the profile page
      var importFormula = `=IMPORTHTML("${profileLink}", "table", 1)`;
      sheet.getRange(newRowIndex, 3).setFormula(importFormula);
    }
  }
}

Key Changes Explained #

• Duplicate Prevention: We pull all existing profile links from column B of the member’s sheet (skipping the header row) and check if the current row’s link is already present. Only if it’s new do we add the row—using the link ensures we avoid duplicates even if two members share the same name.
• IMPORTHTML Formula: Right after appending a new row, we calculate its index and set the formula in column C. The formula uses the profile link from column B of the same row; tweak the "table", 1 part if you need to target a specific table on the profile page (change the number to match the table’s position).
• Optional Header Row: I added a line to copy the master sheet’s header row when creating a new member sheet—this keeps column labels consistent. Feel free to remove this line if you don’t want headers in individual member sheets.

Quick Notes #

• Ensure the profile links in your master sheet are valid URLs that contain a table (otherwise IMPORTHTML will return an error).
• You can re-run this script anytime without worrying about duplicates or redundant formulas—our check takes care of that.

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