Hi there,
I am struggling with turning values in Excel/numbers into parts of code. The length of the process may vary depending on the # of rows, and I am trying to automate this procedure. The code with placeholders would look like this:
I know that jumping between programs is not ideal and causes issues. Hence, the recording function doesn't work and would lack the ability to update the workflow in the future.
Ideally the workflow would:
detect the # of rows
print the first lines of code until it hits the first Placeholder
repeat the same copy/paste script until the last item of the second column is pasted
Use the Search using Regular Expression action with the regex ([^\t]*)\t([^\t]*) to extract the first two columns to variables PlacementCode and PlacementId.
Use the Set Variable to Text action to set a variable "Entries" to "%Variable%Entries%{ code: '%Variable%PlacementCode%', bids… placementId: '%Variable%PlacementId%' … },\n". (ie, append an entry to the variable Entries
Then you can add the leading stuff ("var pbjs…") and trailing stuff as necessary.
I think this should do the trick, or at least get you started.
I used an Excel file for the source data, like this:
This is my dummy data, but I think it is representative of yours.
If you use Apple Numbers, you will have to change the AppleScript, or convert the file to Excel.
Example Output
AppleScript to Create Text List of Source Data for Use in KM Macro
tell application "Microsoft Excel"
--- ACTIVE CELL MUST BE UPPER LEFT CORNER OF REGION ---
set startRange to first range of active cell
set dataRange to current region of startRange
set rowList to value of dataRange
set dataListStr to ""
repeat with iRow from 2 to (count of rows in dataRange)
set oRow to item iRow of rowList
set codeStr to item 1 of oRow --range codeRng
set idStr to item 2 of oRow
set dataListStr to dataListStr & codeStr & "," & idStr & linefeed
end repeat
return dataListStr
end tell
To auto-detect the data range, you must have an empty row below, and an empty column to the right of the data block.
MACRO: Build Blocks of Code from Spreadsheet [Example]
#### DOWNLOAD:
<a class="attachment" href="/uploads/default/original/3X/4/2/426a8ba18d3304c7d70e5c2ca752725861391548.kmmacros">Build Blocks of Code from Spreadsheet [Example].kmmacros</a> (7.1 KB)
**Note: This Macro was uploaded in a DISABLED state. You must enable before it can be triggered.**
---
---
Will this work for you?
Sorry for the minimal documentation, but I'm at the end of my day and thought you might prefer to look at and test what I have rather than wait another day for more documentation.
Questions?
@JMichaelTX@peternlewis
I am fairly new to km, but know that I have to dig into it to actually learn something. So thanks for providing all this and I will look at it with fresh eyes tomorrow.
Besides: I can't stress enough how supportive this community is and how your work helps scraping off hours of meaningless tasks allover the world.
I'd start with something like this to pull the values of the used-range of the worksheet into a list of lists dataset in AppleScript.
Each list corresponds to a row of data.
----------------------------------------------------------------
# Auth: Christopher Stone
# dCre: 2018/05/11 09:10
# dMod: 2018/05/11 09:10
# Appl: Microsoft Excel
# Task: Extract Data from the Used Range of an Excel Worksheet
# Libs: None
# Osax: None
# Tags: @Applescript, @Script, @Microsoft_Excel, @Extract, @Data, @Used, @Range, @Excel, @Worksheet
----------------------------------------------------------------
# Extract the Data from the Used-Range as a list of lists.
tell application "Microsoft Excel"
tell active sheet of active workbook
set myData to used range's value
end tell
end tell
# Remove the Header Line Items:
set myData to items 2 thru -1 of myData
----------------------------------------------------------------
Once you have the data in this form it will be very fast to iterate through and input the data into your template.
You should tell us what app you're using besides Excel.
You should also post the actual text template and an Excel test document for us to play with.
This will improve the quality of the help you'll get.
Chris, thanks for sharing your script, particularly the above statement.
That is a very useful trick.
For those who might want to return the data from Excel to KM as a text table, I have added a few lines to Chris' script:
----------------------------------------------------------------
# Auth: Christopher Stone
# dCre: 2018/05/11 09:10
# dMod: 2018/05/11 09:10
# Appl: Microsoft Excel
# Task: Extract Data from the Used Range of an Excel Worksheet
# Libs: None
# Osax: None
# Tags: @Applescript, @Script, @Microsoft_Excel, @Extract, @Data, @Used, @Range, @Excel, @Worksheet
----------------------------------------------------------------
# Extract the Data from the Used-Range as a list of lists.
tell application "Microsoft Excel"
tell active sheet of active workbook
set myData to used range's value
end tell
end tell
# Remove the Header Line Items:
set myData to items 2 thru -1 of myData
--- Convert List of Lists to Text List --- added by @JMichaelTX
-- (col separated by commas, rows separated by linefeeds)
set AppleScript's text item delimiters to ","
set myDataListStr to {}
repeat with oItem in myData
set end of myDataListStr to oItem as text
end repeat
set AppleScript's text item delimiters to linefeed
set myDataListStr to myDataListStr as text
return myDataListStr
----------------------------------------------------------------
