Google Script动态列拼接问题求助：转换Excel VBA宏至脚本

Let's break down why your current script isn't working and fix it to match the behavior of your Excel VBA macro:

Key Issues in the Original Script #

1. Undefined Prefix in Results:
   You're initializing a[row][0] without setting a starting value, so when you first concatenate, it starts with undefined (since uninitialized array elements default to this value).
2. Incorrect Row Indexing:
   Google Sheets uses 0-based indexing for the getValues() array, but your loop starts at row=2 (skipping the first data row) and uses row < sLR which misses the last row entirely.
3. No Data Written to Sheet:
   You fetch the target range with sSheet.getRange(1,sLC + 1,sLR,1); but never actually write your array a to it using setValues().
4. Column Index Mismatch:
   Your sArr contains Excel-style 1-based column numbers, but sV uses 0-based indices—you need to subtract 1 from each column number in sArr to access the correct cell value.

Fixed Google Apps Script #

function TemplateA_n() { 
  Template_A("A", [2, 4, 6]) 
}

function Template_A(SshtName, sArr){
  const sSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SshtName);
  const sV = sSheet.getDataRange().getValues();
  const sLC = sSheet.getLastColumn();
  const sLR = sSheet.getLastRow();
  
  // Initialize result array with header row
  const resultArr = Array(sLR).fill().map(() => [""]);
  resultArr[0][0] = "Concat"; // Match the header from your VBA macro
  
  // Loop through data rows (skip header, start at 0-based index 1)
  for (let row = 1; row < sLR; row++) {
    let concatStr = "";
    // Process each column in the specified order
    for (const colNum of sArr) {
      // Convert 1-based column number to 0-based array index
      const cellValue = sV[row][colNum - 1];
      if (cellValue !== "") {
        // Add separator only if we already have content to avoid leading pipes
        concatStr = concatStr ? `${concatStr}|${cellValue}` : cellValue;
      }
    }
    resultArr[row][0] = concatStr;
  }
  
  // Write the final concatenated values to the next empty column
  sSheet.getRange(1, sLC + 1, sLR, 1).setValues(resultArr);
}

How This Matches Your VBA Macro #

• Header Handling: We set the first row of the result array to "Concat", just like your VBA code does.
• Row Loop: Starts at the second row (0-based index 1) to skip the header, matching the i = 2 To .Rows.Count logic in VBA.
• Column Order: Respects your sArr column sequence, converting 1-based Excel column numbers to 0-based array indices correctly.
• Separator Logic: Only adds the | separator when there's existing content, preventing empty leading or trailing pipes.
• Sheet Write: Uses setValues() to push the entire result array to the next empty column (last column + 1) in one go, which is efficient for large datasets.

Quick Testing Tips #

• Run TemplateA_n() directly from the script editor, or call Template_A("YourSheetName", [col1, col2, ...]) with your target sheet name and desired column numbers.
• Add Logger.log(concatStr) inside the row loop if you need to debug individual row outputs.

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