Omni Automation and the Numbers App

Although the Numbers app on iOS does not offer support for automation through a scripting interface, the “=HYPERLINK()” formula function can be used to trigger an Omni Automation script URL that includes data dynamically generated in a table.

For example, as the following video demonstrates, column headers and their corresponding value sums can be sent to OmniGraffle to be used as labels in an illustration:

You can download the example NUMBERS file and the example OMNIGRAFFLE file.

The “=HYPERLINK” Reference Function

The formula for the cell containing the link to OmniGraffle extracts the contents of the header and sum cells for each column and inserts their values into a percent-encoded Omni Automation script that is used as the value for the “=HYPERLINK” formula function:

table-with-formula

Here is Apple’s documentation for the HYPERLINK function:

The HYPERLINK function creates a clickable link that opens a webpage or new email message.

HYPERLINK(url, link-text)

  • url: A standard universal resource locator. url is a string value that must contain a properly formatted universal resource locator string.
  • link-text: An optional string value that specifies the text that appears as a clickable link in the cell. If link-text is omitted, url is used as link-text.

Examples

  • =HYPERLINK("http://www.apple.com", "Apple") creates a link with the text “Apple” that, when clicked, opens the default web browser to the Apple homepage.
  • =HYPERLINK("mailto:janedoe@example.com?subject=Quote Request", "Get Quote") creates a link with the text Get Quote that, when clicked, opens the default email application and addresses a new message to janedoe@example.com with the subject line Quote Request.

This function can be adapted for use with Omni Automation to extract data from the source table and insert the extracted data into an Omni Automation script that is executed when the link is activated via a TAP|CLICK.

The Omni Automation script

The Omni Automation script used in this example iterates the passed-in array of strings to create new text labels for each on the current canvas in the OmniGraffle document. In the actual script, the XXXXX placeholders are replaced with generated text strings containing the column title, a space, and then the SUM value of the column, like this: 'Solar 2345'

var textItems = ['XXXXX','XXXXX','XXXXX','XXXXX'] var cnvs = document.windows[0].selection.canvas var typeSize = 24 for(i = 0; i < textItems.length; i++){ position = new Point(100,(2* typeSize)*(i+1)) g = cnvs.addText(textItems[i],position) g.autosizing = TextAutosizing.Full g.textSize = typeSize }
omnigraffle://localhost/omnijs-run?script=var%20textItems%20%3D%20%5B%27XXXXX%27%2C%27XXXXX%27%2C%27XXXXX%27%2C%27XXXXX%27%5D%0Avar%20cnvs%20%3D%20document%2Ewindows%5B0%5D%2Eselection%2Ecanvas%0Avar%20typeSize%20%3D%2024%0Afor%28i%20%3D%200%3B%20i%20%3C%20textItems%2Elength%3B%20i%2B%2B%29%7B%0A%09position%20%3D%20new%20Point%28100%2C%282*%20typeSize%29*%28i%2B1%29%29%0A%09g%20%3D%20cnvs%2EaddText%28textItems%5Bi%5D%2Cposition%29%0A%09g%2Eautosizing%20%3D%20TextAutosizing%2EFull%0A%09g%2EtextSize%20%3D%20typeSize%0A%7D

The Link Cell Formula

The formula of the table cell that contain the Omni Automation script link uses the Numbers HYPERLINK function with a value that is a combination of segments of an encoded Omni Automation script, interspersed with nested functions for extracting the contents of the header and SUM cells.

Note the use of two other formula functions: SUBSTITUTE which is used in this example to replace spaces with the percent encoding %20, and PLAINTEXT which is used to get a cell’s content as plain text.

These formula functions are nested like this: SUSTITUTE(PLAINTEXT(CellID)," ","%20")

=HYPERLINK("omnigraffle:///omnijs-run?script=var%20textItems%20%3D%20%5B%27" & SUBSTITUTE(PLAINTEXT(A1)," ","%20") & "%20" & PLAINTEXT(A6) & "%27%2C%27" & SUBSTITUTE(PLAINTEXT(B1)," ","%20") & "%20" & PLAINTEXT(B6) & "%27%2C%27" & SUBSTITUTE(PLAINTEXT(C1)," ","%20") & "%20" & PLAINTEXT(C6) & "%27%2C%27" & SUBSTITUTE(PLAINTEXT(D1)," ","%20") & "%20" & PLAINTEXT(D6) & "%27%5D%0Avar%20cnvs%20%3D%20document%2Ewindows%5B0%5D%2Eselection%2Ecanvas%0Avar%20typeSize%20%3D%2024%0Afor%28i%20%3D%200%3B%20i%20%3C%20textItems%2Elength%3B%20i%2B%2B%29%7B%0A%09position%20%3D%20new%20Point%28100%2C%282*%20typeSize%29*%28i%2B1%29%29%0A%09g%20%3D%20cnvs%2EaddText%28textItems%5Bi%5D%2Cposition%29%0A%09g%2Eautosizing%20%3D%20TextAutosizing%2EFull%0A%09g%2EtextSize%20%3D%20typeSize%0A%7D", "OmniGraffle")
UNDER CONSTRUCTION

This webpage is in the process of being developed. Any content may change and may not be accurate or complete at this time.

DISCLAIMER