javascript 如何使用Google Apps脚本更新Airtable中的记录

fivyi3re 于 2023-06-28 发布在 Java

关注(0)|答案(1)|浏览(124)

我有一个脚本，它将从Google Photos相册收集的数据写入Airtable。该脚本是纯粹从ChatGPT生成的，我没有编码的经验，实际上我不知道如何编码。我如何修改脚本以批量更新10条现有记录（假设更新前所有现有单元格都是空的），而不是创建新记录来写入数据。
我实际需要的是将从Google Photos收集的数据写入空单元格的脚本，这意味着我将在必须写入数据的表中插入空记录。我目前拥有的脚本跳过空单元格并创建一个新记录来写入数据。
我用来向Airtable写入数据的脚本是：

function listAlbumData() {
  var apiKey = "API_KEY/ACCESS_TOKEN"; //paste your api or access token here
  var baseId = "BASE_ID"; //paste your base id here
  var tableName = "tab3"; //paste your table name here

  // Get albums from Google Photos
  var albums = [];
  var nextPageToken = null;
  do {
    var pageData = getAlbums(nextPageToken);
    if (pageData.albums && Array.isArray(pageData.albums)) {
      albums = albums.concat(pageData.albums);
    }
    nextPageToken = pageData.nextPageToken;
  } while (nextPageToken);

  // Prepare records for Airtable
  var records = [];
  albums.forEach(function(album) {
    var record = {
      "fields": {
        "Album Name": album.title,
        "Number of Photos": album.mediaItemsCount
      }
    };
    records.push(record);
  });

  // Write records to Airtable
  updatetable(records, apiKey, baseId, tableName);
}

function getAlbums(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 updatetable(records, apiKey, baseId, tableName) {
  var url = "https://api.airtable.com/v0/" + baseId + "/" + tableName;
  var headers = {
    "Authorization": "Bearer " + apiKey,
    "Content-Type": "application/json"
  };

  // Batch the records into groups of 10
  var batchSize = 10;
  var batchedRecords = [];
  while (records.length > 0) {
    batchedRecords.push(records.splice(0, batchSize));
  }

  // Send requests for each batch
  batchedRecords.forEach(function(batch) {
    var payload = {
      "records": batch
    };

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

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

JavaScript

来源：https://stackoverflow.com/questions/76520006/how-to-update-records-in-airtable-using-google-apps-script

1条答案

按热度按时间

jvidinwx1#

建议：

本答案旨在作为您的项目的起点或参考。需要注意的是，社区成员不提供编码服务。

这是我对你的剧本做的以下修改
1.添加了一个函数getRecordId，用于提取Airtable上现有空记录的ID
1.将updatetable函数中使用的API端点从Create records更改为Update record
1.根据Airtable上的空记录数量，将收集的Google相册插入到现有的空记录中

脚本：

function listAlbumData() {
  var apiKey = "API KEY"; //paste your api or access token here
  var baseId = "BASE ID"; //paste your base id here
  var tableName = "TABLE NAME"; //paste your table name here

  // Get albums from Google Photos
  var albums = [];
  var nextPageToken = null;
  do {
    var pageData = getAlbums(nextPageToken);
    if (pageData.albums && Array.isArray(pageData.albums)) {
      albums = albums.concat(pageData.albums);
    }
    nextPageToken = pageData.nextPageToken;
  } while (nextPageToken);

  // Prepare records for Airtable
  var records = [];
  albums.forEach(function (album) {
    var record = {
      "fields": {
        "Album Name": album.title,
        "Number of Photos": album.mediaItemsCount
      }
    };
    records.push(record);
  });
  // Write records to Airtable
  updatetable(records, apiKey, baseId, tableName);
}

function getAlbums(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 updatetable(records, apiKey, baseId, tableName) {
  var recordId = getRecordId(apiKey, baseId, tableName)
  for (var i = 0; i < recordId.length; i++) {
    var url = "https://api.airtable.com/v0/" + baseId + "/" + tableName + "/" + recordId[i];
    var headers = {
      "Authorization": "Bearer " + apiKey,
      "Content-Type": "application/json"
    };

    var options = {
      "method": "PATCH",
      "headers": headers,
      "payload": JSON.stringify(records[i])
    };
    UrlFetchApp.fetch(url, options);
  };

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

    var options = {
      "method": "Get",
      "headers": headers
    };
    result = UrlFetchApp.fetch(url, options);
    response = JSON.parse(result)
    data = response.records
    var recordId = []
    for (var i = 0; i < data.length; i++) {
      recordId.push(data[i].id)
    };
    return recordId
  };

}

输出：

参考：

Airtable API

赞(0）回复(0）举报 2023-06-28

我来回答

javascript 如何使用Google Apps脚本更新Airtable中的记录

1条答案

建议：

脚本：

输出：

参考：

相关问题

热门标签

最新问答