November 16, 2022, 10:22pm
I have this project I am working on. I am looking to copy data from the spreadsheet, each column to a different variable. Paste those variables into a different website form. Move on to the next row on the spreadsheet. Until there are no more rows.
I can do the part on the form. What I am unsure where to start is with copying each column to a variable and then move on to the next row at the end, cycle cycle.
November 16, 2022, 10:36pm
Start out by copying the entire segment of data.
You should end up with a tab-delimited table, and that should be more easily parsed than trying to brute-force the UI across each row.
From there you can use a
For Each action.
You can copy the clipboard to a variable and work with that, or you can work with the lines-in-the-clipboard.
November 16, 2022, 10:41pm
Thank you for the quick response. I think it is clear enough and will be trying to get it set up now. A few follow up questions though. But first I want to see if I can solve them before asking,
November 16, 2022, 10:51pm
Nope, stuck. Would it be regex to go from column to column in the file?
November 16, 2022, 11:17pm
Since you know you have five columns per row, you can simply do a search with regex and extract your capture groups directly into variables.
Select a couple of rows of your data, and run this macro.
Keep in mind that Cmd-Arrow will select contiguous data in Google Sheets similarly to Microsoft Excel.
Extract Data from Google Sheets v1.00.kmmacros (8.2 KB)
November 16, 2022, 11:23pm
@ccstone It's beautiful. Thank you, Sir.
November 17, 2022, 12:12am
@ccstone As a small learning exercise, what does each variable entail in the regex? For instance, the first variable -
Where does the regex code end for that specific variable?
November 17, 2022, 10:26am
^ == Anchors at the beginning of a line
( == Start Capture
[^\t] == Range - any character NOT tab
+ == One or more of the previous
) == End Caputure
This repeats 5 times, since there are five variables to capture.
Keyboard Maestro uses the capture segments of the regular expression to figure out what part of the string to assign to what variable.
Take a look at this for a visual breakdown of the expression:
regex101: build, test, and debug regex