My last attempt (coz it’s an interesting problem for sheet). Each layer of customisation increases the complexity and require more time for a “quick and dirty” job. But if there is any bugs or issues in implementation, u may need help from others…
One option
If userInputOrder is false:
You don’t have to delete any column and can choose from the full sheet, but there is no customized order. So u need to re-arrange the order in the sheet before running the script.
If userInputOrder is true:
You don’t have to delete columns or arrange the column of the full sheet. But you need to write down the order of the columns (by numbers) before u run the script for customized sort.
The example shows that the report will be prepared in the order of column 1, column 3, column 2
-- Created by Ngan 2019.09.23
use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
property userInputOrder : false
tell application id "DNtp"
try
set theSheet to content record of think window 1
set theCells to cells of theSheet
set theHeading to columns of theSheet
repeat with i from 1 to length of theHeading
set theHeading's item i to my firstN((offset of "#" in theHeading's item i) - 1, theHeading's item i)
end repeat
activate
if userInputOrder then
set theChosenFields to display name editor "Exact #fields and separate with comma" default answer "1,2,3 (e.g.)"
set theChosenFields to my strToList(theChosenFields, ",")
else
set theChosenFields to choose from list theHeading default items "" with multiple selections allowed
set theChosenFields to my indexOfAllItems(theChosenFields, theHeading)
end if
set theText to ""
repeat with i from 1 to length of theCells
repeat with each in theChosenFields
set j to each as integer
set theText to theText & (theHeading's item j & ": " & (item j of (item i of theCells)) & return)
end repeat
set theText to theText & return & return
end repeat
set theRepName to display name editor "Name of the report" default answer "Report for " & (name of theSheet)
set theRepLocation to display group selector "Create the report in:" for current database
set theNewDoc to create record with {name:theRepName as string, source:theText as string, type:rtf} in theRepLocation
end try
end tell
on indexOfAllItems(theItemList, theList)
set l to {}
repeat with each in theItemList
if my indexOfOneItem(each, theList) is not 0 then set the end of l to my indexOfOneItem(each, theList)
end repeat
return l
end indexOfAllItems
on indexOfOneItem(theItem, theList)
-- credits Emmanuel Levy
set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, return}
set theList to return & theList & return
set AppleScript's text item delimiters to oTIDs
try
-1 + (count (paragraphs of (text 1 thru (offset of (return & theItem & return) in theList) of theList)))
on error
0
end try
end indexOfOneItem
on firstN(n, s)
return text from character 1 to character n of s
end firstN
on strToList(thestr, d)
local theList
set {tid, text item delimiters} to {text item delimiters, d}
set theList to every text item of thestr
set text item delimiters to tid
return theList
end strToList
Not only is is much simpler to run the report repeatedly that way - but I suspect it would not take much of a tweak to hard-code the order of the fields in the script (or place the config in a separate file in DT3).
Then it would be very easy to automatically run the script daily to achieve the desired customized report.
Make sure that userInputOrder is true
Just edit the actual order of columns in this line for repeated reporting. If you need to print col 1,2,5,6,8,11 (in that order) each time.
set theChosenFields to {1,2,5,6,8,11}
Time for bed…
-- Created by Ngan 2019.09.23
use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
property userInputOrder : true
tell application id "DNtp"
try
set theSheet to content record of think window 1
set theCells to cells of theSheet
set theHeading to columns of theSheet
repeat with i from 1 to length of theHeading
set theHeading's item i to my firstN((offset of "#" in theHeading's item i) - 1, theHeading's item i)
end repeat
activate
--if userInputOrder then
-- set theChosenFields to display name editor "Exact #fields and separate with comma" default answer "1,2,3 (e.g.)"
-- set theChosenFields to my strToList(theChosenFields, ",")
--else
-- set theChosenFields to choose from list theHeading default items "" with multiple selections allowed
-- set theChosenFields to my indexOfAllItems(theChosenFields, theHeading)
--end if
-- **** 3 columns to prepare in that order *****
set theChosenFields to {1,2,5,6,8,11}
set theText to ""
repeat with i from 1 to length of theCells
repeat with each in theChosenFields
set j to each as integer
set theText to theText & (theHeading's item j & ": " & (item j of (item i of theCells)) & return)
end repeat
set theText to theText & return & return
end repeat
set theRepName to display name editor "Name of the report" default answer "Report for " & (name of theSheet)
set theRepLocation to display group selector "Create the report in:" for current database
set theNewDoc to create record with {name:theRepName as string, source:theText as string, type:rtf} in theRepLocation
end try
end tell
on indexOfAllItems(theItemList, theList)
set l to {}
repeat with each in theItemList
if my indexOfOneItem(each, theList) is not 0 then set the end of l to my indexOfOneItem(each, theList)
end repeat
return l
end indexOfAllItems
on indexOfOneItem(theItem, theList)
-- credits Emmanuel Levy
set {oTIDs, AppleScript's text item delimiters} to {AppleScript's text item delimiters, return}
set theList to return & theList & return
set AppleScript's text item delimiters to oTIDs
try
-1 + (count (paragraphs of (text 1 thru (offset of (return & theItem & return) in theList) of theList)))
on error
0
end try
end indexOfOneItem
on firstN(n, s)
return text from character 1 to character n of s
end firstN
on strToList(thestr, d)
local theList
set {tid, text item delimiters} to {text item delimiters, d}
set theList to every text item of thestr
set text item delimiters to tid
return theList
end strToList
I am thinking that a nice next step with this would be to be able to present the edited repot not only vertically but also in the original format (with selected columns removed).
While removing columns is not scriptable, I believe creating a new Sheet is scriptable. So perhaps I can edit this to put the revised data in either/both format. I may give that a try.
The script is meant to be a kick-starter only and my skills is limited. The rest of the script is on you…. Make sure that you will share your finished script in this forum.
An update… not a finished project yet but I am considering how to proceed.
A pure Applescript solution will no doubt be the most elegant way to proceed, but I realized there is another option at least for a quick or first-pass solution. Maybe this is/was obvious to others but I had not realized this was possible.
I was considering how to automate the “non-scriptable” parts of DT3. “Find Image” in Keyboard Maestro is always a last resort but not always predictable in its response. I stumbled across the concept however of “GUI Scripting” - particularly using Home - ID-Soft PFdle as shown in this video https://www.youtube.com/watch?v=C06EjbwKuY0&t=143s . This is a neat option which makes a lot of tasks scriptable which would be hard to achieve otherwise.
With that in mind it is possible to script removal of a column using these Remove and Edit Columns keyboard shortcuts and this KM Macro:
The concept can easily be extended to remove other rows or several rows at once.
I am pondering which automation technique to use for the completed project - this does work as a quick interim solution now. In fact it works well with various permutations as the bottom row on my Stream Deck profile for DT3:
