AppsScript中如何向全局数组添加变量而不覆盖原有内容？

问题描述 #

我有一段Google Apps Script代码，希望将group和selectedSubGroups的数据存入全局变量globalVariable中，但当前代码每次执行后，变量原有内容都会丢失，看起来像是被覆盖。已查阅push方法相关资料，但仍无法定位问题，恳请帮忙排查原因！

原代码如下：

function insertSubGroups() {
  // 获取表格及所需工作表
  var spreadsheet = SpreadsheetApp.getActive();
  var calculosSheet = spreadsheet.getSheetByName("Calculos");
  var resultadosSheet = spreadsheet.getSheetByName("Resultados");
  resultadosSheet.getRange(8, 1, resultadosSheet.getLastRow() - 7, 1).removeCheckboxes();
  resultadosSheet.getRange(8, 2, resultadosSheet.getLastRow() - 7, 1).clear();

  // 读取Calculos工作表的数据
  var groups = calculosSheet.getRange("A2:A196").getValues();
  var subGroups = calculosSheet.getRange("C3:C202").getValues();

  // 获取Resultados工作表B6单元格的下拉选项值
  var group = resultadosSheet.getRange("B6").getValue();

  // 遍历groups数组找到对应分组的索引
  var groupIndex = -1;
  for (var i = 0; i < groups.length; i++) {
    if (groups[i] == group) {
      groupIndex = i;
      break;
    }
  }

  // 找到分组索引后，收集对应的子分组
  if (groupIndex != -1) {
    var subGroupList = [];
    for (var i = groupIndex; i < subGroups.length; i++) {
      if (subGroups[i] == "") {
        break;
      }
      subGroupList.push(subGroups[i]);
    }
    // 将子分组写入Resultados工作表的B8开始区域
    resultadosSheet.getRange(8, 2, subGroupList.length, 1).setValues(subGroupList.map(function(subGroup) {
      return [subGroup];
    }));
    // 为每个子分组添加复选框
    var checkboxRange = resultadosSheet.getRange(8, 1, subGroupList.length, 1);
    checkboxRange.setDataValidation(SpreadsheetApp.newDataValidation().setAllowInvalid(false).requireCheckbox().build());
  }
}

var globalVariable = [];


function saveSubGroups() {
  // 声明存储选中数据的变量
  var selectedData = [];

  // 获取表格及Resultados工作表
  var spreadsheet = SpreadsheetApp.getActive();
  var resultadosSheet = spreadsheet.getSheetByName("Resultados");

  // 获取当前选中的分组
  var group = resultadosSheet.getRange("B6").getValue();

  // 收集所有被勾选的子分组
  var subGroupValues = resultadosSheet.getRange(8, 2, resultadosSheet.getLastRow() - 7, 1).getValues();
  var subGroupCheckboxes = resultadosSheet.getRange(8, 1, resultadosSheet.getLastRow() - 7, 1).getValues();
  var selectedSubGroups = [];
  for (var i = 0; i < subGroupValues.length; i++) {
    if (subGroupCheckboxes[i][0]) {
      selectedSubGroups.push(subGroupValues[i][0]);
    }
  }

  // 将选中数据存入selectedData
  selectedData.push({
    group: group,
    subGroups: selectedSubGroups
  });

  // 将选中数据存入全局变量globalVariable
  globalVariable.push({
    group: group,
    selectedSubGroups: selectedSubGroups
  });
  
  // 打印全局变量内容到控制台
  for (var i = 0; i < globalVariable.length; i++) {
    console.log(globalVariable[i].group + ": " + globalVariable[i].selectedSubGroups.join(", "));
  }

}

原因分析 #

Google Apps Script的全局变量不具备持久化特性。每次执行函数（无论是手动触发、菜单触发还是触发器执行），整个脚本都会重新加载，全局变量globalVariable会被重新执行var globalVariable = [];初始化，变成空数组。因此每次调用saveSubGroups时，都是往新的空数组中添加数据，之前的内容根本没有被保留，看起来就像是被覆盖了。

解决方案：使用PropertiesService实现持久化存储 #

PropertiesService是Google Apps Script提供的持久化存储服务，可将数据存储为键值对，绑定当前文档、脚本或用户，这里选择文档属性（仅当前Spreadsheet可访问）来存储数据。

修改后的代码 #

function insertSubGroups() {
  // 原函数逻辑保持不变
  var spreadsheet = SpreadsheetApp.getActive();
  var calculosSheet = spreadsheet.getSheetByName("Calculos");
  var resultadosSheet = spreadsheet.getSheetByName("Resultados");
  resultadosSheet.getRange(8, 1, resultadosSheet.getLastRow() - 7, 1).removeCheckboxes();
  resultadosSheet.getRange(8, 2, resultadosSheet.getLastRow() - 7, 1).clear();

  var groups = calculosSheet.getRange("A2:A196").getValues();
  var subGroups = calculosSheet.getRange("C3:C202").getValues();
  var group = resultadosSheet.getRange("B6").getValue();

  var groupIndex = -1;
  for (var i = 0; i < groups.length; i++) {
    if (groups[i] == group) {
      groupIndex = i;
      break;
    }
  }

  if (groupIndex != -1) {
    var subGroupList = [];
    for (var i = groupIndex; i < subGroups.length; i++) {
      if (subGroups[i] == "") {
        break;
      }
      subGroupList.push(subGroups[i]);
    }
    resultadosSheet.getRange(8, 2, subGroupList.length, 1).setValues(subGroupList.map(function(subGroup) {
      return [subGroup];
    }));
    var checkboxRange = resultadosSheet.getRange(8, 1, subGroupList.length, 1);
    checkboxRange.setDataValidation(SpreadsheetApp.newDataValidation().setAllowInvalid(false).requireCheckbox().build());
  }
}

// 读取持久化存储的数据
function getStoredData() {
  const docProps = PropertiesService.getDocumentProperties();
  const storedJson = docProps.getProperty('globalVariable');
  return storedJson ? JSON.parse(storedJson) : [];
}

// 将数据保存到持久化存储
function saveStoredData(data) {
  const docProps = PropertiesService.getDocumentProperties();
  docProps.setProperty('globalVariable', JSON.stringify(data));
}

function saveSubGroups() {
  var spreadsheet = SpreadsheetApp.getActive();
  var resultadosSheet = spreadsheet.getSheetByName("Resultados");
  var group = resultadosSheet.getRange("B6").getValue();

  var subGroupValues = resultadosSheet.getRange(8, 2, resultadosSheet.getLastRow() - 7, 1).getValues();
  var subGroupCheckboxes = resultadosSheet.getRange(8, 1, resultadosSheet.getLastRow() - 7, 1).getValues();
  var selectedSubGroups = [];
  for (var i = 0; i < subGroupValues.length; i++) {
    if (subGroupCheckboxes[i][0]) {
      selectedSubGroups.push(subGroupValues[i][0]);
    }
  }

  // 获取已有存储数据
  let storedData = getStoredData();
  // 添加新的选择数据
  storedData.push({
    group: group,
    selectedSubGroups: selectedSubGroups
  });
  // 保存更新后的数据
  saveStoredData(storedData);
  
  // 打印日志验证
  for (var i = 0; i < storedData.length; i++) {
    console.log(storedData[i].group + ": " + storedData[i].selectedSubGroups.join(", "));
  }
}

代码说明 #

1. getStoredData()：从文档属性中读取已存储的数据，若没有则返回空数组（JSON.parse用于将存储的字符串转回数组）
2. saveStoredData(data)：将数组转换为JSON字符串（PropertiesService仅支持存储字符串），然后保存到文档属性
3. saveSubGroups()：先读取已有数据，再添加新的选择记录，最后保存回存储，确保数据不会丢失

备选方案：使用隐藏工作表存储 #

如果不想用PropertiesService，也可以在Spreadsheet中新建一张隐藏工作表（比如命名为Storage），将数据以行的形式写入该表，读取时再从表中加载数据，同样可以实现持久化。

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