如何在Google Sheets中实现二维数组读写与文本搜索？

需求背景 #

需要将Google Sheets表格数据加载到二维数组，实现数组的读写操作，并搜索指定文本记录其所在行，类似以下VBA实现的逻辑：

参考VBA代码 #

Dim stArray(11, 1650) As String
Dim iRowLast As Integer: iRowLast = 1650
Dim iColLast As Integer: iColLast = 10
Dim wsRaw As Worksheet, wsMap As Worksheet

Set wsMap = Sheets("Map")
Set wsRaw = Sheets("Raw")

' 从表格读取数据到数组
For xRow = 0 To iRowLast
    For yCol = 0 To iColLast
        stArray(xRow, yCol) = wsRaw.Cells(xRow + 1, yCol + 1).Value
    Next yCol
Next xRow

' 将数组数据写入工作表
For xRow = 0 To iRowLast
    For yCol = 0 To iColLast
        wsMap.Cells(xRow + 1, yCol + 1).Value = stArray(xRow, yCol)
    Next yCol
Next xRow

Google Apps Script 实现方案 #

Google Apps Script支持嵌套数组（等价于二维数组），且有更高效的API直接处理表格数据，无需手动循环创建数组。

1. 读取表格数据到二维数组 #

方式一：直接用API快速获取（推荐）

getValues()方法会直接返回一个二维数组，数组索引对应表格的行和列（注意：数组索引从0开始，表格行/列从1开始）：

function readDataToArray() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const wsRaw = ss.getSheetByName("Raw");
  
  // 获取数据范围：这里假设数据从A1开始，取所有有数据的单元格
  const dataRange = wsRaw.getDataRange();
  // 直接得到二维数组
  const stArray = dataRange.getValues();
  
  console.log(stArray);
  return stArray;
}

方式二：手动创建二维数组并填充

如果需要手动控制范围，可以用你提供的Create2DArray函数，再循环填充数据：

function Create2DArray(rows) {
  var arr = [];
  for (var i = 0; i < rows; i++) {
    arr[i] = [];
  }
  return arr;
}

function manualReadToArray() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const wsRaw = ss.getSheetByName("Raw");
  
  const iRowLast = 1650;
  const iColLast = 10;
  
  // 创建二维数组
  const stArray = Create2DArray(iRowLast + 1);
  
  // 循环填充数据
  for (let xRow = 0; xRow <= iRowLast; xRow++) {
    for (let yCol = 0; yCol <= iColLast; yCol++) {
      stArray[xRow][yCol] = wsRaw.getRange(xRow + 1, yCol + 1).getValue();
    }
  }
  
  console.log(stArray);
  return stArray;
}

2. 将二维数组写入工作表 #

推荐用setValues()方法批量写入，比循环单个单元格高效得多：

function writeArrayToSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const wsMap = ss.getSheetByName("Map");
  const stArray = readDataToArray();
  
  // 确定写入范围：行数=数组长度，列数=数组第一行的长度
  const outputRange = wsMap.getRange(1, 1, stArray.length, stArray[0].length);
  // 一次性写入数组
  outputRange.setValues(stArray);
}

3. 搜索数组中的文本并记录行号 #

遍历二维数组，找到匹配文本后记录对应的表格行号（数组索引+1）：

function searchTextInArray(targetText) {
  const stArray = readDataToArray();
  const matchedRows = [];
  
  // 遍历数组每一行
  stArray.forEach((row, index) => {
    if (row.includes(targetText)) {
      // 数组索引对应表格行号为index+1
      matchedRows.push(index + 1);
    }
  });
  
  console.log(`匹配到的行号：${matchedRows.join(", ")}`);
  return matchedRows;
}

// 调用示例：searchTextInArray("你要搜索的文本");

注意事项 #

• Google Apps Script中二维数组为嵌套格式：arr[rowIndex][colIndex]，与VBA逻辑一致但语法不同。
• 批量API（getValues()/setValues()）在处理大数据量时性能远优于单个单元格读写，优先使用。
• 数组索引从0开始，表格行/列从1开始，注意转换对应关系。

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