使用Apps Script重新格式化Google Sheets表格为Position-Value结构

你之前的代码仅复制指定行数据，完全没实现“单元格位置+对应值”的转置需求，下面是能达成目标的完整Apps Script方案：

完整代码 #

function convertToPositionValue() {
  // 替换为你的源工作表和目标工作表名称
  const sourceSheetName = "FoodMap";
  const targetSheetName = "KeepTrying";
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName(sourceSheetName);
  const targetSheet = ss.getSheetByName(targetSheetName);
  
  // 获取源表所有有数据的单元格范围（自动适配数据大小）
  const dataRange = sourceSheet.getDataRange();
  const values = dataRange.getValues();
  const numRows = dataRange.getNumRows();
  const numCols = dataRange.getNumColumns();
  
  // 存储最终要写入的Position-Value数据
  const result = [];
  
  // 遍历每一行每一列
  for (let row = 0; row < numRows; row++) {
    const rowNumber = row + 1; // 表格行从1开始计数
    for (let col = 0; col < numCols; col++) {
      const colLetter = columnToLetter(col + 1); // 列号转字母
      const position = colLetter + rowNumber; // 组合成A1、B3这类格式
      const value = values[row][col];
      
      // 保留所有单元格（包括空值），若要跳过空值可加判断：if (value !== '') { ... }
      result.push([position, value]);
    }
  }
  
  // 清空目标表原有内容（可选，按需决定）
  targetSheet.clearContents();
  
  // 将结果写入目标表A、B列
  if (result.length > 0) {
    targetSheet.getRange(1, 1, result.length, 2).setValues(result);
  }
}

// 辅助函数：将列数字转换为Google Sheets的列字母（支持AA、AB这类多字母列）
function columnToLetter(column) {
  let letter = '';
  while (column > 0) {
    const remainder = (column - 1) % 26;
    letter = String.fromCharCode(65 + remainder) + letter;
    column = Math.floor((column - 1) / 26);
  }
  return letter;
}

关键说明 #

• 自动适配数据：用getDataRange()自动识别源表所有有数据的区域，不用手动指定行列数
• 列号转字母：通过columnToLetter()处理列数字到字母的转换，支持超过Z列的场景
• 高效写入：先把所有数据收集到数组，再一次性写入目标表，比逐个单元格写入效率高很多

使用步骤 #

1. 打开你的Google Sheets，点击顶部「扩展程序」→「Apps 脚本」
2. 清空默认代码，粘贴上述脚本
3. 修改sourceSheetName和targetSheetName为你实际的工作表名称
4. 点击脚本编辑器顶部的运行按钮，首次运行需完成权限授权
5. 返回表格查看目标工作表，即可看到整理好的Position和Value两列数据

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