// Code.gs - 部署为Web应用
function doGet(e) {
var sheet = SpreadsheetApp.openById('YOUR_SHEET_ID');
var parameterSheet = sheet.getSheetByName('Parameters');
// 获取参数数据
var data = parameterSheet.getDataRange().getValues();
var headers = data[0];
var result = {};
for (var i = 1; i < data.length; i++) {
var row = data[i];
var id = row[0]; // ID列
var value = row[2]; // 数值列
result[id] = value;
}
return ContentService.createTextOutput(JSON.stringify(result))
.setMimeType(ContentService.MimeType.JSON);
}
// 数据变更触发器
function onEdit(e) {
var sheet = e.source.getActiveSheet();
var range = e.range;
// 只在参数表修改时触发
if (sheet.getName() === 'Parameters') {
var timestamp = new Date();
var editor = Session.getActiveUser().getEmail();
var cell = range.getA1Notation();
var oldValue = e.oldValue;
var newValue = e.value;
// 记录修改日志
logChange(timestamp, editor, cell, oldValue, newValue);
// 发送通知邮件（如果数值变化较大）
if (Math.abs(newValue - oldValue) > oldValue * 0.2) {
sendNotificationEmail(editor, cell, oldValue, newValue);
}
}
}
function logChange(timestamp, editor, cell, oldValue, newValue) {
var sheet = SpreadsheetApp.openById('YOUR_SHEET_ID').getSheetByName('ChangeLog');
sheet.appendRow([timestamp, editor, cell, oldValue, newValue]);
}
function sendNotificationEmail(editor, cell, oldValue, newValue) {
var subject = '数值重大变更通知 - ' + cell;
var body = '数值发生超过20%的重大变更：\n' +
'修改者: ' + editor + '\n' +
'位置: ' + cell + '\n' +
'原数值: ' + oldValue + '\n' +
'新数值: ' + newValue + '\n' +
'时间: ' + new Date();
MailApp.sendEmail('[email protected]', subject, body);
}
