Hey there! Let's break down what's causing the "SetValue is not defined" error and get your script working properly.

Core Issues in Your Code #

The error stems from two key syntax mistakes, plus a critical limitation of Google Sheets custom functions you should know about:

1. Incorrect Operator: Comma Instead of Dot
   Your code uses commas , where you should use dot operators . to chain methods on Range objects. For example:

   // ❌ Wrong: Comma breaks the method chain, making the script look for a variable named "setValue"
   sheet.getActiveCell(),setValue(45)
   

   You need to use a dot to connect the Range object returned by getActiveCell() to its setValue() method:

   // ✅ Correct: Dot links the object to its method
   sheet.getActiveCell().setValue(45)
   

2. Casing Consistency: setValue is CamelCase
   While the immediate error comes from the comma issue, it's worth noting that Google Apps Script uses camelCase for method names. setValue() (lowercase "s") is the valid method—SetValue() (uppercase "S") would indeed be undefined if you tried to use it.

3. Custom Function Limitation: Can't Modify Other Cells
   Even with fixed syntax, if you try to run this as a cell-invoked custom function (e.g., typing =SetRange(5) in a cell), it will fail. Google Sheets custom functions are designed to return a value to the cell they're called from—they can't directly modify other cells in the spreadsheet.

Corrected Script Options #

Option 1: Fixed Syntax (For Learning Purposes) #

If you just want to fix the syntax errors to understand method chaining, here's the corrected code—though it won't work as a cell-invoked custom function:

function SetRange(RowNum) {
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  // Fixed method chaining with dots
  sheet.getActiveCell().setValue(45);
  sheet.getRange(RowNum, 24).setValue(51);
  
  // Custom functions require a return value
  return "Values attempted to set!";
}

Option 2: Menu-Triggered Script (Recommended for Modifying Cells) #

To actually modify other cells as intended, use a menu-triggered script instead. This adds a custom menu to your spreadsheet, letting you run the function manually:

// Runs when the spreadsheet opens, adds a custom menu
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Actions')
    .addItem('Set Target Values', 'setRangeValues')
    .addToUi();
}

// The function that sets the values
function setRangeValues() {
  const ui = SpreadsheetApp.getUi();
  // Prompt the user for a row number
  const response = ui.prompt('Enter the target row number:', ui.ButtonSet.OK_CANCEL);
  
  if (response.getSelectedButton() === ui.Button.OK) {
    const rowNum = parseInt(response.getResponseText());
    if (!isNaN(rowNum)) {
      const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      // Set values as intended
      sheet.getActiveCell().setValue(45);
      sheet.getRange(rowNum, 24).setValue(51);
      ui.alert('Values set successfully!');
    } else {
      ui.alert('Please enter a valid number for the row!');
    }
  }
}

How to Use the Menu Script: #

1. Paste this code into your script editor (Extensions > Apps Script)
2. Save the project and refresh your spreadsheet
3. You'll see a new "Custom Actions" menu at the top—click it and select "Set Target Values"
4. Enter a row number when prompted, and the script will set the values as expected

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