How Do I Create Variables From Clipboard in Order to Generate a Report Using a Database (Airtable) and Ulysses?

[Blakeandersonj]

Hi Keyboard Maestro fans,

I'm new here.

What I want to do, is generate a report in Ulysses from a database using Airtable. I was told by a nice fellow on YouTube, that I can use variables using the clipboard, to generate the report much easier, as I was originally planning to copy and paste from Airtable to Ulysses, which seems excessive given the lengthy report. In other words I want to copy various cells and set variables for each cell and then eventually paste that into a document report.

Now I've only a beginner when it comes to AppleScript, and I know there's a way that I might be able to do it this way, but at least at this time, this seems too advanced for me. This must be a common tasks for most people to create databases to generate reports. I’m sure it’s been asked before. So I'm hoping someone can help me with this. Or at least point me in another direction. I really appreciate the help thanks. Blake.

Hello Blake. Good demo. Please know that you use variables to eliminate the back and forth between applications. Here is how you would create it.

Activate "Spreadsheet Application" 
Pause for .3 Seconds.
Copy
Set Variable "name" to Text %SystemClipboard%
      to %System Clipboard%
Type the Tab Keystroke
      Simulate keystroke Tab

Repeat those steps for the different variables (insurance name, insurance addy, policyno, client name etc.)
Then Activate Ulysses
Insert Text by pasting

Assessment Report

%Variable%ninsurance%
%Variable%naddress%

Attention:

RE: %Variable%nname%
      Claim #: %Variable%nclaimno%
      Policy #: %Variable%npolicyno%
      Date of Loss: %Variable%ndeadline%

---

This way the KM isn't constantly going back and forth between the two apps.
If you want the action, I can upload it for you. I hope this helps!

Take care,
KC

more info re variables:
https://wiki.keyboardmaestro.com/manual/Variables

https://wiki.keyboardmaestro.com/AppleScript

[gglick]

Hi Blake,

I have no doubt that someone here can help you with this task, but it's more difficult to do so when we don't know exactly what sort of data you're working with or how exactly you want to create the report. At the very least, we could use a screenshot of the Airtable document and some example text of what a finished report would look like, but even better would be a link to a read-only version of the Airtable document itself or a zipped and uploaded version after being exported to Excel. Before you post again, I also encourage you to read this and keep its points in mind: Tip: How Do I Get The Best Answer in the Shortest Time?

[kcwhat]

Hello Blake,

I was the one that responded to you on YouTube . I left a Dropbox link, to a zip file, with the demo macro and a quick video on what it does/how to do it. I’m sure there are a ton of superheroes here that can make it more elegant. Did you download and review what I provided?

You can link to your video, if you’d like as it will give the forum an idea what you need. I’m sure the forum can build on the solution I provided and/or provide another avenue.

KC

[Blakeandersonj]

Hi Kenneth,

You know I actually switched to a different YouTube channel, so must have to took that link down. Anyway you could send it to me again? Thanks again for your support. I really appreciate it. You put my on this thread to learn more about variables and Apple Script.

Yes here is that Youtube video, perhaps it will help others understand more what I’m looking for and help them in the process: https://youtu.be/3mWwfT648s0

P.s. I love Soulection radio as well. I’m listening to the Apple Soulection Playlist right now!

Best,
Blake

blake.anderson.j@gmail.com

[Blakeandersonj]

Hey Gabe. Thanks. Please check out my youtube video explaining it. Thanks: https://youtu.be/3mWwfT648s0

Best,
Blake

blake.anderson.j@gmail.com

[gglick]

Thanks, Blake. I would have preferred you explained your desired workflow with screenshots and sample data as per the link I included in my last post, but the video certainly sufficed. At any rate, there is a much, much better solution to this than what you demonstrate in the video. With this macro, you can copy every cell in a given Airtable row at once (you can click the little bumpy grab handle to the left of the checkbox to select the whole row:

and then the macro will produce a new Ulysses sheet with everything already filled out:

Results

It does this primarily by using a simple regex to process the clipboard, and Ulysses' URL scheme to create the new sheet. Here's the macro for you to download and try for yourself:

Generate Report from Airtable Row.kmmacros (2.9 KB)

In my testing, Airtable did not seem to work with KM's automated copy commands, so with this macro you still have to hit ⌘C to copy the selected row manually, but once you do that, you can just run this macro and the whole thing is done in a microsecond. Feel free to report back if you have any questions.

[kcwhat]

Blake,

I sent the link privately as in located in your KM Discourse inbox. I was going to post it here but since I'm using different applications (Numbers and TextEdit), I won't post it here. It looks like Gabe has and used the specific applications for your purpose. If you ascertain any good nuggets from my video, that would be great. Gabe's solution is solid.

KC

[ccstone]

@gglick

With this macro, you can copy every cell in a given Airtable row at once (you can click the little bumpy grab handle to the left of the checkbox to select the whole row:

Hey Gabe,

That method of selection is problematic in that the keyboard is locked-out until a new selection is made with the mouse.

⌘⇧⇢ when operated from the first cell in the row will select the row while leaving the keyboard focus in in the cell.

Using the keyboard to do the selection allows further automation (or user-interaction) to take place more easily.

I don't see a way around this at the moment, but I'm not very familiar with AirTable...

-Chris

[gglick]

Aha, I didn't realize selecting with the mouse that way locked the keyboard out like that. Thanks for pointing that out, Chris! Unfortunately Airtable still refuses to let the user copy selected cells in any way other than a manual ⌘C (in fact, Airtable actually grays out a lot of the Edit menu, for reasons I can't imagine:

so selecting a row with ⌘⇧⇢ still doesn't let KM automate the copy, but at least it does let the user take further actions more freely afterwards, so I'm happy to second this recommendation.

[ccstone]

@gglick

Unfortunately Airtable still refuses to let the user copy selected cells in any way other than a manual ⌘C

Hey Gabe,

Hmm... I hadn't noticed that in preliminary testing, but I was able to make it fail just now.

(in fact, Airtable actually grays out a lot of the Edit menu, for reasons I can't imagine:

I'm not seeing grayed-out menu items in Brave, but I noticed that manually selecting Copy from the Edit menu didn't necessarily work.

Weird. Someone needs to report this as a nasty bug...

Okay – this is working pretty reliably for me.

Copy Data From AirTable to BBEdit v1.00.kmmacros (6.9 KB)

The pause duration may need adjusting for other Macs and set-ups.

-Chris

[gglick]

Aha, I'd forgotten I could use System Events like that. Thanks again, Chris! With that missing piece filled in, I was able to make a version that is fully automatic:

Generate Report from Airtable Row 1.1.kmmacros (5.2 KB)

I included a ⌘← keystroke at the top so that the user could run the macro from anywhere in the row, eliminating the need for them to manually select the left-most column.

[JMichaelTX]

@Blakeandersonj

What I want to do, is generate a report in Ulysses from a database using Airtable. I was told by a nice fellow on YouTube, that I can use variables using the clipboard, to generate the report much easier, as I was originally planning to copy and paste from Airtable to Ulysses, which seems excessive given the lengthy report. In other words I want to copy various cells and set variables for each cell and then eventually paste that into a document report.

@Blakeandersonj, looks like @gglick and @ccstone have given you a solution that deals with simple use cases. If you need to access Airtable in a more sophisticated, complex way, then you may need to use the Airtable API.

See Get Data from the Airtable API the Easy Way.

[gglick]

Thanks for pointing this out, @JMichaelTX! It was quite easy getting the data from my sample Airtable DB with a curl command in the Execute a Shell Script action as per their instructions. The only downsides that I see are that it seems to be considerably slower to get the data from the API instead of copying it to the clipboard, and the resulting data is in JSON rather than an easier-to-parse (at least for someone like me, who doesn't know much JavaScript) tab-separated variables format. The slowness may be acceptable depending on the use case, but for the second issue, it may be worth waiting until KM 9 is out, since one of the new features will be built-in parsing for JSON data.