Copy Data From Google Sheet Row to Variables, Enter the Variables Into Different Website Form

[MikeS88]

Hello hello,

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.

[ccstone]

Hey Mike,

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.

Clear enough?

-Chris

[MikeS88]

Hi Chris,

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,

Thanks again,
Mike

[MikeS88]

Nope, stuck. Would it be regex to go from column to column in the file?

[ccstone]

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)

[MikeS88]

@ccstone It's beautiful. Thank you, Sir.

[MikeS88]

@ccstone As a small learning exercise, what does each variable entail in the regex? For instance, the first variable -
local_Name

Where does the regex code end for that specific variable?

[ccstone]

^([^\t]+)\t([^\t]+)\t([^\t]+)\t([^\t]+)\t([^\t]+)

^		==  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.

local_Name
local_RateType
local_RateDesc
local_Rate
local_StartDate

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

[MikeS88]

Thank you!