Google Apps Script行分组函数手动运行正常 时间驱动触发器执行无错但失效

• 核心故障点是createGroup()函数中调用了SpreadsheetApp.getActiveSheet()：手动运行代码时，你当前打开的工作表正好是LOG表，getActiveSheet()可以正确获取目标表；但时间驱动触发器是后台静默执行，不存在「活动工作表」的上下文，默认会取表格文件的第一个工作表执行分组操作，自然在LOG表中看不到效果。
• 潜在风险点：日期匹配使用的是Session.getScriptTimeZone()脚本时区，如果脚本时区和你表格设置的时区不一致，会导致日期格式化结果和表格内存储的日期格式不匹配，找不到对应行，也会出现无报错但无执行效果的情况。

仅需两处最小改动即可解决问题：

1. 将createGroup()中的活动工作表获取逻辑改为直接指定LOG表
2. 将日期格式化的时区替换为表格时区，避免时区不匹配问题

修改后完整代码如下：

const logSheetName = "LOG";

// Functions to group by day, month and year. Within each the variables are redefined, except logSheetName

// Grouping by Day
function groupDays() {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(logSheetName);
    // Variables needed to get row number of the first row of row block for last day, for rowStart variable
    var dateDay = sheet.getRange(sheet.getLastRow() - 1, 1).getValue();
    // 替换为表格时区，避免时区不一致导致的日期匹配错误
    var dayPlusMonth = Utilities.formatDate(dateDay, ss.getSpreadsheetTimeZone(), "dd/MM/YYYY");
    var cells = sheet.getRange("A5:A").createTextFinder(dayPlusMonth).findAll().map(x => x.getRowIndex());
    var firstRowOfDay = cells[0];
    // Variables needed for Grouping Function
    rowStart = firstRowOfDay;
    rows = sheet.getLastRow() - rowStart + 1;
    dates = sheet.getRange(rowStart, 1, rows, 1).getValues().flat();
    // Group by day execution
    groupDates(dates, 'date');
    console.log("groupDays() executed");
    console.log("first row from Days position is " + rowStart);
}

var rowStart;
var rows;
var dates;
var prevDate;
var rangeStart;
var rangeEnd;
var groupingPeriod;

function groupDates(dates, period) {
    groupingPeriod = period;
    dates.forEach((date, idx) => {
        if (idx === 0) {
            processInitialDate(date);
        } else {
            processDate(date, idx);
        }
    });
}

function processInitialDate(date) {
    prevDate = date;
    rangeStart = 0;
    rangeEnd = rangeStart;
}

function processDate(date, idx) {
    if (periodHasChanged(date, prevDate, groupingPeriod)) {
        createGroup();
        rangeStart = rangeEnd + 1;
        rangeEnd = rangeStart;
    } else {
        rangeEnd++;
        if (idx === dates.length - 1) {
            createGroup();
        }
    }
    prevDate = date;
}

function periodHasChanged(currDate, prevDate, period) {
    switch (period) {
        case 'year':
            var currPeriod = currDate.getYear();
            var prevPeriod = prevDate.getYear();
            break;
        case 'month':
            var currPeriod = currDate.getMonth();
            var prevPeriod = prevDate.getMonth();
            break;
        case 'date':
            var currPeriod = currDate.getDate();
            var prevPeriod = prevDate.getDate();
            break;
    }
    return currPeriod !== prevPeriod;
}

function createGroup() {
    // 直接指定LOG表，不依赖活动工作表上下文
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(logSheetName);
    var rangeGroup = dates.slice(rangeStart, rangeEnd + 1);
    if (rangeGroup.length > 1) {
        var range = `${rowStart + rangeStart + 1}:${rowStart + rangeEnd}`;
        sheet.getRange(range).shiftRowGroupDepth(1);
    }
}

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