google-sheets-api - Google Sheet API 删除包含值的行

Question

所以，

我有一个从节点脚本更新的电子表格。

但我没有找到任何关于如何：

• 在 A 列中查找所有具有值的行（例如：'2019-09-06'）
• 删除这些匹配的行

非常感谢 ！

实际的脚本现在非常基本

async function appendData(spreadsheetId, sheets, rows) {
    return await sheets.spreadsheets.values.append({
        valueInputOption: 'USER_ENTERED',
        spreadsheetId: spreadsheetId,
        range: 'API',
        requestBody: {
            values: rows,
        },
    });
}

const auth = await getAuth();
const sheets = google.sheets({version: 'v4', auth});
const result = await appendData('xxSHEET_IDxx', sheets, [].concat(...rows));

标签： google-sheets-api

Answer 1

有用的东西

async function removeMatchingRows(spreadsheetId, sheets, date) {
    const result = await sheets.spreadsheets.values.get({
        spreadsheetId: spreadsheetId,
        range: 'API!A:A',
    });

    let ranges = [];
    var current = {
        dimension: "ROWS",
        startIndex: 0,
        endIndex: 0
    };
    for(var i = 0; i < result.data.values.length; i++) {
        if (result.data.values[i][0] == date) {
            if (current.endIndex === i - 1 || current.startIndex === 0) {
                if (current.startIndex === 0) {
                    current.startIndex = i;
                }
                current.endIndex = i + 1;
            } else {
                ranges.push(current);
                current = {
                    dimension: "ROWS",
                    startIndex: i,
                    endIndex: i + 1
                }
            }
        }
    }
    if (current.startIndex !== 0) {
        ranges.push(current);
    }
    let requests = ranges.map(range => {
        return {
            deleteDimension: {
                range: range
            }
        }
    }).reverse();

    await sheets.spreadsheets.batchUpdate({
        spreadsheetId: spreadsheetId,
        requestBody: {
            requests: requests
        }
    });
}