Creating a script to add a record to Airtable

Hi,

I am really keen to create a script that would let a user add a record to (and possibly amend a record within?) my Airtable database from within Drafts.

I found the action below in the Drafts Directory which allows easy interaction with the Airtable API:

However, I haven’t the first idea how to write the script which would come next and allow the user to use the above script.

Would anyone be able to help by mocking up a basic script for me? Or additionally/alternatively point me in the direction of example scripts/resources that I might find useful?

I am new to JavaScript (can you tell? :wink:), and I have a feeling that this is quite an ambitious project.

Thanks enormously for any help given,

Martin

Have you read through the script information on the page you like to. There’s an example script at the end.

1 Like

@sylumer You’re quite right - I shall use that as a starting point; however, I’ve already run into an issue: despite entering a valid API key and endpoint, I’m getting a 404: Not Found error. I assume the endpoint is the same as the base ID?

Thanks,

Martin

Endpoints for web-based application programming interfaces (API) are the parameter-less access URLs used to access the service. Based on what I see in my own AirTable bases (I don’t currently use any with Drafts) I think they are constructed like this:

https://api.airtable.com/v0/{base ID}/

For example, if I was interacting with a table called “Fruits” in a base with an ID of “appnABcDefgHiJklm” I would use, according to the base’s API information, https://api.airtable.com/v0/appnABcDefgHiJklm/Fruits.

The API Key is then a separate string that is used for the authentication, and again is available from the API documentation for the base (tick ‘show API key’).

1 Like

@sylumer That’s what I understood, too. I’m really confused as to why it’s not working.

I have multiple workspaces in Airtable. In one of them I have one base (Inventory) with one table (Main).

I have copied the API key directly from my account, and I have copied the base ID for Inventory directly from my account. I then paste them into the fields in the dialogue box that pops up when the Airtable API script runs (adding /Main after the base ID to reference the table).

I’m still getting the error message. I’ve even tried regenerating the API key.

I’m running the example script (unaltered) on the Drafts Directory page immediately after the Airtable API script, but I don’t think this should make a difference when establishing my credentials with Airtable.

Help!

Martin

I’m not an AirTable user and do not have any familiarity with their API or this example, but it sounds like you are saying you are including the table name in the “Endpoint” you put in the credentials, and my reading of the docs for that action say you should not. I would expect you enter:

Endpoint: https://api.airtable.com/v0/BASE-ID/
API key: YOUR-API-KEY

As they would be copied from the Airtable’s site.

I believe per the example script, you create an ATTable with the table name and I assume that is getting appended to the URLs for you by the scripts.

1 Like

@agiletortoise I had made a mistake in not adding the correct details to my follow-on script as per your final paragraph.

Many thanks,

Martin

1 Like

Hi,

I have written a script (below) which lets a user add records to Airtable via Drafts; however, I’ve run into two issues which I would appreciate some help with.

First of all, trying to set the Tags field in a new record within the Equipment base is not working. It is a multiselect field, and I suspect the API is not happy with what it’s being sent. Copying the result of this statement to the clipboard results in, e.g. ,tech, tools. It may be that the leading comma is confusing the API. I’ve discovered that adding {typecast:true} should help, but I think I’ve put it where it belongs, and it doesn’t seem to solve the problem.

Finally, I can’t seem to get the script to write accurately to the console upon successful execution (i.e. if Airtable is called, either after the first prompt, or at the end of the script).

let basename = "";
let cb = CallbackURL.create();
cb.baseURL = "airtable://";

(() => {

loop:
  while(true) { 
    let p = Prompt.create();
    p.addButton("Add to existing record");
    p.addButton("Create a new record");
    
    if (!p.show()) {
      context.cancel();
      return;
    }
    switch (p.buttonPressed) {
      case "Add to existing record":
app.setClipboard(draft.content);
      cb.open();
        break loop;
        break;
      
      case "Create a new record":
        if (chooseBase()) break loop;
        break;
    }
  }
})()

function chooseBase() {

loop:
  while(true) { 
    let p = Prompt.create();
    p.addButton("Add to Equipment");
    p.addButton("Add to Inventory");
    
    if (!p.show()) {
      context.cancel();
      return;
    }
    switch (p.buttonPressed) {
      case "Add to Equipment": basename = "Equipment"; addRecord();
    break loop; break;
      
      case "Add to Inventory": basename = "Inventory"; addRecord();
    break loop; break;
    }
    }
    }

function addRecord(){

var options = ["accessories", "apple", "bagsandcontainers", "cables", "chargers", "children", "clothes", "healthandhygiene", "luxury", "photography", "stationery", "tech", "tools"];
var selectedOptions = [""];
var base = ATBase.create(basename);
var main = ATTable.create("Main", base);
var newRecord = ATRecord.create();

  if (basename == "Equipment") {
    let p1 = Prompt.create();
  p1.addTextField("name", "Name:", "");
  p1.addTextField("attachments", "Attachments:", "");
  p1.addSelect("tags", "Tags:", options, selectedOptions, true);
  p1.addSwitch("edc", "EDC:", false);
  p1.addSwitch("purchased", "Purchased:", false);
  p1.addTextField("notes", "Notes:", "");
  
  p1.addButton("OK");

  
      if (!p1.show()) {
      context.cancel();
      return;
    }

newRecord.setFieldValue("Name", p1.fieldValues['name']);
newRecord.setFieldValue("Attachments", p1.fieldValues['attachments']);
newRecord.setFieldValue("Tags", p1.fieldValues['tags',{"typecast":true}]);
newRecord.setFieldValue("EDC", p1.fieldValues['edc']);
newRecord.setFieldValue("Purchased", p1.fieldValues['purchased']);
newRecord.setFieldValue("Notes", p1.fieldValues['notes']);}

  else if (basename == "Inventory") {
    let p2 = Prompt.create();
  p2.addTextField("name", "Name:", "");
  p2.addTextField("model", "Model:", "");
  p2.addTextField("purchasedate", "Purchase Date:", "DD/MM/YYYY");
p2.addTextField("purchaselocation", "Purchase Location:", "");
  p2.addTextField("warranty", "Warranty:", "");
  p2.addTextField("serialnumbers", "Serial Number(s):", "");
  p2.addTextField("notes", "Notes:", "");
   p2.addTextField("manuals", "Manual(s):", "");
   
   p2.addButton("OK");

   
   if (!p2.show()) {
      context.cancel();
      return;
    }
    
    newRecord.setFieldValue("Name", p2.fieldValues['name']);
newRecord.setFieldValue("Model", p2.fieldValues['model']);
newRecord.setFieldValue("Purchase Date", p2.fieldValues['purchasedate']);
newRecord.setFieldValue("Purchase Location", p2.fieldValues['purchaselocation']);
newRecord.setFieldValue("Warranty", p2.fieldValues['warranty']);
newRecord.setFieldValue("Serial Number(s)", p2.fieldValues['serialnumbers']);
newRecord.setFieldValue("Notes", p2.fieldValues['notes']);
newRecord.setFieldValue("Manual(s)", p2.fieldValues['manuals']);}

main.addRecord(newRecord);
main.update();
cb.open();

return true;

  if (success) {
    console.log("Item added to Airtable");
  } else {
    console.log(cb.status);
    if (cb.status == "cancel") {
      context.cancel();
    } else {
      context.fail();
    }
  }
}

Thanks for any help you can offer,

Martin

Kind of tricky to help troubleshoot…on a quick look…

// this line isn't quite right to get the field values
newRecord.setFieldValue("Tags", p1.fieldValues['tags',{"typecast":true}]);
// try this...
newRecord.setFieldValue("Tags", p1.fieldValues['tags']);

That would be getting the array of selected tags. I’m not sure about the {"typecast":true} part or if the AirTable API action supports passing along those values…but, if it does, that should be outside the end bracket to get the value from the p1 prompt.

1 Like

@agiletortoise Thanks for this.

Airtable’s documentation states that:

Airtable API performs automatic data conversion from string values if typecast parameter is passed in. Automatic conversion is disabled by default to ensure data integrity, but it may be helpful for integrating with 3rd party data sources.

When the parameter is inserted where I have it in the script, then the script will execute; however, the contents of the tags field are ignored. If I remove the parameter entirely then I get a 422: Invalid Request error and the script fails. The API needs the data to be entered into a multiselect field to be formatted, e.g. item1,item2,item3. The two possible solutions I have identified are: remove the trailing comma from the output in Drafts, or use the typecast parameter to get the API to tidy things up. I suppose a third alternative would be to forget the multiselect prompt and just go with a simple text box, but this would be less user-friendly and the available tags would not be visible at the prompt.

The value return from a select in a prompt is not a string. It is an array of values. My example was to demonstrate you were not getting the value from the prompt correctly. If you want is as a comma-separated string, you would need to convert it yourself, like…

newRecord.setFieldValue("Tags", p1.fieldValues['tags'].join(","));

I don’t know what that wrapper JS library does from the AirTable API action does with values, but I doubt it is setup to pass those additional parameters to the actual HTTP calls to AirTable.

1 Like

@agiletortoise I think I’m muddying the waters here. My apologies.

A multiselect field in the Airtable API expects an array of strings.

If I were to rewrite my code as, e.g.

newRecord.setFieldValue("Tags", [“cables”, “tech”]);

then the script executes fine, adding cables and tech as tags in my base.

Please can you explain to me how to convert the output from the prompt (,item1,item2) into the correct format for the API ([“item1”, “item2”])?

Did you try newRecord.setFieldValue("Tags", p1.fieldValues['tags']);?

That should be an array.

1 Like

@agiletortoise That’s what I currently have, but it throws up an error. Could it be because the API is expecting an array of strings, but the prompt is generating a different array? Or am I missing something?

Apologies for the limited answers. Not at a computer, but still trying to but helpful.

1 Like

@agiletortoise You’re being extremely helpful! I think we might be getting somewhere - I suspect I just need to convert the output from the prompt to a format friendly to the API. I’ll experiment and report back!

I looked back over this code, and here is your problem:

var selectedOptions = [""];

You are starting the selectedOptions with an empty string value, not an empty array. That value remains in the selectedOptions value, so after you call the prompt, you end up with something like ["", "cables", "text"] in there and I assume empty string is not a valid value for your field in Airtable. Change that line to:

var selectedOptions = [];
1 Like

That’s absolutely spot on - problem solved! :slightly_smiling_face:

Any idea why a successful execution of the script doesn’t write to the console?

Many thanks,

Martin

There’s a return true; line in your function that exits the function just before your if (success)... block - to the code never makes it to that to run.

@agiletortoise I can’t really explain the behaviour of the console with this script. Sometimes it tells me that the script has been cancelled even when I know it has been successful, other times the console is not updated at all. Occasionally, the console is updated correctly upon successful execution, but only a few minutes after execution has occurred. Also, invariably upon returning to Drafts from Airtable, all the actions in the app stop working.

Any help appreciated!

Martin