金山文档数据表查询脚本AirScript

AirScript.js

//
// Get a date object for the current time
var d = new Date();
// Set it to one month ago
d.setMonth(d.getMonth() - 1);
d.setDate(1)
// Zero the time component
d.setHours(0, 0, 0, 0);
// Get the time value in milliseconds and convert to seconds
datestr = d.toLocaleDateString("zh-CN")
console.log(datestr);
// 获取当前工作表
const currentSheet = ActiveSheet
console.log(Application.ActiveSheet.Name)
Application.Sheets.Item('国外订单自动化').Activate()
const sheet = Application.ActiveSheet
function fetchAllRecords() {
  const view = sheet.Selection.GetActiveView()
  let all = []
  let offset = null;

  while (all.length === 0 || offset) {
    let records = sheet.Record.GetRecords({
      ViewId: view.viewId,
      Offset: offset,
      MaxRecords: 1,
      Fields: ["编号","状态", "日期"],
      Filter: {"mode": "AND", "criteria": [{
        "field": "状态",
        "op": "Equals",
        "values": ["待付款"]
      },{
        "field": "日期",
        "op": "Less",
        "values": [datestr]
      },]}
    })
    offset = records.offset
    all = all.concat(records.records)
  }
  console.log(all.length)
  return all
}

const recs = fetchAllRecords()
console.log(recs)
for(var i=0; i< recs.length; i++) {
  const upded = sheet.Record.UpdateRecords({
        Records: [{
            id: recs[i].id,
            fields: {
                 "状态": "已取消",
            }
        }],
    })
    console.log(upded);
}