如何用Google Apps Script在同步相册前批量删除Airtable表记录

要实现写入新相册数据前清空Airtable目标表的所有记录，需通过Airtable API先获取表内所有记录ID，再按API限制批量删除。以下是修改后的完整方案：

修改说明 #

1. 新增deleteAllRecordsFromAirtable函数：分页获取目标表所有记录ID，按Airtable API限制（单次最多删10条）批量执行删除操作
2. 在syncAlbumData主函数中，获取Google Photos相册数据后、写入新记录前，调用清空函数完成前置操作

完整代码 #

function syncAlbumData() {
  var apiKey = ""; // 填入你的Airtable API密钥
  var baseId = ""; // 填入你的Airtable Base ID
  var tableName = "tab3"; // 填入目标表名

  // 获取Google Photos相册数据
  var albums = [];
  var nextPageToken = null;
  do {
    var pageData = getGooglePhotosAlbums(nextPageToken);
    if (pageData.albums && Array.isArray(pageData.albums)) {
      albums = albums.concat(pageData.albums);
    }
    nextPageToken = pageData.nextPageToken;
  } while (nextPageToken);

  // 清空Airtable目标表所有现有记录
  deleteAllRecordsFromAirtable(apiKey, baseId, tableName);

  // 准备写入Airtable的记录
  var records = [];
  albums.forEach(function(album) {
    var record = {
      "fields": {
        "Album Name": album.title,
        "Number of Photos": album.mediaItemsCount
      }
    };
    records.push(record);
  });

  // 将新记录写入Airtable
  writeRecordsToAirtable(records, apiKey, baseId, tableName);
}

function getGooglePhotosAlbums(pageToken) {
  var options = {
    method: "GET",
    headers: {
      "Authorization": "Bearer " + ScriptApp.getOAuthToken()
    },
    muteHttpExceptions: true
  };

  var url = "https://photoslibrary.googleapis.com/v1/albums";
  if (pageToken) {
    url += "?pageToken=" + pageToken;
  }

  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());

  return data;
}

function writeRecordsToAirtable(records, apiKey, baseId, tableName) {
  var url = "https://api.airtable.com/v0/" + baseId + "/" + tableName;
  var headers = {
    "Authorization": "Bearer " + apiKey,
    "Content-Type": "application/json"
  };

  // 按10条一组分批写入（Airtable API限制）
  var batchSize = 10;
  var batchedRecords = [];
  while (records.length > 0) {
    batchedRecords.push(records.splice(0, batchSize));
  }

  batchedRecords.forEach(function(batch) {
    var payload = {
      "records": batch
    };

    var options = {
      "method": "POST",
      "headers": headers,
      "payload": JSON.stringify(payload)
    };

    UrlFetchApp.fetch(url, options);
  });
}

// 新增函数：清空Airtable目标表所有记录
function deleteAllRecordsFromAirtable(apiKey, baseId, tableName) {
  var url = "https://api.airtable.com/v0/" + baseId + "/" + tableName;
  var headers = {
    "Authorization": "Bearer " + apiKey,
    "Content-Type": "application/json"
  };

  var allRecordIds = [];
  var offset = null;

  // 分页获取所有记录ID
  do {
    var requestUrl = url;
    if (offset) {
      requestUrl += "?offset=" + offset;
    }

    var options = {
      method: "GET",
      headers: headers,
      muteHttpExceptions: true
    };

    var response = UrlFetchApp.fetch(requestUrl, options);
    var data = JSON.parse(response.getContentText());

    if (data.records && Array.isArray(data.records)) {
      data.records.forEach(function(record) {
        allRecordIds.push(record.id);
      });
    }

    offset = data.offset;
  } while (offset);

  // 按10条一组分批删除（Airtable API限制）
  var batchSize = 10;
  while (allRecordIds.length > 0) {
    var batchIds = allRecordIds.splice(0, batchSize);
    var payload = {
      "records": batchIds.map(id => ({ id: id }))
    };

    var deleteOptions = {
      method: "DELETE",
      headers: headers,
      payload: JSON.stringify(payload)
    };

    UrlFetchApp.fetch(url, deleteOptions);
    // 可选：若表内记录极多，取消下方注释避免触发API速率限制
    // Utilities.sleep(1000);
  }
}

注意事项 #

• 确保你的Airtable API密钥拥有删除记录的权限
• 若目标表内记录数量庞大，建议启用删除函数中的延迟代码，避免触发Airtable的API请求频率限制
• 测试阶段可先在Airtable表中保留少量记录，确认清空功能正常后再全量运行

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