Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

I want to write a script or Automator workflow/app that emails a random shortcut of the day to three recipients. The source is from an Excel spreadsheet. One column is the shortcut, and the second column is the definition. Anyone have experience?

I want to write a script or Automator workflow/app that automatically emails a random shortcut of the day to three recipients. The source is from an Excel spreadsheet. One column is the shortcut, and the second column is the definition. Anyone have similar experience and know that this would work?

iMac (27-inch Mid 2010), OS X Mountain Lion (10.8.3)

Posted on May 15, 2013 8:01 PM

Reply
20 replies

Jun 12, 2013 3:48 PM in response to Skoot28

I think you need to be a little clearer on the contents of the spreadsheet. Do you, for instance, want to get a random shortcut from anywhere in column 1 or do you want to go through these sequentially? What does the 'definition' mean that's in the second column.


Do you want always to send to the same three recipients (so can their addresses be hardcoded)?


It would be helpful if you could post example spreadsheet data (not necessarily real).


Finally, do you get sent the data in the spreadsheet? In other words, are you particularly wedded to using Excel?

Jun 12, 2013 7:40 PM in response to Arkouda

Thanks for the response Bernard.




They are shortcuts for Logic Pro


Column A contains the shortcut

Column B contains what the shortcut does


Example:


C Copy

V Patse

Z Undo

⇧Z Redo


Etc


Would like it random.

Would be nice to be able add email addresses to a list.

Data can just be sent as plain text in the email or Message.



Does that help explain a little better?

Jun 14, 2013 6:16 AM in response to Skoot28

I have had a first stab at the script, below. It uses a file to store the shortcuts and command [descriptions]. You should be able to see from the script annotations how to add a new one. (I populated 1-4 with real data, but got lazy after that, so you have a few placeholders to work with first.


As I have noted, once you are happy that you have all the data in the file, you can comment out part of the script for ongoing use. (BTW, my reluctance to use Excel is that I don't currently have it installed and I don't want to offer wrong advice. If you have Numbers, I do have that and could probably modify to work with a spreadsheet from there. This might be especially useful if you have the data already sitting in Excel.)


A few things came-up whilist I was writing the script:


1. Currently, all recipients will not only get the same tip at the same time, but they will see the names and email addresses of the others who receive them. It is possible to modify this.


2. I have added a property gRandomCheck which keeps track of which shortcut has already been used (since the last time the script was compiled. This will prevent the same tip being sent more than once. When all tips have been sent once, the script will alert you and not send anything until reset. It does not check on a per-addressee basis (which would be a refinement). (If you add a new addressee at this stage, the whole process will start again, and you may not really want this to be the behaviour.)


3. The way that I have built the list, commandList, is deliberately cumbersome - it's for the sake of clarity. If you prefer, you can construct the whole list as {{shortcut:"X", command:"X"}, {shortcut:"Y", command:"Y"}}


Have a look - I am sure you will have questions!



SCRIPT STARTS HERE Paste the following lines (thru the end) into a new AppleScript Editor document and press RUN


--The property gRandomCheck persists between runs and is used to stop sending the same hint twice.

property gRandomCheck : {}


--I am defining a file on the desktop. It doesn't have to be in this location

set theFolder to path to desktop

set commandFile to (theFolder as text) & "CommandFile.csv"

--


--(* Unless you need to change the file contents you do not need to write to it each time. Remove the "--" on this line and before the asterisk about 18 lines below


--Follow this format and enter as many records as you like on a new line - each with a unique name

set record1 to {shortcut:"Z", command:"Undo"}

set record2 to {shortcut:"R", command:"Record"}

set record3 to {shortcut:"R", command:"Record Toggle"}

set record4 to {shortcut:".", command:"Discard Recording & Return to Last Play Position"}

set record5 to {shortcut:"X", command:"x"}

set record6 to {shortcut:"X", command:"x"}

set record7 to {shortcut:"X", command:"x"}

set record8 to {shortcut:"X", command:"x"}

set record9 to {shortcut:"X", command:"x"}

set record10 to {shortcut:"X", command:"x"}

set record11 to {shortcut:"X", command:"x"}

set record12 to {shortcut:"X", command:"x"}

set record13 to {shortcut:"X", command:"x"}



--


--Make sure you add the record name before randomCheck:

set commandList to {record1, record2, record3, record4, record5, record6, record7, record8, record9, record10, record11, record12, record13}

--



--This part writes the above records to the file each time.

set fileRef to open for accesscommandFile with write permission

set eof of fileRefto 0

writecommandListtofileRefstarting ateofaslist

close accessfileRef

--

--remove "--" here to stop writing (see above)*)


--This reads from the file

set fileRef to open for accesscommandFile with write permission

set commandList to readfileRefaslist

close accessfileRef

--


--Here's where the random record is chosen

set selected to 0

if (count of gRandomCheck) is not (count of commandList) then

repeat

set selected to (random number from 1 to (count of commandList))

if selected is not in gRandomCheck then

set gRandomCheck to gRandomCheck & selected

exit repeat

end if

end repeat

else

display dialog "You have sent all shortcuts to all recipients once. Recompile to reset"

return

end if

--


--This is setting-up the format of the mail contents

set messageText to ("Shortcut: " & shortcut of recordselected of commandList & return & "Command: " & command of recordselected of commandList)

--


tell application "Mail"



--When you're ready to use, you probably will not want Mail popping to the front, so add "--" before activate


activate



--You can change the subject of the message here. You can also set visible:true to visible:false when you are happy all is working OK

set theMessage to (make new outgoing message with properties {visible:true, subject:"Today's Logic Pro Shortcut", content:messageText})


--


tell theMessage



--You can add new recipients here. Just add a new line. Modify the names and addresses here to real ones

make new to recipient with properties {name:"Fred Smith", address:"freds@wizxzy.com"}

make new to recipient with properties {name:"John Smith", address:"johns@wizxzy.com"}

--



--When you are ready to start sending, remove the dashes before "send" below


--send

--

end tell

end tell

Jun 15, 2013 4:58 PM in response to Arkouda

I have modified to use the data from Numbers.


A few things to note:


I have kept the name of the spreadsheet file as you sent it to me. The script will open it if it is placed on the desktop (but I did get an error with a missing font, but that's probably just a mismatch on my system). Alternatively, you can comment out the second paragraph altogether and make sure that you open the file in Numbers and that it is the topmost document in the application.


The script does not at this point have any error handling, so if something fails on a pass it will fail. I don't see there being scope for damage (but I would caveat the sensible advice to make sure you have backups).


I have used the "All Commands -" table of the "All Commands" sheet, but this can be modified once the basic functionality is right. The script will load all lines in that table, ignoring empty lines and lines that start with " ----", so so long as you keep to that format you will not break anything.


Your final offline question on how to run on a regular basis: you can use iCal as you suggest, you can also use a crontab, or launchd. However, these latter two are fiddly so I use an app called Lingon X (which is an interface to launchd). You can find it here: http://www.peterborgapps.com/lingon/


It would probably be useful to add a list of email addresses to the spreadsheet so that they are not hardcoded. I can do that in a further iteration.


Here's the revised script:


--The property gRandomCheck persists between runs and is used to stop sending the same hint twice.

property gRandomCheck : {}


--I am locating the file on the desktop. It doesn't have to be in this location

set theFolder to path to desktop

set commandFile to (theFolder as text) & "Logic Pro 9 Command Keys Spreadsheet For Bernard Numbers Format.numbers"

--

set commandList to {}

tell application "Numbers"


openfilecommandFile

tell document 1

tell sheet "All Commands"

tell table "All Commands - "


--get number of rows in table

set rowCount to row count

repeat with rownumber from 1 to rowCount

tell cell 1 of row rownumber


--read first column

set shortVal to value

set skipNext to false



--check it starts with something other than "---" or ""

if (shortVal starts with " ---" or shortVal is 0.0) then set skipNext to true


--if it does, get second column value

end tell

if not skipNext then

tell cell 2 of row rownumber

set commVal to value

set end of commandList to {shortcut:shortVal, command:commVal}

endtell

end if

end repeat

end tell

end tell

end tell

end tell


--Here's where the random record is chosen

set selected to 0

if (count of gRandomCheck) is not (count of commandList) then

repeat

set selected to (random number from 1 to (count of commandList))

if selected is not in gRandomCheck then

set gRandomCheck to gRandomCheck & selected

exit repeat

end if

end repeat

else

display dialog "You have sent all shortcuts to all recipients once. Recompile to reset"

return

end if

--


--This is setting-up the format of the mail contents

set messageText to ("Shortcut: " & shortcut of recordselected of commandList & return & "Command: " & command of recordselected of commandList)

--


tell application "Mail"



--When you're ready to use, you probably will not want Mail popping to the front, so add "--" before activate


activate



--You can change the subject of the message here. You can also set visible:true to visible:false when you are happy all is working OK

set theMessage to (make new outgoing message with properties {visible:true, subject:"Today's Logic Pro Shortcut", content:messageText})


--


tell theMessage



--You can add new recipients here. Just add a new line. Modify the names and addresses here to real ones

make new to recipient with properties {name:"Fred Smith", address:"freds@wizxzy.com"}

make new to recipient with properties {name:"John Smith", address:"johns@wizxzy.com"}

--



--When you are ready to start sending, remove the dashes before "send" below


--send

--

end tell

end tell

Jun 18, 2013 11:35 AM in response to Skoot28

Substitute the line setting theFolder (set theFolder to path to desktop) for these lines


Tell application "Finder"

set theFolder to (folder "Applescripts Shortcuts of the Day" of folder "Applications" of startup disk) as alias

End tell


Then the next line setting the commandFile should work if you have the correct file name.


Incidentally, I did not close the file. You can just add:


Tell application "Numbers"

close document 1

End tell


At the end of the script.

I want to write a script or Automator workflow/app that emails a random shortcut of the day to three recipients. The source is from an Excel spreadsheet. One column is the shortcut, and the second column is the definition. Anyone have experience?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.