Omni Automation and Airtable

What is Airtable? “At its core, Airtable allows you to easily create a database that holds the information that matters for your work, then use it to power the visualizations, processes, and integrations that make up a custom application that’s truly unique to you.”

One of the most basic, yet most functional representation of Airtable databases, is as a standard table of data, similar to what you may see in a spreadsheet application like Numbers from Apple.

The following documentation details how to use Omni Automation with Airtable to implement a standard publishing solution involving OmniGraffle.

DISCLAIMER: Mention of third-party websites and products is for informational purposes only and constitutes neither an endorsement nor a recommendation. OMNI-AUTOMATION.COM assumes no responsibility with regard to the selection, performance or use of information or products found at third-party websites. OMNI-AUTOMATION.COM provides this only as a convenience to our users. OMNI-AUTOMATION.COM has not tested the information found on these sites and makes no representations regarding its accuracy or reliability. There are risks inherent in the use of any information or products found on the Internet, and OMNI-AUTOMATION.COM assumes no responsibility in this regard. Please understand that a third-party site is independent from OMNI-AUTOMATION.COM and that OMNI-AUTOMATION.COM has no control over the content on that website. Please contact the vendor for additional information.

Tagged-Container Publishing

Tagged-Container Publishing is an often used technique to publish information, by using automation to connect a data source with an application that creates layouts. In this scenario, the receiving containers in a design layout (ex: text or image shapes) are marked or “tagged” with some sort of unique “identifier” that it used by the source database to represent a specific record or data set. For an example, in the case of real estate, an MLS (Multiple Listing Service) number, or in the case of individuals, a Social Security or employee number.

Once a container is “tagged” with a unique identifier, another script or automation tool can extract the assigned identifier from the container and use it to locate the corresponding record in the source database, and then extract and download the data in the record’s fields, to populate the receiving tagged container with the extracted data.

In such a scenario, Omni Automation script URLs embedded in a source Airtable database, are triggered by the user to target a selected graphic shape in an OmniGraffle layout, in order to assign or tag them with name of the source Airtable table and the record ID (identifier) of a specific product whose information and image are to be transfered to the selected shape.

Once the receiving graphic shapes (containers) in the design layout are “tagged,” an Omni Automation OmniGraffle plug-in is executed that will iterate the selected graphic shapes, and one-by-one, use their assigned identifiers to locate, download, and assign the data from their corresponding Airtable record to the metadata storage of each OmniGraffle graphic shape, including each related product image file.

A second plug-in is then executed that extracts the locally stored product description and creates a similar sized text box, containing the description text, to the right, left, or below of each selected graphic shape.

Here’s a video demonstrating the process:

Tagged-Container Publishing with OmniGraffle and Airtable.

Graphic Tagging Function

The first step in developing this tagged-container solution, is to create an Omni Automation self-executing function with passed-in data, that can be converted into a “security-approved” function that will tag a selected OmniGraffle shape with the identifying metadata. Such a function will later be encoded for use as a script URL that gets assigned to a field in the source Airtable database table as a formula.

The following code snippet is a self-executing function that will tag the selected graphic shape with the tags and values provided as an argument to the function.

Self-Executing “Approvable” Tagging Function


(function setKeyAndValueForSelectedGraphic(argObject){
	graphics = document.windows[0].selection.graphics
	if (graphics.length == 1){
		objKeys = Object.keys(argObject)
		objKeys.forEach(key => {
			graphics[0].setUserData(key, argObject[key])
		})
	} else {
		alert = new Alert('SELECTION ERROR','Please select a single graphic.')
		alert.show()
		throw new Error('No selected item')
	}
})(argument)

Using the encoding tools provided by this website, the function is converted into the following Omni Automation script URL:

Encoded Script URL


omnigraffle://localhost/omnijs-run?script=%28function%20setKeyAndValueForSelectedGraphic%28argObject%29%7B%0A%09graphics%20%3D%20document%2Ewindows%5B0%5D%2Eselection%2Egraphics%0A%09if%20%28graphics%2Elength%20%3D%3D%201%29%7B%0A%09%09objKeys%20%3D%20Object%2Ekeys%28argObject%29%0A%09%09objKeys%2EforEach%28key%20%3D%3E%20%7B%0A%09%09%09graphics%5B0%5D%2EsetUserData%28key%2C%20argObject%5Bkey%5D%29%0A%09%09%7D%29%0A%09%7D%20else%20%7B%0A%09%09alert%20%3D%20new%20Alert%28%27SELECTION%20ERROR%27%2C%27Please%20select%20a%20single%20graphic%2E%27%29%0A%09%09alert%2Eshow%28%29%0A%09%09throw%20new%20Error%28%27No%20selected%20item%27%29%0A%09%7D%0A%7D%29%28argument%29

Note that the schema of the script URL targets the OmniGraffle application (omnigraffle://). This type of schema is supported by Airtable.

This encoded function will eventually be adapted into a field formula that is used in the Airtable table as the formula assigned to a specific field.

Passed-In Argument for Tagging Function

In the example scenario, the passed-in argument will consist of a JavaScript object containing two table identifiers:

The name of the source Airtable table
The record ID of the corresponding table for the product whose data will be used to populate the graphic and its metadata structures.

Both of these elements will be associated with user-defined tags, in this case: AT-TB-NAME (Airtable table name) and AT-REC-ID (Airtable table record identifier)

Here is the argument object with placeholders used for the values. (Object keys are green and placeholder values are orange)

Argument Object to be Appended to Script URL


{"AT-TB-NAME":"XXXXX", "AT-REC-ID": "YYYYY"}

Using the provided encoding tools, the argument object is encoded to enable it to be appended to the previous encoded script URL:

Encoded Script Argument Object


&arg=%7B%22AT-TB-NAME%22%3A%22XXXXX%22%2C%20%22AT-REC-ID%22%3A%20%22YYYYY%22%7D

Note that during the encoding process, the encoded object is preceded with the script URL header of: &arg= This is done in preparation for the encoded argument to be appended to the encoded script URL.

Converting Encoded Script into Table Formula

The next step is to adapt the encoded script URL into a formula used by table cells in the Airtable database.

After appending the encoded argument to the previously encoded script URL, perform the following actions with the resulting URL:

Replace XXXXX with Airtable formula for retrieving encoded table name
Replace YYYYY with Airtable formula for retrieving record ID
Encase the encoded script sections within quotes and parens: ("…")
NOTE: Paren sections are concatenated with ampersand characters: &

The resulting formula will look similar to this:

Encoded Tagging Function as Table Cell Formula


("omnigraffle://localhost/omnijs-run?script=%28function%20setKeyAndValueForSelectedGraphic%28argObject%29%7B%0A%09graphics%20%3D%20document%2Ewindows%5B0%5D%2Eselection%2Egraphics%0A%09if%20%28graphics%2Elength%20%3D%3D%201%29%7B%0A%09%09objKeys%20%3D%20Object%2Ekeys%28argObject%29%0A%09%09objKeys%2EforEach%28key%20%3D%3E%20%7B%0A%09%09%09graphics%5B0%5D%2EsetUserData%28key%2C%20argObject%5Bkey%5D%29%0A%09%09%7D%29%0A%09%7D%20else%20%7B%0A%09%09alert%20%3D%20new%20Alert%28%27SELECTION%20ERROR%27%2C%27Please%20select%20a%20single%20graphic%2E%27%29%0A%09%09alert%2Eshow%28%29%0A%09%09throw%20new%20Error%28%27No%20selected%20item%27%29%0A%09%7D%0A%7D%29%28argument%29&arg=%7B%22AT-TB-NAME%22%3A%22") & ENCODE_URL_COMPONENT("REPLACE-WITH-TABLE-NAME") & ("%22%2C%20%22AT-REC-ID%22%3A%20%22") & RECORD_ID() & ("%22%7D")

Copy this example and add it as formula for a new table field titled: LINK
Next, replace the placeholder REPLACE-WITH-TABLE-NAME with the name of the Airtable table containing the new field.

Once added to the Airtable tabel as a field formula, simply clicking or tapping the script URL in the Airtable interface will automatically trigger the process of transferring the identifying tags and value to the selected OmniGraffle shape.

NOTE: For security purposes you’ll need to enable the execution of remote scripts in OmniGraffle and provide a one-time approval of the initial script execution. This topic is detailed completely in the Script Security section of this website.

Plug-In for Retrieving Airtable Data for Tagged Shape

This publishing example incorporates the use of the following plug-ins (DOWNLOAD):

Clear Contents and Metadata • Removes the text/image contents of a shape along with any assigned metadata.
Place Notes Text • Creates and positions a duplicate text container of the selected shape and fills it with the assigned notes text of the source shape.
Update Metadata for Shape • This plug-in uses assigned metadata tags to locate a corresponding Airtable record and download its contents to the shape and its metadata storage.

The Update Metadata for Shape plug-in uses the metadata of the selected graphics to identify, download, and store metadata from related Airtable table records. It incorporates the use of the URL.FetchRequest and URL.FetchResponse classes (documentation) to communicate with Airtable via the internet.

This plug-in also incorporates the Preferences class (documentation) to store and retrieve settings and the required Airtable API and Airtable Database (App) keys, which you enter when you first use the plug-in. NOTE: if you wish to change stored keys, select the plug-in from the Automation Menu while holding down the Control key.

IMPORTANT: This plug-in uses the an AirTable API Token in place of the deprecated API Key.

Retrieve and Store Metadata for Tagged Shapes
 

/*{
	"type": "action",
	"targets": ["omnigraffle"],
	"author": "Otto Automator",
	"identifier": "com.omni-automation.og-at.update-selected-graphics",
	"version": "1.1",
	"description": "Uses the metadata of the selected graphics to identify and download and store metadata from related AirTable table records.",
	"label": "Update Metadata for Graphics",
	"shortLabel": "Update Metadata",
	"paletteLabel": "Update Metadata",
	"image": "square.and.arrow.down.on.square"
}*/
(() => {
	const preferences = new Preferences()
		
	const action = new PlugIn.Action(async function(selection, sender){
		try {
			if (app.controlKeyDown){
				var errorID = "A"

				APIKey = preferences.readString("APIKey")

				var APIKeyInput = new Form.Field.String(
					"APIKey",
					"API Key",
					APIKey,
					null
				)

				AppID = preferences.readString("AppID")

				var AppIDInput = new Form.Field.String(
					"AppID",
					"App ID",
					AppID,
					null
				)

				inputForm = new Form()
				inputForm.addField(APIKeyInput)
				inputForm.addField(AppIDInput)
				formPrompt = "Enter AirTable API Token and App ID:"
				formObject = await inputForm.show(formPrompt, "Continue")

				newAPIKey = formObject.values["APIKey"]
				newAppID = formObject.values["AppID"]
				
				preferences.write("APIKey", newAPIKey)
				preferences.write("AppID", newAppID)
			} else {
				var errorID = "B"
				
				var APIKey = preferences.readString("APIKey")
				if(!APIKey || APIKey === ""){
					throw {
						name: "Undeclared API Token",
						message: "An AirTable API Token has not yet been provided for this plug-in.\n\nRun this plug-in again, while holding down the Control key, to summon the preferences dialog."
					}
				}

				var AppID = preferences.readString("AppID")
				if(!AppID || AppID === ""){
					throw {
						name: "Undeclared App ID",
						message: "An AirTable App ID has not yet been provided for this plug-in.\n\nRun this plug-in again, while holding down the Control key, to summon the preferences dialog."
					}
				}
				//console.log("Pref-AppID:", AppID)
				
				const shapes = document.windows[0].selection.solids
				const docView = document.windows[0].selection.view
				
				// TABLE AND RECORD IDENTIFIERS
				const MetaKeyRecID = "AT-REC-ID"
				const MetaKeyTblNme = "AT-TB-NAME"				
				
				// Iterate without forEach(…) which is not asynchronous 
				for (const shape of shapes){
					docView.select([shape])

					// RETRIEVE TAGGED METADATA TO CONSTRUCT AirTable URL
					metaData = shape.userData

					itemTblNme = metaData[MetaKeyTblNme]
					if(!itemTblNme){
						throw {
							name: "Missing Metadata",
							message: `The selected shape has not been tagged with an AirTable table name.`
						}
					}

					itemRecID = metaData[MetaKeyRecID]
					if(!itemRecID){
						throw {
							name: "Missing Metadata",
							message: `The selected shape has not been tagged with an AirTable record ID.`
						}
					}

					// GENERATE AirTable URL
					itemTblNme = encodeURIComponent(itemTblNme)
					ATURL = `https://api.airtable.com/v0/${AppID}/${itemTblNme}/${itemRecID}`

					// CREATE NEW FETFCH REQUEST
					fetchRequest = URL.FetchRequest.fromString(ATURL)

					fetchRequest.method = 'GET'
					fetchRequest.cache = "no-cache"
					fetchRequest.headers = {"Authorization": "Bearer" + " " + APIKey}

					// WAIT FOR THE RESPONSE FROM AirTable
					response = await fetchRequest.fetch()
					
					statusCode = response.statusCode
					if(statusCode >= 200 && statusCode < 300){
						tableJSON = JSON.parse(response.bodyString)

						tableID = tableJSON["id"]
						tableDate = tableJSON["createdTime"]
						shape.setUserData("AT-REC-DATE", tableJSON["createdTime"])

						// POPULATE NAME, NOTES, and FIELDS
						fieldsObj = tableJSON["fields"]
						shape.name = fieldsObj["NAME"]
						shape.notes = fieldsObj["DESCRIPTION"]
						shape.setUserData("AT-FLD-MAN", fieldsObj["MANUFACTURER"])
						shape.setUserData("AT-FLD-SKU", fieldsObj["SKU"].toString())
						shape.setUserData("AT-FLD-PRC", fieldsObj["PRICE"].toString())
						shape.setUserData("AT-FLD-SUM", fieldsObj["SUMMARY"])

						// POPULATE IMAGE METADATA
						imageDataArray = fieldsObj['IMAGE']
						imageObject = imageDataArray[0]
						shape.setUserData("AT-IMG-FNME", imageObject['filename'])
						shape.setUserData("AT-IMG-URL", imageObject['url'])
						shape.setUserData("AT-IMG-ID", imageObject['id'])
						shape.setUserData("AT-IMG-HORZ", imageObject['width'].toString())		 
						shape.setUserData("AT-IMG-VERT", imageObject['height'].toString())
						shape.setUserData("AT-IMG-SIZE", imageObject['size'].toString())
						shape.setUserData("AT-IMG-TYPE", imageObject['type'])

						// IMPORT IMAGE FILE
						imageURL = URL.fromString(imageObject['url'])
						imageURL.fetch(function(shape, data){
							shape.image = addImage(data)
						}.bind(this,shape))
					} else {
						throw {
							name: "Data Retrieval Error",
							message: `A ${statusCode} status code was returned from the query for record ID: ${itemRecID}`
						}
					}
				}
				docView.select(shapes)
				shapeCount = shapes.length
				shapeOrShapes = (shapeCount === 1) ? "shape":"shapes"
				hasOrHave = (shapeCount === 1) ? "has":"have"
				alertMessage = `The metadata for ${shapeCount} tagged ${shapeOrShapes} ${hasOrHave} been updated.`
				new Alert("Update Completed", alertMessage).show()
			}
		}
		catch(err){
			if(errorID !== "A"){
				new Alert(err.name, err.message).show()
			}
		}
	});

	action.validate = function(selection, sender){
		return (document.windows[0].selection.solids.length > 0)
	};

	return action;
})();