Help

Need help to update records in airtable using google apps script.

896 2
cancel
Showing results for 
Search instead for 
Did you mean: 
danielmohuriya
5 - Automation Enthusiast
5 - Automation Enthusiast

I have a script that writes the data that is collected from a google photos album to airtable. The script is purely genereated from cha-gpt, I have no experience of coding and indeed I don't know how to code. Could someone please help me IF POSSIBLE, to 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. 
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);
  });
}

 

 

 

2 Replies 2

To update existing records, you'll need a way of determining which record belongs to which photo. This can be based on some info retrieved with the photo and a populated field in your table. For example, an ID or exact name of the photo coming from Google photos that is already populated in Airtable.

What you're asking for involves more than a few steps (retrieve all table records from Airtable, get the record ID for each existing photo record found, update records in batches of 10, etc. Have you considered hiring this work out?

Hey @Stephen_Orr1 thank you for your response, sorry for the confusion. 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. 
I have considered hiring someone to solve this issue but due to high fee I wanted to try it for myself with the help of the community and chat-gpt to solve the problem, if I am not successful I will eventually hire someone.