Google Sheets脚本按日月分组行时9-10日区间分组错误如何解决

问题描述 #

我编写了一个脚本，用于读取第一列的日期数据，将所有行先按日分组，再在日分组的基础上按月分组，因为每日下有每小时或每4小时一条的多行数据。
脚本执行时先按日分组，再执行月度分组，当某月份内同时存在9日和10日数据时，月度分组环节会错误地将9日最后2行和10日前2行单独分组，无法完成正常的整月分组。
如果列中不含9日和10日数据，比如仅包含当月16日到31日的数据，该错误不会出现。
实际测试删除所有9日数据后，8日和10日区间仍会出现该错误；删除所有10日数据后错误消失，推测问题与10日的第一行数据相关。

原问题代码 #

function groupRow() {
    const timeZone = "GMT+1";
    const sheet = SpreadsheetApp.getActiveSheet();
    const rowStart = 5;
    const rows = sheet.getLastRow() - rowStart + 1;
    const values = sheet.getRange(rowStart, 1, rows, 1).getValues().flat();
    const o = [];

    values.forEach((date, i) => {
        const [m, d] = Utilities.formatDate(date, timeZone, "yyyyMM,dd").split(",");
        if (!o[m]) {
            o[m] = [];
        }
        if (!o[m][d]) {
            o[m][d] = [];
        }
        o[m][d].push(rowStart + i);
    });

    var collator = new Intl.Collator([], {numeric: true});

    for (var m in o) {
     o[m] = Object.values(o[m]).sort((a,b) => parseInt(a) - parseInt(b));
    }

    Object.values(o).forEach(m => {
        for (const d of m) {
            if (d.length === 1) {
            continue;
            }
            const range = `${ d[1] }:${ d.slice(-1)[0] }`;
            sheet.getRange(range).shiftRowGroupDepth(1);
        }
        const a = m.flat();
        if (a.length === 1) {
            return;
        }
        const range = `${ a[1] }:${ a.slice(-1)[0] }`;
        sheet.getRange(range).shiftRowGroupDepth(1);
    });
}

错误原因 #

JS 对象对键的遍历有固定规则：符合数字索引规则的字符串键（如'10'、'11'）会被当做数字处理，按数值升序排在最前面；带前导零的字符串键（如'09'、'08'）属于普通字符串键，会按插入顺序排在数字键之后。
你代码中使用两位日期字符串作为o[m]的键，1-9日的键带前导零属于普通字符串键，10日及以上的键属于数字键，导致Object.values(o[m])获取的日分组顺序是10日、11日…31日、1日、2日…9日，后续排序逻辑无法修正这个顺序错误，最终月度分组的行号区间计算错误。

修复后代码 #

function groupRow() {
    const timeZone = "GMT+1";
    const sheet = SpreadsheetApp.getActiveSheet();
    const rowStart = 5;
    const rows = sheet.getLastRow() - rowStart + 1;
    const values = sheet.getRange(rowStart, 1, rows, 1).getValues().flat();
    const o = {}; // 改用对象存储月度分组，避免数组隐式索引问题

    values.forEach((date, i) => {
        const [m, dStr] = Utilities.formatDate(date, timeZone, "yyyyMM,dd").split(",");
        const d = parseInt(dStr); // 转成数字日作为键，避免前导零导致的键排序问题
        if (!o[m]) {
            o[m] = {};
        }
        if (!o[m][d]) {
            o[m][d] = [];
        }
        o[m][d].push(rowStart + i);
    });

    // 按月处理分组
    Object.values(o).forEach(monthGroups => {
        // 按日的数值升序排序日分组
        const sortedDays = Object.entries(monthGroups)
            .sort(([dayA], [dayB]) => parseInt(dayA) - parseInt(dayB))
            .map(([_, rows]) => rows);
        
        // 处理日分组
        sortedDays.forEach(dayRows => {
            if (dayRows.length < 2) return;
            const range = `${dayRows[1]}:${dayRows.at(-1)}`;
            sheet.getRange(range).shiftRowGroupDepth(1);
        });

        // 处理月度分组
        const allMonthRows = sortedDays.flat();
        if (allMonthRows.length < 2) return;
        const range = `${allMonthRows[1]}:${allMonthRows.at(-1)}`;
        sheet.getRange(range).shiftRowGroupDepth(1);
    });
}

修复说明 #

1. 将月度分组存储结构从数组改为纯对象，避免数组的隐式索引干扰
2. 把日期字符串转成数字作为日分组的键，统一键的类型，消除前导零带来的遍历顺序问题
3. 排序时直接基于日的数值排序，保证日分组顺序和实际日期一致
4. 兼容原有分组逻辑，无需调整其他业务配置

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