How to find the next empty column using an API CALL?

[SoSimple]

Hey friends, maybe salvation will come from here.

I try to do progress tracking for my students - that is, if a particular student wrote that they are "doing well" or "need help".

Now - I want to find a way to tell Airtable to update the next empty column using an API CALL but I can't figure out how to do it.

For example, in the picture I attached - if user1 called "זרם" gives his answer it will be updated in week3 but on the other hand if the other student writes any answer it will go into the week1 column.

Does anyone have an idea?

thankss

[kuovonne]

Because Airtable is a database and not a spreadsheet, the concept of a “next column” doesn’t really exist. Columns can have different orders in different views.

You can hardcode your column order logic into your API call. 

Or you can have your API call put the value in a temporary holding field and then use an automation to move the value to the proper field. 

Or you could have a formula field that calculates the name of the next empty field and read that to determine where to put your next value. 

Or you can revisit your base design to better leverage linked tables so that you add data in linked records instead of adding columns. This is my preferred option. 

[Marko_K]

Your "weeks" are better split into row values in a table that looks a bit like:

Student	Week	Response
Student 1	1	Student 1, week 1 response ...
Student 1	2	Student 1, week 2 response ...
Student 2	1	Student 2, week 1 response ...

This way, you don't have to add a new field for each additional week. Other benefits include not having empty values ("holes" in your data), and it's easier to run reports against.

But if you're dead-set on your current setup, this script will do what you want: 

  const API_KEY = " ... "
  const BASE_ID = " ... "
  const TABLE_ID = " ... "

  const weeks = ["week1", "week2", "week3"] // Hard-code all of your columns in order.

  const records = await fetchAirtableRecords(API_KEY, BASE_ID, TABLE_ID) 
  const updatedRecords = addFirstMissingWeek(weeks, records)

  for(let record of updatedRecords) {
    await updateAirtableRecord(API_KEY, BASE_ID, TABLE_ID, record.id, record.fields);
  }

And the functions:

async function fetchAirtableRecords(API_KEY, BASE_ID, TABLE_ID) {

  const url = `https://api.airtable.com/v0/${BASE_ID}/${TABLE_ID}`;

  const options = {
    method: 'GET',
    headers: {
      'Authorization': `Bearer ${API_KEY}`,
      'Content-Type': 'application/json'
    }
  };

  const response = await fetch(url, options);
  if (!response.ok) {
    throw new Error(`HTTP error! status: ${response.status}`);
  }
  
  const data = await response.json();
  return data.records;
}

async function updateAirtableRecord(API_KEY, BASE_ID, TABLE_ID, recordId, updatedFields) {

  const url = `https://api.airtable.com/v0/${BASE_ID}/${TABLE_ID}/${recordId}`;

  const options = {
    method: 'PATCH',
    headers: {
      'Authorization': `Bearer ${API_KEY}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({ "fields": updatedFields })
  };

  try {
    const response = await fetch(url, options);
    return response.json();
  } catch (error) {
    console.error(error);
  }
}


function addFirstMissingWeek(weeks, records) {
    // Loop through each student / record
    for(let record of records) {
        // Loop through each week
        for(let week of weeks) {
            // If the student / record does not have data for this week
            if(!record.fields[week]) {
                // Add the data for the week
                record.fields[week] = `new student response here...`;
                // Break the loop as we want to add only the first missing week
                break;
            }
        }
    }
    return records;
}

Also, this is what the data looks like for reference (commented out): 

// records

// [
//   {
//     id: 'recav38ZEoUZEC6Mp',
//     createdTime: '2023-06-14T18:19:19.000Z',
//     fields: { Name: 'Student 2' }
//   },
//   {
//     id: 'recmSa4etWYah5sKl',
//     createdTime: '2023-06-14T18:19:19.000Z',
//     fields: {
//       Name: 'Student 1',
//       week1: 'student response here...',
//       week2: 'student response here...'
//     }
//   }
// ]


// updatedRecords:

// [
//   {
//     id: 'recav38ZEoUZEC6Mp',
//     createdTime: '2023-06-14T18:19:19.000Z',
//     fields: { Name: 'Student 2', week1: 'new student response here...' }
//   },
//   {
//     id: 'recmSa4etWYah5sKl',
//     createdTime: '2023-06-14T18:19:19.000Z',
//     fields: {
//       Name: 'Student 1',
//       week1: 'student response here...',
//       week2: 'student response here...',
//       week3: 'new student response here...'
//     }
//   }
// ]

Here's a before

And after:

---

Marko at Subsystem