Google Apps Script表格：获取高亮区域并写入自定义函数数据

Got it, let's fix this step by step so your script can write data from myFunc into whatever cells the user has highlighted—whether it's a single range or multiple ones. Here's what we need to adjust and why:

Key Issues in Your Original Code #

Right now, your script hardcodes the range to B1:B10 instead of using the user's selected area, and it doesn't properly handle multiple highlighted ranges (since RangeList needs to be converted into individual Range objects to work with each selection).

Modified Working Code #

function writeToHighlightedCells() {
  const ui = SpreadsheetApp.getUi();
  const inputPrompt = ui.prompt("Enter a number");
  const text = inputPrompt.getResponseText();
  
  // Validate input is a valid number
  const inputNum = parseInt(text);
  if (isNaN(inputNum)) {
    ui.alert('Input is not a valid number');
    return;
  }
  
  const sheet = SpreadsheetApp.getActiveSheet();
  const activeRangeList = sheet.getActiveRangeList();
  
  // Check if any cells are selected
  const selectedRanges = activeRangeList.getRanges();
  if (selectedRanges.length === 0) {
    ui.alert('No range selected');
    return;
  }
  
  // Loop through each highlighted range
  selectedRanges.forEach(range => {
    const numRows = range.getNumRows();
    // Get your custom array from myFunc
    const resultArray = myFunc(numRows, inputNum);
    
    // Convert 1D array to 2D format (required for Google Sheets' setValues)
    const twoDArray = resultArray.map(item => [item]);
    
    // Double-check array length matches the selected range's row count
    if (twoDArray.length !== numRows) {
      ui.alert(`Oops! myFunc returned ${resultArray.length} items, but your selected range has ${numRows} rows. Please check the function.`);
      return;
    }
    
    // Write the data to the highlighted cells
    range.setValues(twoDArray);
  });
}

// Replace this with your actual myFunc implementation
function myFunc(numRows, inputNum) {
  const output = [];
  for (let i = 1; i <= numRows; i++) {
    output.push(i + inputNum); // Example logic: adjust this to your needs
  }
  return output;
}

What Each Part Does #

• Handling Multiple Selections: activeRangeList.getRanges() turns the highlighted areas into an array of individual Range objects, so we can loop through each one and apply the function separately.
• Row Count Capture: range.getNumRows() grabs the number of rows in each selected range—this is what we pass to myFunc to generate an array of the exact right length.
• 2D Array Conversion: Google Sheets requires a 2D array for setValues() (each inner array represents a single row). The map(item => [item]) line converts your 1D array (like [1,2,3]) into the required format ([[1],[2],[3]]).
• Validation Checks: We added checks to ensure the input is a number, cells are selected, and the array from myFunc matches the selected range's row count (to avoid unexpected errors).

Quick Notes #

• If your myFunc already returns a 2D array (e.g., [[10],[20],[30]]), you can skip the map step and use resultArray directly in range.setValues().
• If users might select multi-column ranges and you want to fill all columns with the same data, you can modify the 2D array to repeat values across columns (e.g., twoDArray.map(row => Array(range.getNumColumns()).fill(row[0]).map(x => [x]))).

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