Google Sheets脚本修改求助：将隐藏非适用列逻辑替换为删除列逻辑

Got it, let's get this sorted for you! The main snag when using deleteColumn() directly is that deleting columns left-to-right shifts the indices of remaining columns, leading to missing deletions or accidentally removing the wrong columns. We’ll fix this by adjusting the logic to delete columns right-to-left and tweak the validation flow accordingly.

Here’s the modified complete script with the delete column functionality working correctly:

function loadObjectsAndCreateProductDropDown() { 
  const ss = SpreadsheetApp.getActive(); 
  const sh = ss.getSheetByName('Sheet0'); 
  const psh = ss.getSheetByName('Sheet1'); 
  const [ph, ...prds] = sh.getRange(1, 1, 10, 6).getValues().filter(r => r[0]); 
  const [ch, ...chcs] = sh.getRange(11, 1, 10, 10).getValues().filter(r => r.join()); 
  let pidx = {}; 
  ph.forEach((h, i) => { pidx[h] = i }); 
  let prd = { pA: [] }; 
  prds.forEach(r => { 
    if (!prd.hasOwnProperty(r[0])) { 
      prd[r[0]] = { 
        type: r[pidx['Type']], 
        size: r[pidx['Size']], 
        color: r[pidx['Color']], 
        material: r[pidx['Material']], 
        length: r[pidx['Length']] 
      }; 
      prd.pA.push(r[0]); 
    } 
  }); 
  let cidx = {}; 
  let chc = {}; 
  ch.forEach((h, i) => { cidx[h] = i; chc[h] = [] }); 
  chcs.forEach(r => { 
    r.forEach((c, i) => { 
      if (c && c.length > 0) chc[ch[i]].push(c) 
    }) 
  }) 
  const ps = PropertiesService.getScriptProperties(); 
  ps.setProperty('product_matrix', JSON.stringify(prd)); 
  ps.setProperty('product_choices', JSON.stringify(chc)); 
  Logger.log(ps.getProperty('product_matrix')); 
  Logger.log(ps.getProperty('product_choices')); 
  psh.getRange('A2').setDataValidation(SpreadsheetApp.newDataValidation().requireValueInList(prd.pA).build()); 
}

// Use this with an INSTALLABLE onEdit trigger (critical for delete permissions!)
function onMyEdit(e) { 
  const sh = e.range.getSheet(); 
  if (sh.getName() == 'Sheet1' && e.range.columnStart == 1 && e.range.rowStart == 2 && e.value) { 
    // Clear existing validations first
    sh.getRange('C2:G2').clearDataValidations(); 
    
    let ps = PropertiesService.getScriptProperties(); 
    let prodObj = JSON.parse(ps.getProperty('product_matrix'));
    let choiObj = JSON.parse(ps.getProperty('product_choices')); 
    let hA = sh.getRange(1, 1, 1, sh.getLastColumn()).getDisplayValues().flat(); 
    let col = {}; 
    hA.forEach((h, i) => { col[h.toLowerCase()] = i + 1 }); 

    // Step 1: Collect columns to delete (don't delete yet!)
    const colsToDelete = [];
    ["type", "size", "color", "material", "length"].forEach(c => { 
      const colIndex = col[c.toLowerCase()];
      if (choiObj[prodObj[e.value][c]]) { 
        // Set validation for applicable columns
        sh.getRange(e.range.rowStart, colIndex)
          .setDataValidation(SpreadsheetApp.newDataValidation().requireValueInList(choiObj[prodObj[e.value][c]]).build())
          .offset(-1,0).setFontColor('#000000'); 
      } else { 
        // Mark non-applicable columns for deletion
        colsToDelete.push(colIndex);
      } 
    }); 

    // Step 2: Delete columns RIGHT-TO-LEFT (sort indices descending to avoid index shifts)
    colsToDelete.sort((a, b) => b - a).forEach(colNum => {
      sh.deleteColumn(colNum);
    });
  } 
}

Key Changes Explained: #

1. Collect Columns First: Instead of deleting columns immediately, we first gather all the indices of non-applicable columns into an array. This prevents index shifting issues mid-operation.
2. Right-to-Left Deletion: We sort the colsToDelete array in descending order (largest index first). When you delete a column on the right, it doesn’t affect the indices of columns to the left—so we avoid accidentally skipping or deleting the wrong columns.
3. Permissions Note: Since deleteColumn() requires edit permissions that the simple onEdit trigger doesn’t have, you must use an installable onEdit trigger bound to the onMyEdit function. You can set this up in the script editor under Edit > Current project's triggers.

Important Tips: #

• Always back up your sheet before testing this script—deleting columns is permanent!
• Double-check that your header names in Sheet1 match the strings in the array ("type", "size", "color", "material", "length"), case-insensitive (we convert headers to lowercase for mapping).

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