Google Apps Script：UserProperties调用差异及用户专属存储问题

我在开发Google Apps Script时遇到UserProperties的异常行为：

1. 用户1创建表格，通过菜单调用inputApiKey函数输入API密钥
2. 分享表格给用户2后，用户2也通过菜单输入了自己的专属API密钥
3. 调用MyApiKey时：
   • 用户2通过菜单调用能返回自己的密钥
   • 用户2通过单元格公式=MyApiKey()调用时，返回的却是用户1的密钥

我已经清楚该现象的原因，现咨询如何实现各用户专属信息（如API密钥）的私密存储，确保彼此不可见。

相关代码：

const API_KEY = 'API_KEY'; // 补充缺失的常量定义

function inputApiKey() {
  var ui = SpreadsheetApp.getUi();
  var apiKeyValue;
  if (!!PropertiesService.getUserProperties().getProperty(API_KEY)) apiKeyValue = ui.prompt('Your current API Key is:\n' + PropertiesService.getUserProperties().getProperty(API_KEY) + '\n\nEnter your new API Key below:\n', ui.ButtonSet.OK);
  else apiKeyValue = ui.prompt('Add your API Key here:\n', ui.ButtonSet.OK);
  if (apiKeyValue.getResponseText()) PropertiesService.getUserProperties().setProperty(API_KEY, apiKeyValue.getResponseText());
}

function MyApiKey() {
  return PropertiesService.getUserProperties().getProperty(API_KEY);
}

核心背景回顾 #

Google Apps Script的自定义函数（单元格公式调用）是以表格所有者身份执行，而非当前操作表格的用户；而菜单/对话框触发的函数，是以当前用户身份执行，这就是两种调用方式返回不同结果的原因。

以下是几种可行的私密存储方案：

方案1：放弃自定义函数，用菜单/对话框读写密钥 #

这是最直接的方案，完全基于用户身份执行，密钥存储在UserProperties中，彼此完全隔离：

const API_KEY = 'API_KEY';

function inputApiKey() {
  const ui = SpreadsheetApp.getUi();
  const currentKey = PropertiesService.getUserProperties().getProperty(API_KEY);
  let apiKeyValue;
  
  if (currentKey) {
    apiKeyValue = ui.prompt('Your current API Key is:\n' + currentKey + '\n\nEnter your new API Key below:\n', ui.ButtonSet.OK);
  } else {
    apiKeyValue = ui.prompt('Add your API Key here:\n', ui.ButtonSet.OK);
  }
  
  const newKey = apiKeyValue.getResponseText();
  if (newKey) {
    PropertiesService.getUserProperties().setProperty(API_KEY, newKey);
    ui.alert('API Key updated successfully!');
  }
}

// 新增：通过菜单触发查看密钥的函数
function showMyApiKey() {
  const ui = SpreadsheetApp.getUi();
  const key = PropertiesService.getUserProperties().getProperty(API_KEY);
  
  if (key) {
    ui.alert('Your API Key:\n' + key);
  } else {
    ui.alert('No API Key found. Please add one first.');
  }
}

// 初始化菜单
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('API Key Manager')
    .addItem('Input/Update API Key', 'inputApiKey')
    .addItem('View My API Key', 'showMyApiKey')
    .addToUi();
}

方案2：加密存储到用户私有范围（需在单元格使用密钥时） #

如果必须在脚本中使用密钥进行后续操作，可将加密后的密钥存储在表格的用户私有范围中，确保只有对应用户能访问：

const API_KEY = 'API_KEY';
const AES_KEY = Utilities.computeHmacSha256Signature('your-secure-secret', 'your-secure-secret').slice(0, 16); // 生成AES密钥

// AES加密
function encrypt(text) {
  const iv = Utilities.getUuid().replace(/-/g, '').slice(0, 16);
  const cipherText = Utilities.encrypt(Utilities.CipherAlgorithm.AES, AES_KEY, iv, text);
  return Utilities.base64Encode(iv + cipherText);
}

// AES解密
function decrypt(encryptedText) {
  const decoded = Utilities.base64Decode(encryptedText);
  const iv = decoded.slice(0, 16);
  const cipherText = decoded.slice(16);
  return Utilities.decrypt(Utilities.CipherAlgorithm.AES, AES_KEY, iv, cipherText);
}

function inputApiKey() {
  const ui = SpreadsheetApp.getUi();
  const currentKey = getMyApiKey();
  let apiKeyValue;
  
  if (currentKey) {
    apiKeyValue = ui.prompt('Your current API Key is:\n' + currentKey + '\n\nEnter your new API Key below:\n', ui.ButtonSet.OK);
  } else {
    apiKeyValue = ui.prompt('Add your API Key here:\n', ui.ButtonSet.OK);
  }
  
  const newKey = apiKeyValue.getResponseText();
  if (newKey) {
    const userEmail = Session.getActiveUser().getEmail();
    const rangeName = `User_${userEmail}_API_Key`;
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('PrivateData');
    
    // 创建隐藏工作表存储数据
    if (!sheet) {
      sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('PrivateData');
      sheet.hideSheet();
    }
    
    // 获取或创建用户专属范围
    let range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(rangeName);
    if (!range) {
      range = sheet.getRange(sheet.getLastRow() + 1, 1);
      SpreadsheetApp.getActiveSpreadsheet().setNamedRange(rangeName, range);
      // 设置权限：仅当前用户可编辑
      const protection = range.protect();
      protection.addEditor(userEmail);
      protection.removeEditors(protection.getEditors().filter(email => email !== userEmail));
    }
    
    // 加密后存储
    range.setValue(encrypt(newKey));
    ui.alert('API Key updated successfully!');
  }
}

// 获取当前用户的密钥（仅脚本内调用，不要用自定义函数）
function getMyApiKey() {
  const userEmail = Session.getActiveUser().getEmail();
  const rangeName = `User_${userEmail}_API_Key`;
  const range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(rangeName);
  
  if (range && range.getValue()) {
    return decrypt(range.getValue());
  }
  return null;
}

• 禁止在自定义函数中读取UserProperties，否则会读取表格所有者的存储值
• 存储敏感信息时务必加密，即使是UserProperties也建议加密后存储
• 不要将敏感信息明文存储在普通单元格，即使隐藏行/列也存在被脚本读取的风险

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