NodeJS调用Google Sheets API遇配额超限及异步执行问题求助

我用Node.js开发项目，需要列出Google Drive中所有共享给域外用户的文件并写入Google Sheets，目前碰到两个核心问题：

1. 文件量多、分页频繁，写入请求触发Google Sheets API的429错误（Write requests每分钟用户配额超限），试了sleep没用；
2. 批量处理多用户时，forEach循环异步执行导致速度过快，没法等单用户处理完再继续。

相关代码 #

主检测函数 #

function exportDataFileOOD(nextPageToken, auth) {
    const service = google.drive({version: 'v3', auth});
    service.files.list({
            corpora: 'user',
            includeItemsFromAllDrives: true,
            pageSize: 30,
            pageToken: nextPageToken,
            supportsAllDrives: true,
            q: '\'me\' in owners and not trashed',
            fields: 'nextPageToken,files(name,id,webViewLink,permissions, shared)'
    }, (err, res) => {

    if (err) {
        return console.error('The API returned an error:', err.message);
    }

    let files = res.data.files
    nextPageToken = res.data.nextPageToken

    files.forEach((file) => {
            if(file.shared === true){

            let fileOOD = new Boolean(false);
            let permissionList = file.permissions

            permissionList.forEach((permission) => {
                if (permission.emailAddress){
                    let mailSplit = permission.emailAddress.split('@')

                    if (mailSplit[1] !== 'DOMAIN') {
                        fileOOD = Boolean(true);
                    }
                }
            })

            if (fileOOD === true) {
                writeData(file)
                sleep(10000)
            }
        }
    })

        if(nextPageToken){
            exportDataFileOOD(nextPageToken)
        }
    })
}

写入函数 #

function writeData(file){
    columns[0].value.push(file.name)
    columns[1].value.push(file.webViewLink)

    file.permissions.forEach((permission) => {
        if (permission.role === "owner") {
            columns[2].value.push(permission.emailAddress);
        } else if (permission.role === "writer") {
            columns[3].value.push(permission.emailAddress);
        } else if (permission.role === "reader") {
            columns[4].value.push(permission.emailAddress)
        } else if (permission.role === "commenter") {
            columns[5].value.push(permission.emailAddress)
        } else {
            console.log("Error about this user permission: " + permission.email + " is " + permission.role)
        }
    })

    let auth = callAPI("USER@DOMAIN")
    columns.forEach((column) => {
        const sheets = google.sheets({version: 'v4', auth})

        if (column.value !== "") {
            sheets.spreadsheets.values.batchUpdate({
                spreadsheetId: idSheets,
                requestBody: {
                    valueInputOption: "RAW",
                    data: [{
                        range: column.letter + line,
                        values: [[
                            String(column.value)
                        ]]
                    }]
                }
            }, (err) => {
                if (err) {
                    console.log(err)
                }
            })
        }
    })

    columns.forEach((column) => {
        column.value = [];
    })
    line++
}

批量用户处理循环 #

let users = promise.users

users.forEach((user) => {
        exportDataFileOOD(null, callAPI(user.primaryEmail))
})

columns变量 #

let columns = [
    {
        letter: "A",
        title: "Name",
        value: []
    }, {
        letter: "B",
        title: "Link",
        value: []
    }, {
        letter: "C",
        title: "Owner(s)",
        value: []
    }
];

一、解决Google Sheets API 429配额超限问题 #

原代码的核心问题是：单文件分多列发起请求导致请求量爆炸，且同步sleep无法等待异步API请求完成。优化方案如下：

1. 批量攒数据，一次性写入：把多个文件的数据攒成一批，用一次batchUpdate写入多行多列，大幅减少请求次数；
2. 异步限流+指数退避：用Promise封装请求，配合定时器控制请求间隔，碰到429错误时自动重试并递增等待时间。

优化后的写入逻辑示例：

// 全局缓存待写入数据，批量提交
const batchBuffer = [];
const BATCH_SIZE = 50; // 按配额调整，比如每次写50行
const REQUEST_INTERVAL = 1000; // 控制请求间隔，避免触发配额

// 封装异步批量写入函数
async function batchWriteToSheets() {
    if (batchBuffer.length === 0) return;

    const auth = callAPI("USER@DOMAIN");
    const sheets = google.sheets({version: 'v4', auth});
    const startLine = line;
    // 构造批量写入的数据集
    const data = batchBuffer.map((row, index) => ({
        range: `A${startLine + index}:F${startLine + index}`,
        values: [row]
    }));

    try {
        await sheets.spreadsheets.values.batchUpdate({
            spreadsheetId: idSheets,
            requestBody: {
                valueInputOption: "RAW",
                data: data
            }
        });
        line += batchBuffer.length;
        batchBuffer.length = 0;
        console.log(`成功写入${data.length}行`);
    } catch (err) {
        if (err.code === 429) {
            // 指数退避重试
            const waitTime = Math.pow(2, global.retryCount || 0) * 1000;
            console.log(`触发配额限制，等待${waitTime}ms后重试`);
            await new Promise(resolve => setTimeout(resolve, waitTime));
            global.retryCount = (global.retryCount || 0) + 1;
            await batchWriteToSheets();
        } else {
            console.error("写入失败:", err);
        }
    }
}

// 修改writeData为异步攒数据函数
async function writeData(file) {
    const row = ["", "", "", "", "", ""];
    row[0] = file.name;
    row[1] = file.webViewLink;

    // 整理权限到对应列
    file.permissions.forEach(permission => {
        switch(permission.role) {
            case "owner":
                row[2] = row[2] ? `${row[2]}, ${permission.emailAddress}` : permission.emailAddress;
                break;
            case "writer":
                row[3] = row[3] ? `${row[3]}, ${permission.emailAddress}` : permission.emailAddress;
                break;
            case "reader":
                row[4] = row[4] ? `${row[4]}, ${permission.emailAddress}` : permission.emailAddress;
                break;
            case "commenter":
                row[5] = row[5] ? `${row[5]}, ${permission.emailAddress}` : permission.emailAddress;
                break;
            default:
                console.log(`权限异常：${permission.email} 的角色是 ${permission.role}`);
        }
    });

    batchBuffer.push(row);

    // 达到批量阈值时写入
    if (batchBuffer.length >= BATCH_SIZE) {
        await batchWriteToSheets();
        await new Promise(resolve => setTimeout(resolve, REQUEST_INTERVAL));
    }
}

二、解决批量用户异步执行失控问题 #

原forEach循环会同时启动所有用户的处理流程，导致并发过高。优化方案：

1. 串行处理用户：用for...of循环配合await，等前一个用户的所有文件处理完成后再开始下一个；
2. 可选并发限制：如果需要并行处理，用p-limit库控制同时处理的用户数量（比如同时处理2个）。

优化后的批量处理逻辑示例：

// 串行处理所有用户
async function processAllUsers(users) {
    for (const user of users) {
        console.log(`开始处理用户：${user.primaryEmail}`);
        await processUserFiles(user.primaryEmail);
        console.log(`用户${user.primaryEmail}处理完成`);
    }
}

// 把原检测函数改造成异步串行版本
async function processUserFiles(userEmail) {
    const auth = callAPI(userEmail);
    const service = google.drive({version: 'v3', auth});
    let nextPageToken = null;

    do {
        const res = await service.files.list({
            corpora: 'user',
            includeItemsFromAllDrives: true,
            pageSize: 30,
            pageToken: nextPageToken,
            supportsAllDrives: true,
            q: '\'me\' in owners and not trashed',
            fields: 'nextPageToken,files(name,id,webViewLink,permissions, shared)'
        });

        const files = res.data.files;
        nextPageToken = res.data.nextPageToken;

        // 串行处理每个文件，避免并发写入
        for (const file of files) {
            if (file.shared) {
                let fileOOD = false;
                // 找到域外权限就停止遍历
                for (const permission of file.permissions) {
                    if (permission.emailAddress) {
                        const mailSplit = permission.emailAddress.split('@');
                        if (mailSplit[1] !== 'DOMAIN') {
                            fileOOD = true;
                            break;
                        }
                    }
                }
                if (fileOOD) {
                    await writeData(file);
                }
            }
        }
    } while (nextPageToken);

    // 处理最后一批剩余数据
    if (batchBuffer.length > 0) {
        await batchWriteToSheets();
    }
}

// 调用方式
const users = promise.users;
processAllUsers(users);

// 可选：并行处理（需先安装p-limit：npm install p-limit）
// const pLimit = require('p-limit');
// const limit = pLimit(2); // 同时处理2个用户
// const promises = users.map(user => limit(() => processUserFiles(user.primaryEmail)));
// await Promise.all(promises);

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