Copy Column of Values; Paste One Cell at a Time

Hi All,
I'm trying to achieve the following:

  • Copy a column from google sheets / excel
  • Have KM copy the individual cells as their own pastable entity (which I then use with a paste and pop)

But KM is only copying unique cells to the clipboard.

image

Here's my little macro... Any ideas as to how I can get a complete list as an output?

image

Keyboard Maestro lets you create named clusters of key:value pairs which it calls dictionaries.

manual:Dictionaries [Keyboard Maestro Wiki]

Choose a name for a fresh dictionary,
and for each cell string in your incoming list:

  • look that string up in the dictionary
  • if the dictionary already contains a non-empty value for that string, just move on
  • if the dictionary doesn't yet have a value for that string:
    • append the string to your paste list
    • assign a simple value like "1" to that string in the dictionary.

at the end, clear the dictionary,
and use your paste list of unique (unrepeated) strings.

KM is only doing what you tell it to do - it's not intentionally losing duplicate cells. So double-check to make sure the clipboard contains what you think it does.

Hey Jack,

Welcome to the forum!   :smile:

Well, this is not exactly true.

What's happening is that Keyboard Maestro's clipboard history “helpfully” removes duplicate entries.

<soapbox>

Personally I think this is a terrible feature in that the user has no control over it – nor do I think it's documented in the manual.

The idea that a user would never need duplicate entries in the clipboard history is shortsighted.

My opinion is that the user should be able to either toggle this feature on/off – or that duplicates should always be accepted, but the user could at their discretion remove duplicates from the history.

Of course having both options would also be acceptable.

LaunchBar's clipboard history and Paste.app both play the same game, so this heavy-handedness is not limited to Keyboard Maestro.

</soapbox>

So, what's the solution?

I do NOT recommend dictionaries – they are too complicated to mess with for this task.

It depends upon how bad you want to use the feature – I'd just roll my own paste-pop with a Keyboard Maestro variable instead of the KM Clipboard History.

Frustrating... But it is what it is.

-Chris

2 Likes

I stand corrected, and... you learn something every day!

Hey Jack,

One other thought.

When you cycle through the cells you could append or prepend a serial number to prevent KM from seeing entries as duplicates.

Pasting back would remove the serial number.

It's a bit ungainly, but it's workable and not too difficult – and it allows you to use the clipboard history without resorting to another mechanism.

-Chris

Thanks, Chris.

Happy to know I'm not (that) crazy!

My inelegant solution is just to copy the column to a text file and pop the top line after I paste it using head / tail in bash.

1 Like

Thanks for the input, Dan!

Also, Paste and Pop is my most used macro. When all is said and done, you've probably added years to my life.

1 Like

Any solution that works has a certain degree of elegance...  :sunglasses:

-Chris

2 Likes

You don't show your entire macro, but here is my macro that works fine with Excel.
I did not test with Google Sheets.

The key is in copying the Excel data to a KM Variable, which is plain text, and then setting the Clipboard to that Variable, and then Paste.
It does NOT depend on the behavior of the KM Clipboard History.

Example of Macro Running

MY-KM-Macro-Copy-Excel-Column

Below is just an example written in response to your request. You will need to use as an example and/or change to meet your workflow automation needs.

Please let us know if it meets your needs.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

MACRO:   Copy Excel Column and Paste into New Column [Example]

-~~~ VER: 1.0    2021-02-09 ~~~
Requires: KM 8.2.4+   macOS 10.11 (El Capitan)+
(Macro was written & tested using KM 9.0+ on macOS 10.14.5 (Mojave))

DOWNLOAD Macro File:

Copy Excel Column and Paste into New Column [Example].kmmacros
Note: This Macro was uploaded in a DISABLED state. You must enable before it can be triggered.


ReleaseNotes

Author.@JMichaelTX

PURPOSE:

  • Copy Excel Column and Paste into New Column [Example]

HOW TO USE

  1. First, make sure you have followed instructions in the Macro Setup below.
  2. Select the cells in the Source column in Excel
  3. Trigger this macro.

MACRO SETUP

  • Carefully review the Release Notes and the Macro Actions
    • Make sure you understand what the Macro will do.
    • You are responsible for running the Macro, not me. ??

REQUIRES:

  1. KM 9.0+ (may work in KM 8.2+ in some cases)
  2. macOS 10.11.6 (El Capitan)+

TAGS: @Excel @Copy @Example

==USE AT YOUR OWN RISK==

  • While I have given this a modest amount of testing, and to the best of my knowledge will do no harm, I cannot guarantee it.
  • If you have any doubts or questions:
    • Ask first
    • Turn on the KM Debugger from the KM Status Menu, and step through the macro, making sure you understand what it is doing with each Action.

Hi JMicahelTX,

Here's a use case:

I have to copy a test answer key into an online testing platform that doesn't have an import function. Answers for each question have to be submitted one at a time. My answers currently reside in a spreadsheet, and I don't want to spend a day going back and forth between spreadsheet and test creation UI.

Here are the two macros my simple mind ended up using. Haven't been able to break it yet.

Macro 1 - Copy to File:
image

Macro 2 - Paste and Remove Line from File
image

I'm sure there is a better shell script solution using the sed command that doesn't require the creation of a temp.txt, I'm just not familiar enough with it.

What's nice about this system is that if I get pulled into another task, I can use my system clipboard and it's associated paste and pop functionality without losing my place in the other ordered clipboard.

Hey Jack,

Yukko...

There's nothing wrong with using the shell and files, but it's not necessary.

I'm using Keyboard Maestro variables and native actions for these.

If this task was mine I'd want to know if I could use the Set Front Browser Field to Text action to fill the form instead of using the clipboard for pasting.

-Chris


Test Group.ccstone Macros.kmmacros (14 KB)

image

image

As it's written, if an answer is more than one letter, this macro doesn't paste each cell as a whole. That is, the answer "ADE" gets pasted as "A", then "D", then "E".

I deleted some of the question marks from each regular expression and it works perfectly.

^(.+?)\R? --> ^(.+)\R?
and
^(.+?)\n? --> ^(.+)\n?

Thanks, Chris!

1 Like

I still don't fully understand your use case. So please confirm or correct:

  1. You have ALL the answers in an Excel file.
  2. You want to copy each answer into the Online Testing Platform
  3. You create or open the specific Test that needs answers
  4. You paste the answer from Excel into a question, and then move to the next question
    • Can you TAB between Online Questions?
  5. Repeat Step #4 until all questions are answered.

If the above is basically correct, then you can just do the following:

  1. Select All of the answers in Excel, with each answer in the same column, but different rows.
  2. Copy
  3. Set KM Variable to the Clipboard
  4. Activate the Online Testing Platform, and create/open the corresponding Test set
  5. If needed, select the first question
  6. Use the KM For Each action, with a Lines In collection from the above KM Variable
  7. Insert Text by Pasting of the For Each loop variable, Local__Line
  8. Move to next Question, by using the KM Type Keystroke of TAB.

I stop here until you confirm/correct your workflow.