谷歌工作表 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));

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