Hi, I can't get this to work. This macro Code search v3.kmmacros (6.1 KB)
is supposed to copy strings from an Excel/LibreOffice spreadsheet and search for the string in a new browser tab (search is done via Alfred, that all works fine). After each lookup, it goes down a cell.
What I can't figure out is a stop condition (so, when there are no more strings - empty cells below the table).
I tried the obvious "While (system clipboard is not empty)", but that didn't work. I tried to view the actual contents of the clipboard with Clipboard Viewer and I see "empty" is actually a few kB of data, so no wonder KM doesn't actually see this as an empty clipboard.
In the macro you uploaded, your while action is testing if the clipboard is empty, not, as you said, if the clipboard is not empty.
I'm not sure what the rest of your macro does, since I don't have those spreadsheets or the apps you mention or your data file, but I thought it was important to mention that your "while action" has the opposite condition that you were saying it does.
Ah right, I exported the macro while testing this. I do have it as you suggest. The issue still stands though. Copying an empty cell in LibreOffice does not result in an actually empty clipboard. I even tried to sanitize the clipboard contents by pasting it into Alfred and then copying it back which DOES result in the expected text to be in the clipboard (and not half a meg of junk), but it's still not totally empty, there's still some sort of empty character.
I'm still trying to understand your problem, but if the problem is that you want to set the system clipboard to an empty string, you can do this:
If you place the above action just before each occurrence when you press CMD-C, then you will know that the COPY command failed, as opposed to when it succeeded.
There's a column of strings. I want KM to copy these cells one by one, and search for them in a new tab on a website. I want the script to stop when there are no more cells (strings) to copy. I can't find a way to make KM stop on an empty cell as it's not actually empty (there's tons of system and app-specific data). That's the issue.
I could probably enter some sort of "stopword" into the spreadsheet and test for that but the script's not for me and this is kinda silly.
Then use the action I suggested above, prior to issuing a COPY keystroke, and then you will know whether the COPY failed (i.e., the clipboard remains empty) or whether it succeeded (i.e., the clipboard contains a string.)
LibreOffice puts even more crap into the clipboard. I repeat, this is an EMPTY cell. You can see what is in the actual system clipard by this little app: Clipboard Viewer.
I understand, no worries. The apps are not the culprit here, you can test with like the Google Sheets or any other software that has cells with text that can be selected by keyboard arrows. The underlying problem is that empty=not actually empty.
For the case of Google Sheets, if the copy command on an empty cell always produced that string, you could simply modify your loop to test for that, perhaps like this:
Yes, that could help. I have also noticed that the strings we have always have an underscore, so I can test for that. I find that curious testing for empty clipboard is this hard. Thank you!
EDIT: yeah, testing for an underscore worked, in this instance.
I agree that the problem is that empty=not empty, but I assert that the problem is that Google Sheets and LibreOffice are placing non-empty strings into the clipboard, so it is an app problem. But KM has a lot of power so using your underscore trick may be a good solution. I don't know enough about Google Sheets or LibreOffice to know whether there is a setting in them to make COPY work better on an empty cell.
I just asked ChatGPT why Google Sheets is giving you a non-empty string, and it replied that Google Sheets always returns formatting information. That's what you are seeing. There are several ways to fix that. One way is to simply remove formatting from your spreadsheet. Do you really need formatting codes in your spreadsheet?
That's odd, I just tried copying an empty cell in a new Google Sheets document, and I'm not getting any of that formatting that you are getting. So why am I getting the desired results and you aren't? My only theory is that your spreadsheet has formatting in it.
When you use the Copy command in an app it rarely copies just one thing -- more usually it is a collection of things that is placed on the System Clipboard. In Excel's case that would include the contents of the cell in various formats, the formatting of the cell, an MS object reference, the HTML representation of the cell, the cell's reference... So even a cell without content won't result in an empty Clipboard.
If you could select "nothing" -- Copy wouldn't work and the Clipboard would still hold its prior contents. It still wouldn't be empty.
So you'll need to work on the text content of the cell, which is what you want to Paste into your browser anyway. A simple way to do that while still only selecting the cell itself is Copy and then use the KM %SystemClipboard% token to evaluate the Clipboard as plain text. Something like this:
...will show the contents of the copied cell if it has any, or "No cell contents" when the cell is empty.
Give it a go, using an "infinite loop" to work down your column of cells and either sending the URL to your browser or, when you reach that empty cell, exiting the loop.
Copy on an empty cell should generally fail (error), or perhaps could copy a reference to the cell, it's a bit ambiguous.
If I was doing this, and if it was at all possible, I would use a sentinel in the cell, eg "KMEXITKM". And then your macro can easily detect that it is the last cell and stop.
Failing that you could possibly detect the absence of a valid value in the clipboard (eg if the entries all are single words, you could detect that the clipboard contains a single word).
I would do it exactly the same way :). However, sometimes you cannot change the original file (access rights, passwords, etc.) or you don't want to set the file to "dirty". In that case I would try to create an AppleScript or LibreOffice Basic macro to copy the content of the relevant cells to the clipboard.
BTW: I read that LibreOffice also supports Python, JavaScript, and BeanShell.
Another option is to copy all the cells at the start - select the first cell, and then if Command-Shift-Down Arrow selects all the cells below it then Copy all of them at once, and then you can use Lines In collection to iterate through them.
