Skip to main content
Solved

Upserting multiple fields based on API data with an automation script

  • May 22, 2023
  • 9 replies
  • 283 views
D
Forum|alt.badge.img+5

I am building an automated script that fetches two properties from an external API and subsequently upserts those data points into distinct fields in Airtable. The input is a URL field in the relevant record, and entering a URL in the field triggers the script.

So far, I've put together a getData() function that retrieves the data from the external API and stores it as two variables: name and owner.

I'm stuck as to how I actually populate the fields. In basic terms, I want to populate the "Name" field in Airtable with name and the "Owner" field in Airtable with owner.

I've found the following snippets that may be the key, but I would appreciate some guidance: 

 

let table = base.getTable("Records list"); let record = await input.recordAsync("Converting record", table);

 

and

 

base('Records list').update([...]);

 

I can't find a learning resource for understanding the .recordAsync() method nor for the base.update() syntax. Is either of these what I'm looking for? Furthermore, is it possible to update fields in other tables?

Best answer by ag314

You are on the right track. And learning to use code is far easier than you may think. You don’t need to wire up any external 3rd party services - Airtable gives you everything you need.

And yes, you can update fields in any table you like. Below are some examples to help get you started. Feel free to reply back if you have more questions.

First step is what you have in the first line of code:

let table = base.getTable("Records list");

That gives you access to a table named “Records list”. So, in your case, change that to whatever your table is named.

If you are looking to add a NEW record into the table, then you use code like this:

await table.createRecordAsync({
  'Name': the name data from your api
  'Owner': the owner data from your api
})

This will add a new row to the table with the data shown above. And you can do this for ANY table in your base.

Now, if your goal is to update an EXISTING record in a table, then you use the `updateRecordAsync` method, an example shown below. But note that in order to update a row in a table, you first need to know which record needs to be updated. When creating a NEW row, you don’t need that info since Airtable will always add the new row at the end. But when you need to update an existing record, you need to know the row - hopefully that makes sense.

So, getting the row (otherwise known as the record id) that you need to update is quite easy - typically an automation step will provide that to you. So once you have that record id, then you update the table like this:

await table.updateRecordAsync({ recordId,
  'Name': a new name value
  'Owner': a new owner value
})

So you see that updating is very similar to creating new rows - except now we added in the row (recordId) to tell us which row to update.

Hopefully that makes sense. And hopefully you can see how easy it is using code to do whatever you want in Airtable. Don’t be dissuaded by anyone telling you coding is hard. It’s quite simple, and opens up a world of possibilities in Airtable.

I hope this helps.

-Anthony

9 replies

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • May 22, 2023

I’m so sorry that I can’t directly answer your question, because I don’t know JavaScript. (There are other JavaScript experts here who may be able to help below.)

However, I do understand API calls, and even I was finding Airtable’s “upsert” command to be a little bit challenging to understand. 

if you’re okay with a low-code/no-code solution instead of scripting, I love using the “upsert” command that Make has built into their Airtable automations.

Make also lets you make custom API calls to custom APIs by using its HTTP module. 

Because I don’t know scripting, I use Make for tons of things every day. 

There can also be a bit of a learning curve with Make, which is why I created this basic navigation video for Make, along with providing the link to Make’s free training courses. 

D
A
Forum|alt.badge.img+4
  • ag314
  • Inspiring
  • Answer
  • May 22, 2023

You are on the right track. And learning to use code is far easier than you may think. You don’t need to wire up any external 3rd party services - Airtable gives you everything you need.

And yes, you can update fields in any table you like. Below are some examples to help get you started. Feel free to reply back if you have more questions.

First step is what you have in the first line of code:

let table = base.getTable("Records list");

That gives you access to a table named “Records list”. So, in your case, change that to whatever your table is named.

If you are looking to add a NEW record into the table, then you use code like this:

await table.createRecordAsync({
  'Name': the name data from your api
  'Owner': the owner data from your api
})

This will add a new row to the table with the data shown above. And you can do this for ANY table in your base.

Now, if your goal is to update an EXISTING record in a table, then you use the `updateRecordAsync` method, an example shown below. But note that in order to update a row in a table, you first need to know which record needs to be updated. When creating a NEW row, you don’t need that info since Airtable will always add the new row at the end. But when you need to update an existing record, you need to know the row - hopefully that makes sense.

So, getting the row (otherwise known as the record id) that you need to update is quite easy - typically an automation step will provide that to you. So once you have that record id, then you update the table like this:

await table.updateRecordAsync({ recordId,
  'Name': a new name value
  'Owner': a new owner value
})

So you see that updating is very similar to creating new rows - except now we added in the row (recordId) to tell us which row to update.

Hopefully that makes sense. And hopefully you can see how easy it is using code to do whatever you want in Airtable. Don’t be dissuaded by anyone telling you coding is hard. It’s quite simple, and opens up a world of possibilities in Airtable.

I hope this helps.

-Anthony

D
kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • May 22, 2023

Have you see the Scripting Documentation? It includes information on how to create and update records, as well as the write format for each field type.

Note that while the web API has upsert capabilities, scripting does not. If you want to upsert a record, you must manually check for an existing record on your own and then have your code decide if it will create a new record or update an existing one.

ScottWorld
D
D
Forum|alt.badge.img+5
  • DPG
  • Author
  • Inspiring
  • May 23, 2023

Have you see the Scripting Documentation? It includes information on how to create and update records, as well as the write format for each field type.

Note that while the web API has upsert capabilities, scripting does not. If you want to upsert a record, you must manually check for an existing record on your own and then have your code decide if it will create a new record or update an existing one.


Thank you for the resources and the succinct explanation. I was mistaken in using the "upsert" term and meant update.

D
Forum|alt.badge.img+5
  • DPG
  • Author
  • Inspiring
  • May 23, 2023

I’m so sorry that I can’t directly answer your question, because I don’t know JavaScript. (There are other JavaScript experts here who may be able to help below.)

However, I do understand API calls, and even I was finding Airtable’s “upsert” command to be a little bit challenging to understand. 

if you’re okay with a low-code/no-code solution instead of scripting, I love using the “upsert” command that Make has built into their Airtable automations.

Make also lets you make custom API calls to custom APIs by using its HTTP module. 

Because I don’t know scripting, I use Make for tons of things every day. 

There can also be a bit of a learning curve with Make, which is why I created this basic navigation video for Make, along with providing the link to Make’s free training courses. 


Thank you for your tips! 

D
Forum|alt.badge.img+5
  • DPG
  • Author
  • Inspiring
  • May 23, 2023

You are on the right track. And learning to use code is far easier than you may think. You don’t need to wire up any external 3rd party services - Airtable gives you everything you need.

And yes, you can update fields in any table you like. Below are some examples to help get you started. Feel free to reply back if you have more questions.

First step is what you have in the first line of code:

let table = base.getTable("Records list");

That gives you access to a table named “Records list”. So, in your case, change that to whatever your table is named.

If you are looking to add a NEW record into the table, then you use code like this:

await table.createRecordAsync({
  'Name': the name data from your api
  'Owner': the owner data from your api
})

This will add a new row to the table with the data shown above. And you can do this for ANY table in your base.

Now, if your goal is to update an EXISTING record in a table, then you use the `updateRecordAsync` method, an example shown below. But note that in order to update a row in a table, you first need to know which record needs to be updated. When creating a NEW row, you don’t need that info since Airtable will always add the new row at the end. But when you need to update an existing record, you need to know the row - hopefully that makes sense.

So, getting the row (otherwise known as the record id) that you need to update is quite easy - typically an automation step will provide that to you. So once you have that record id, then you update the table like this:

await table.updateRecordAsync({ recordId,
  'Name': a new name value
  'Owner': a new owner value
})

So you see that updating is very similar to creating new rows - except now we added in the row (recordId) to tell us which row to update.

Hopefully that makes sense. And hopefully you can see how easy it is using code to do whatever you want in Airtable. Don’t be dissuaded by anyone telling you coding is hard. It’s quite simple, and opens up a world of possibilities in Airtable.

I hope this helps.

-Anthony


I appreciate the encouragement and explanation. Thank you!

Regarding the .updateRecordAsync() method. I added an input variable for the record ID, which I defined as simply "record", and appended my script with the following code:

for (let record of result.records) { await table.updateRecordAsync(record, { 'Name': name, 'Owner': owner }) }

Is the "for" loop necessary, redundant or incorrect? I suspect it's redundant. Could I instead wrap the "await table.updateRecordAsync()" in an async function? Can I place an async function within an async function? Maybe it would help if I provided more context:

async function refreshData() { var responseToken = await fetch(url, options); var dataToken = await responseToken.json(); var accessToken = dataToken.access_token; var responseApi = await fetch(...} }); var dataApi = await responseApi.json(); var name = dataApi.name; var owner = dataApi.owner.display_name; let table = base.getTable('Records list'); let result = await table.selectRecordsAsync(); for (let record of result.records) { await table.updateRecordAsync(record, { 'Playlist': name, 'Owner': owner }) } }

 I appreciate any further insight.

A
Forum|alt.badge.img+4
  • ag314
  • Inspiring
  • May 23, 2023

I appreciate the encouragement and explanation. Thank you!

Regarding the .updateRecordAsync() method. I added an input variable for the record ID, which I defined as simply "record", and appended my script with the following code:

for (let record of result.records) { await table.updateRecordAsync(record, { 'Name': name, 'Owner': owner }) }

Is the "for" loop necessary, redundant or incorrect? I suspect it's redundant. Could I instead wrap the "await table.updateRecordAsync()" in an async function? Can I place an async function within an async function? Maybe it would help if I provided more context:

async function refreshData() { var responseToken = await fetch(url, options); var dataToken = await responseToken.json(); var accessToken = dataToken.access_token; var responseApi = await fetch(...} }); var dataApi = await responseApi.json(); var name = dataApi.name; var owner = dataApi.owner.display_name; let table = base.getTable('Records list'); let result = await table.selectRecordsAsync(); for (let record of result.records) { await table.updateRecordAsync(record, { 'Playlist': name, 'Owner': owner }) } }

 I appreciate any further insight.


No problem.

In your refreshData method, I’m not seeing where you define the options object. But let’s assume you’ve not included that here and that the code flow down through name and owner give you exactly what you need …

With that, here’s what you want to do. I’ll describe it first in plain text, and then with code.

You’ll need a way to identify exactly which record in the “Records list” table that you want to update. There are two ways to do that: you can either pass the id in from a prior step in the automation, or you can use code to find the record in question. I’ll give examples of both below.

First example - where the id of the row you want to update is passed in from a prior step in the automation. For example, let’s say you had a checkbox column in your “Records list” table called “Refresh playlist”. And the automation triggered anytime the checkbox was checked. In that case, you would create an input in your code: when you are in the code editor, on the left side, click the blue link that reads “+ Add input variable”. And then give it a name like “recordId” (without the quotes). And the Value will be Airtable record ID from the previous step. With that, you are ready to wire-up the update like this:

let table = base.getTable('Records list');
await table.updateRecordAsync(recordId, {
  'Playlist': name,
  'Owner': owner
})

That’s it. No need to loop. No need to select any records. Why? Because you already know EXACTLY which record to update - the one given by the recordId that was passed in.

Second example - where you do NOT know ahead of time exactly which record needs to be updated. In THAT case, then we’ll need to loop through the records to find the one to update. Like this:

let table = base.getTable('Records list');
let allRecords = table.selectRecordsAsync({ fields:
  [ "Playlist", "Owner" ]
})

for (let record of allRecords) {
  let recordOwner = record.getCellValueAsString("Owner")
  if (recordOwner === owner) {
    await table.updateRecordAsync(recordId, {
      'Playlist': name,
    })
  }
  break
}

Now, let’s walk through what we’re doing here. First, we get all the records in the “Records list” table. Note that when you are calling the selectRecordsAsync function, you want to pass in which fields you want to get back.

The line that reads "for (let record of allRecords)" is simply saying this: "Let's loop over all the rows in this table, and we'll take each one and stick it into a variable named record." You can just as easily have written "let abc of allRecords", in which each, the loop will stick each row into a variable named "abc". 

Then, once we have the list of all records, we’ll loop through them. And what we’re looking for is the record that has an owner that matches the owner returned from your fetch call. To do that, we need to get the text of the owner field from each row. Which we do with the line that reads "let recordOwner = record.getCellValueAsString("Owner")". Now the recordOwner variable will contain whatever is in the owner field as we loop through each row in the table.

Then the “if” statement is comparing the owner in each row of the table (recordOwner) with the owner returned by the fetch. And if it finds a match, then it updates that one record with the new Playlist info. Note that I may not fully understand your use-case, but hopefully what I’m describing makes sense. Also, further complicating your specific example is that you have a table of records, which I assume contains actual music records, which is cool. But the word “record” also means a row in an Airtable table. So the wording can get a little mixed up.

Anyway, after we found the row where the owner matches, then we do the update. And notice that we only updated the Playlist entry. No need to update the Owner field because we don’t need to change it. When you do an update like this, you only need to specify the fields that are actually changing.

And then after the update, we “break” from the loop. In other words, there is no need to continue looping though all records - we already found the owner. So we can speed up the code by breaking from the loop.

Now a great question to ask yourself here is this: “what if there is more than one possible match”? Well, that’s where you need to think through your use case. Because the code above will only update the FIRST owner match. If you remove the break, then the code above will update that playlist for EVERY owner match - which may, or may not, be what you want.

But hopefully this gives you a brief overview of finding and updating records using code in Airtable. Notice how short the code is. Yes, I include a lot of written text here - but only to help acclimate you to the concepts. Once you get a few of these under your belt, you’ll be flying on your own.

Also, don’t hesitate to play around in the code. Drop in “console.log” statements all over the place so you can see exactly what is happening. You’ll soon find yourself saying, “ohhh, that’s how that works.”

And, don’t hesitate to reach back into the community with any questions you have. The combination of Airtable and coding is incredibly powerful. It opens a world of opportunities for you.

D
D
Forum|alt.badge.img+5
  • DPG
  • Author
  • Inspiring
  • May 23, 2023

No problem.

In your refreshData method, I’m not seeing where you define the options object. But let’s assume you’ve not included that here and that the code flow down through name and owner give you exactly what you need …

With that, here’s what you want to do. I’ll describe it first in plain text, and then with code.

You’ll need a way to identify exactly which record in the “Records list” table that you want to update. There are two ways to do that: you can either pass the id in from a prior step in the automation, or you can use code to find the record in question. I’ll give examples of both below.

First example - where the id of the row you want to update is passed in from a prior step in the automation. For example, let’s say you had a checkbox column in your “Records list” table called “Refresh playlist”. And the automation triggered anytime the checkbox was checked. In that case, you would create an input in your code: when you are in the code editor, on the left side, click the blue link that reads “+ Add input variable”. And then give it a name like “recordId” (without the quotes). And the Value will be Airtable record ID from the previous step. With that, you are ready to wire-up the update like this:

let table = base.getTable('Records list');
await table.updateRecordAsync(recordId, {
  'Playlist': name,
  'Owner': owner
})

That’s it. No need to loop. No need to select any records. Why? Because you already know EXACTLY which record to update - the one given by the recordId that was passed in.

Second example - where you do NOT know ahead of time exactly which record needs to be updated. In THAT case, then we’ll need to loop through the records to find the one to update. Like this:

let table = base.getTable('Records list');
let allRecords = table.selectRecordsAsync({ fields:
  [ "Playlist", "Owner" ]
})

for (let record of allRecords) {
  let recordOwner = record.getCellValueAsString("Owner")
  if (recordOwner === owner) {
    await table.updateRecordAsync(recordId, {
      'Playlist': name,
    })
  }
  break
}

Now, let’s walk through what we’re doing here. First, we get all the records in the “Records list” table. Note that when you are calling the selectRecordsAsync function, you want to pass in which fields you want to get back.

The line that reads "for (let record of allRecords)" is simply saying this: "Let's loop over all the rows in this table, and we'll take each one and stick it into a variable named record." You can just as easily have written "let abc of allRecords", in which each, the loop will stick each row into a variable named "abc". 

Then, once we have the list of all records, we’ll loop through them. And what we’re looking for is the record that has an owner that matches the owner returned from your fetch call. To do that, we need to get the text of the owner field from each row. Which we do with the line that reads "let recordOwner = record.getCellValueAsString("Owner")". Now the recordOwner variable will contain whatever is in the owner field as we loop through each row in the table.

Then the “if” statement is comparing the owner in each row of the table (recordOwner) with the owner returned by the fetch. And if it finds a match, then it updates that one record with the new Playlist info. Note that I may not fully understand your use-case, but hopefully what I’m describing makes sense. Also, further complicating your specific example is that you have a table of records, which I assume contains actual music records, which is cool. But the word “record” also means a row in an Airtable table. So the wording can get a little mixed up.

Anyway, after we found the row where the owner matches, then we do the update. And notice that we only updated the Playlist entry. No need to update the Owner field because we don’t need to change it. When you do an update like this, you only need to specify the fields that are actually changing.

And then after the update, we “break” from the loop. In other words, there is no need to continue looping though all records - we already found the owner. So we can speed up the code by breaking from the loop.

Now a great question to ask yourself here is this: “what if there is more than one possible match”? Well, that’s where you need to think through your use case. Because the code above will only update the FIRST owner match. If you remove the break, then the code above will update that playlist for EVERY owner match - which may, or may not, be what you want.

But hopefully this gives you a brief overview of finding and updating records using code in Airtable. Notice how short the code is. Yes, I include a lot of written text here - but only to help acclimate you to the concepts. Once you get a few of these under your belt, you’ll be flying on your own.

Also, don’t hesitate to play around in the code. Drop in “console.log” statements all over the place so you can see exactly what is happening. You’ll soon find yourself saying, “ohhh, that’s how that works.”

And, don’t hesitate to reach back into the community with any questions you have. The combination of Airtable and coding is incredibly powerful. It opens a world of opportunities for you.


Wow. Thanks again.

Do I need to declare recordId as a variable prior to use? Why/why not?

A
Forum|alt.badge.img+4
  • ag314
  • Inspiring
  • May 23, 2023

Wow. Thanks again.

Do I need to declare recordId as a variable prior to use? Why/why not?


For the first example, yes, you need to declare it like this, at the top of your code:

 

let inputData = input.config()
let { recordId } = inputData

You could combine that into a single line like “let recordId = input.config().recordId” but you’ll want to get into the habit of using “clean” code that is easier to read and debug. Plus, as you get into more complex workflows where you have more than one input variable, you’ll be able to get them all much more efficiently - known as "destructuring" in coding terms.

As to WHY you need it: Well, since you are passing recordId into the script, the script needs a mechanism for how to use it. And the “input.config()” is a method for getting all the input variables so that you can use them in your code.

For the second example, you don’t need the input variable at all. No input variable on the left side of the code editor, and no input.config() at the top. The reason is that we’ll be looping through all the rows to find the one we want to update - so there is no need for any recordId.

As you think through future use-cases, consider whether you can know the exact row that needs to be updated (e.g Example 1). If so, this will be much faster than needing to loop through every row in your table to find the one that needs updating (e.g Example 2).

With small tables, you won’t notice a difference. But once your tables have hundreds or thousands of rows - then knowing the exact row to update ahead of time will be much faster than looping through all of them.

Hopefully this all makes sense. And again, don't be afraid to play around in the code. See what pops out as you try different things. Just be careful when it comes to the updateRecord and createRecord stuff - because those WILL make changes to your tables. So, when playing around, use tables with dummy data so that you don't care if you accidentally erase all of them. And once your code works as you expect in a dummy table, then you can change the table reference to your "real" table.

D
Terms & ConditionsAccessibility statement