I just started using Keyboard Maestro and I would really need all the expertise I can get. I have been trying to use KM to automate a very mundane process very crucial to my teams work.
Here is a brief explanation of the process I am trying to automate:
I have a Google Spreadsheet that has 2 columns:
Column A with a list of internal job links that needs to be worked upon for the current month.
Column B where we need to input the subscription status of the user's job links. The states can be active/closed/trial and I can get the status by opening each of the links.
Each month, we have been opening around 1-2k links manually trying to figure out the subscription status to prioritise the work cuz we do not have to work on closed accounts, whereas active account jobs would be our first priority.
I am trying to automate KM so that this job can be taken care of automatically, making our lives easier. So far, I am honestly not getting the hang of it. Can someone please guide me on what should I be doing? Any sample project of yours similar to the problem statement can really open my eyes on what needs to be done. I am open to all the tips and ideas you guys can offer.
Side note: I am getting back to Automation after a long break with my prior experience being Cucumber framework automation. So any help means a lot. Thanks in advance!!
Making a macro like this can feel overwhelming, but breaking it down into individual steps can help. Also, How one would solve this depends heavily on what the data / tool(s) you are working with look like.
You've got a list of URLS. Can you show us an example of how these links show you the status?
What I would probably start with is:
Get the list of links into keyboard maestro. You can copy it to the clipboard, export it as a text file, whatever works. Copying a single column from google sheets will copy the data as a list with each URL on a separate line.
Set up a "For Each" loop to run on the "The Lines in: System Clipboard" collection. And make a set of actions to get the status on each of these links, depending on how the data looks on the links, this could be relatively simple, or pretty complex.
Generate a list of the Statuses into a variable at the end of each for loop. You'd use "Append Variable with text" with a \n or linefeed token at the end to put each status on it's own line corresponding with the link. I'd just write the statuses so you can paste them back into google sheets, but once you get this working you can save this data however you want. A few examples could be, only saving the "Active" statuses, pulling additional helpful data onto the line of the output variable, formatting it as a CSV. I'd avoid this and just focus on saving a list that corresponds to your original input data that you can paste back into google sheets to start though!
Manually copying the whole of column A into a KM variable
Working through each line of the variable to
Open the link
Determine the status
Append that status to an "output" variable (possibly as an {id:status} key/value pair)
Pasting the "output" variable back into the Sheet, either directly replacing column B (when you've created a reliably-ordered list of status) or into a background sheet which can be used to update column B by lookup (key/value pairs).
The fun bit will be getting the status -- hopefully those links are to an API which return just that, or a pairing of id:status. Even more hopefully that API will accept a list of ids and you can do the lot with a single curl statement! The bad news would be if you had to open each link in an actual browser window and scrape that to get the status -- but it should still be doable.
If it is an API, take a look using a spreadsheet function. I don't use Sheets much, but these days most decent spreadsheet software will let you populate cell values via an API call to another service. Again, you'd use a lookup against data derived from those API calls, using a manual refresh.