Spreadsheet automation

Hi

I do some calculation on spreadsheets which entails getting a bunch of numbers from various websites and plugging them in to various spreadsheets and get results. Copying and pasting the numbers to different cells on various spreadsheets is the tedious bit that I’d like to automate via KM.

From what I understand, I can use clipboard to automate this task but I’m not sure how to go about it.

The spreadsheet program is Libreoffice.

Thanks for any pointers and help.

As a starting point, you can use the clipboard history (activate the clipboard history by pressing Command-Control-Shift-V by default). So you can copy a bunch of numbers, then switch to your spread sheet then paste each of them in. At the very least that saves you bouncing back and forth.

To go further would depend on your specific workflow, but you could, for example, copy a series of numbers, and then have a macro that does something like:

  • Paste
  • Right Arrow
  • Set Clipboard to Past Clipboard:2
  • Paste
  • Right Arrow
  • Set Clipboard to Past Clipboard:4
  • Paste

etc

It tends to get confusing, and error prone using the clipboard history like that, so you may prefer something like:

  • Set Variable “Cost” to “%PastClipboard:0”
  • Set Variable “Price” to “%PastClipboard:1”
  • Set Variable “Quantity” to “%PastClipboard:2”
  • Insert Text "%Variable%Cost%
  • Tab
  • Insert Text "%Variable%Quantity%
  • Tab
  • Insert Text "%Variable%Price%
  • Tab

One thing to take care about - if two of the things you copy are identical, Keyboard Maestro’s clipboard history removes duplicates and that will mess this sort of thing up. This is usually not an issue, but the more things you copy and the smaller a space they come from (eg numbers instead of arbitrary text) the more likely you are to hit the issue.

1 Like