Google Sheet API Delete Rows containing a value

Question

So,

I have a spreadsheet that I update from a node script.

But I don't find any documentation on how to:

• Find all the rows that have a value (example: '2019-09-06') in column A
• Delete these matching rows

Thanks a lot !

The actual script is pretty basic for now

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));

Answer 1

Something that works

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
        }
    });
}