How to update records in Airtable using Google Apps Script

Question

I have a script that writes the data that is collected from a Google Photos album to Airtable. The script is purely generated from ChatGPT, I have no experience of coding and indeed I don't know how to code. How can I modify the script to update the existing records in batches of 10 (assuming all the existing cells will be empty/blank before updating), instead of creating new records to write the data.

What I actually need is the script to write the data collected from Google Photos to empty cells, which means I will insert empty records into the table in which the data must be written. The script that I currently have skips the empty cells and creates a new record to write the data.

The script that I use to write data to Airtable is:

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

Answer 1

Suggestion:

This answer is intended to serve as a starting point or reference for your project. It's important to note that the community members do not provide coding services.

This is the following modification I've made on your script

1. Added a function getRecordId that extracts the ID of an existing empty record on Airtable
2. Change the API endpoint used in updatetable function from Create records to Update record
3. Insert the collected google albums to existing empty records based on the number of empty records on Airtable

Script:

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

}
 

Output:

Reference:

Airtable API