Google Apps Script新手问题：如何获取数组(i,j)位置的值及代码异常排查

Hey there! Let's break down what's going wrong with your code and how to properly access values in a 2D array from Google Sheets.

First, Let's Diagnose the Issue in Your Code #

Your final output has all values in the second column set to 3 because of two critical mistakes:

• Repeatedly overwriting the entire range: Inside your inner loop, you use sheet.getRange(cells.getRow(),2,rowCount,2).setValue(inputt) every iteration. This sets the entire second column range to the current inputt value. By the time your loop finishes, the last inputt value is 3 (from the 6th row of your initial data), so every cell in that range gets overwritten to 3.
• Redundant inner loop: That for (var i =1;i <=3;++i) loop serves no useful purpose here—it just runs 3 times and repeats the same overwrite, amplifying the problem.

How to Access Values at (i,j) in a Google Apps Script Array #

When you use getValues() on a range, it returns a 2D array where:

• The first index is the row position (starts at 0, matching the first row of your selected range)
• The second index is the column position (starts at 0, matching the first column of your selected range)

For example, using your initial data:

• To get the value from the 1st row, 1st column (which is 1): addresses[0][0]
• To get the value from the 4th row, 2nd column (which is 2): addresses[3][1]

Fixed Code Example #

Assuming your goal is to work with row-by-row values (instead of overwriting the entire column), here's a corrected version. This example lets you read and modify values without breaking your data:

function learn() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cells = sheet.getActiveRange();
  var rowCount = cells.getNumRows();
  // Fetch all values as a 2D array (rows x columns)
  var addresses = cells.getValues();

  // Loop through each row (using 0-based index for the array)
  for (var rowIndex = 0; rowIndex < rowCount; rowIndex++) {
    // Get value from first column of the current row
    var firstColVal = addresses[rowIndex][0];
    // Get value from second column of the current row
    var secondColVal = addresses[rowIndex][1];
    
    Logger.log("Row " + (rowIndex + 1) + ": First column = " + firstColVal + ", Second column = " + secondColVal);

    // If you want to update the second column for THIS ROW ONLY (example):
    // sheet.getRange(cells.getRow() + rowIndex, 2).setValue(firstColVal + "-updated");
  }
}

Key Fixes in This Code: #

• Uses the 2D addresses array directly to access values, which is faster and avoids repeated range calls.
• Loops with 0-based indexing, matching how getValues() returns data.
• Updates individual cells instead of the entire range when needed (commented out example).
• Removes the redundant inner loop that was causing unnecessary overwrites.

If Your Goal Was Something Else... #

If you had a specific transformation or outcome in mind (like modifying data in a different way), feel free to clarify, but this fixes the immediate issue of overwriting your column and shows you how to properly access array values.

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