Skip to main content

Hello, AirTable forum!

I am trying to figure out how to update an AirTable, using the Scripting extension and drawing from an external API (LegiScan). The issue I am running into is that first, I am incredibly new to JavaScript, and second, single- and multi-select options are causing me some trouble. I have reviewed a number of posts in this forum about APIs, as well as the "Cell values and field options" documentation (https://airtable.com/developers/scripting/api/cell_values) but I cannot figure a solution in this case--this is likely my newness to and lack of skills with JavaScript! (I previously mainly coded in R, which is...not the same.)

My code can pull the data (and reformat a date), but I cannot update any field that is single- or multi-select, and I am also struggling to declare multiple fields to update (this is, I think, just an issue tied in with the single- and multi-select).

As an example of my annotated code (my API token has been removed for privacy) that shows how I am trying to update the already-existing last updated single-select field of a particular bill in my AirTable:

// Setting up API
let apiToken = 'myAPItoken'

//Setting it to link to a particular table
let table = base.getTable('Test')

let { records } = await table.selectRecordsAsync()


// Setting up the call as a "for" loop
// For updating bill info
for (let record of records) {
    let billUpdate = await remoteFetchAsync(`https://api.legiscan.com/?key=${apiToken}&op=getBill&id=${record.name}`)

// Has to be in json format for AirTable 
    let data = await billUpdate.json()

// Reformatting the date so it matches the format I want it to be 
    let status_date = new Date(data.bill.status_date)
    let lastAction = (status_date.toLocaleDateString("en-US"))

// Updating a field/add to the table: Updating the last date of something happening
await table.updateRecordAsync(record, {
    'Last Action': lastAction
})
}

The error message I am getting is:

ERROR

j: Can't set cell values: invalid cell value for field 'Last Action'.
Cell value has invalid format: <root> must be an object
Single select field value must be an object with at least one of 'id' or 'name' as a property.
at main on line 21

I have the feeling this is related to how I am updating the table, but I've spent a few days on this, and I just can't figure the solution. Thank you in advance for your help!

 

Hello @rinewithoutacat ,

You can check out the API documentation from the Help section.
There are lots of helpful hints and tips.

Learning a new language can be tricky, and learning how to read documentation can be tricky too. The documentation page you linked to gives the "write" format for each field type. A single select needs to be updated with an object. A multiple-select field needs to be updated with an array of objects. For example, {name: "Done"} to update a single select to the value "Done". Or [{name: "red"}, {name: "white"}, {name: "blue"}] to update a multiple select with three colors. Note that all select choices must already be defined for the field.

Hello @rinewithoutacat ,

You can check out the API documentation from the Help section.
There are lots of helpful hints and tips.


Apologies for the delay, was under the weather for a few days.

 

In my post, I said I checked the API documentation page and was still having trouble. Telling me to check the page that I said I was having trouble with isn't helpful for me--I am trying to figure out what's up, and I'm really putting in the time to try and figure a solution, but if I could figure it solely from that, I wouldn't be asking here.

Learning a new language can be tricky, and learning how to read documentation can be tricky too. The documentation page you linked to gives the "write" format for each field type. A single select needs to be updated with an object. A multiple-select field needs to be updated with an array of objects. For example, {name: "Done"} to update a single select to the value "Done". Or [{name: "red"}, {name: "white"}, {name: "blue"}] to update a multiple select with three colors. Note that all select choices must already be defined for the field.


Apologies for the delay, I was ill for a few days.

Thanks for responding! After reading your reply, I re-reviewed the documentation, but am still struggling. I am hoping to update a field from a variable (in this example, lastAction) and write new choices, as pulled from the API. Is this just not possible with scripting?

I thought that I would be able to update choices by 'spreading choices' (as described in the documentation, upon my re-review), but this code (below) is not resulting in any changes at all to the AirTable, even though it says 'record updated' and even if I delete all selections:

    const selectLastAction = table.getField('Last Action');
    await selectLastAction.updateOptionsAsync({
        choices: [
            ...selectLastAction.options.choices,
            {name: `{LastAction}`},
        ],
    });

I am not good at Javascript either, so I wrote this as a learning experience.
Test code for scripting extensions.
note that the max number of single select options is 10000.

 

let newSelect = new Date(Date.now()).toLocaleDateString("en-US");



const table = base.getTable("TEST Table");

const selectField = table.getField("SingleSelect");

await selectField.updateOptionsAsync({

choices: [

...selectField.options.choices,

{name: newSelect}

],

});

let query = await table.selectRecordsAsync({fields: ["SingleSelect"]});

let recordId = query.records[0].id;

await table.updateRecordAsync(recordId, {

"SingleSelect":{name:`${newSelect}`},

})

 

 In this case, however, it may be better not to use a "single select field.
A process will be needed to consolidate duplicate values.

Apologies for the delay, I was ill for a few days.

Thanks for responding! After reading your reply, I re-reviewed the documentation, but am still struggling. I am hoping to update a field from a variable (in this example, lastAction) and write new choices, as pulled from the API. Is this just not possible with scripting?

I thought that I would be able to update choices by 'spreading choices' (as described in the documentation, upon my re-review), but this code (below) is not resulting in any changes at all to the AirTable, even though it says 'record updated' and even if I delete all selections:

    const selectLastAction = table.getField('Last Action');
    await selectLastAction.updateOptionsAsync({
        choices: [
            ...selectLastAction.options.choices,
            {name: `{LastAction}`},
        ],
    });

Try breaking down your script into smaller piece. Write a script that does only one thing. Start with hardcoded values and get that to work. Then try doing it with variables. Then write a simple script that does one other thing and get it to work. Then try putting the pieces together.  Try using console.log() to see the values of your variables to debug.

For example, write a script that just shows the existing choices for the select field. Write a script that creates a new record and sets the select field value. Write a script that sets the value of the select field for an existing record. Write a script that sets the values of two fields at the same time, etc.

Reply


Terms & Conditions