Apologies in advance if I use the wrong terminology as I am a novice. I store a Numbers file on my computer which contains a list of names in one column with associated email addresses and physical addresses in adjacent columns. I can use a CSV file instead if that works better with Shortcuts. I would like to create multiple “mail merged” emails with the information contained in the Numbers/CSV file.
With the help of another post on this forum with an answer by @sylumer , I have gotten close, but as my needs are slightly different, and as my programming skills are slim to none, and I am a bit stuck. For reference, here is my current iteration: Shortcuts
Below are the issues I have. I would appreciate any help on any one of them or all of them.
The current shortcut references a dictionary, but I have hundreds of rows of data on my spreadsheet that I would need to manually input, along with 4 values each, unless there is a way for the dictionary to pull the data itself. Or, if Shortcuts can directly access the Numbers/CSV file, a dictionary may not be necessary.
For some reason the “Street” variable from the dictionary is not inserting the referenced data in the output, but the “First Name” and “Number” variables are working fine.
In “Choose from Dictionary” I have selected both “Select Multiple” and “Select All Initially,” but when run, only the first key is chosen and I have to manually select the 2nd key (I only have 2 keys on my draft shortcut, but with hundreds this would be quite a burden.)
The resulting emails oddly have a Times font instead of my regular Helvetica. I’d like to keep it normal and I don’t see what would be changing it.
Can Shortcuts send the email on its own? At this point it get the emails composed, but I have to click “Send” on each one.
As I add rows to the Numbers/CSV file, can I run the shortcut again where it is only picking up new entries, or do I need to create a separate file for an additional batch of emails? Can I have the flexibility to pick up only new entries -or- choose to email the whole list again?
These last two don’t seem to affect the shortcut, but I am wondering:
I don’t know why the “Receive” message sits at the top, and I can’t delete it, but it doesn’t seem to affect anything.
Does the “Comment” box at the top serve a purpose? It contains the same text as the “Text” box and doesn’t seem necessary.
If I was not clear on anything or more information is needed, please ask, and thank you for any insight!
Shortcuts support for Numbers is surprisingly limited. On the Mac, if you display the list of available actions for Numbers, you will get a short list, none allowing you to read out from a Numbers file.
In terms of being able to read data out, then a CSV file is a plain text file format and Shortcuts can read plain text files, so that would be better.
Given you are mail merging using basic contact data (first name, e-mail address, (house) number, and street (presumably first address line), it could make sense for you to use your address book and have a group for the contacts.
Steve,
Your mailing address is 50 Brooklyn Street. Blah Blah Blah.
Best,
Me
---
Bruce,
Your mailing address is - Anywhere. Blah Blah Blah.
Best,
Me
---
Tony,
Your mailing address is 1 Avengers Tower. Blah Blah Blah.
Best,
Me
Many text editors would with a bit of find and replace, or a crafted shortcut, would allow you to change simple text formats (e.g. manay spreadsheet applications copy as tab separated values (TSV) into a structured JSON format, which is a dictionary interchange format that Shortcuts uses. But for maintenance purposes, I think using a CSV or contacts as covered above would be a better fit.
They are all working fine, you are just referencing the wrong variables. I have colour coded what actions each of your “Updated Text” magic variables reference. It is good practice to give your magic variables meaningful names to avoid confusion.
In effect what you have is your third replace carried out a replace on the first rather than the second replace, and then you simply never use the third replace, instead making rich text from the second replace action. If you had set it to the third action, then you would have found you second replace action, for NUMBER, would have been the variable that seemed to be being missed out.
A little further down, I have a reworking that includes renaming of variables to make things clearer.
When hovering over the select all option there is a pop up about a list, but the base documentation for the action does suggest dictionaries are treated as lists. Really I think it is just poor documentation.
It certainly does work for lists, and that’s the key to thinking about this.
If you are selecting from a dictionary, the dictionary can be multiple levels deep. Would a select all mean select all the root level items, all items at all levels, or something else? What would be the consistency?
The way to deal with this is to choose from the list of keys at the root level of the dictionary, then in the loop, instead of working on the repeat item, which would just be a key name, you use the repeat item to get the nested dictionary and then work on the elements in that.
That’s probably not all that meaningful in words, but don’t worry I’ve got a reworking of the shortcut a little further down.
Shortcuts is defaulting the rich text to the Times font. The rich testxt is already set as that before it ever reaches the Mail app, which is presumably where you specified your default font. The resolution is to specify the font you want before you convert the Markdown to rich text.
While it might seem at first there is no way to do this, the key things to know are that Markdown supports HTML, and behind the scenes the Shortcuts conversion engine is transforming the Markdown to HTML and then generating rich text from that. Because of this you can add a bit of HTML and CSS to the text before it is passed to the Markdown to Rich Text action.
The shortcuts example below takes your original shortcut, adds in more variable names, uses the dictionary keys for choosing, sets the generated rich text to be Helvetica, and shows each output instead of producing an e-mail (I didn’t want to be e-mailing and I don’t use the stock Mail app).
There are a number if ways you could tackle this. Here are just a few.
You could add an entry in the listing that tracks if they have been mailed before.
Note this requires writing back to your original file into a specific field of a contact record (or updating some property of a contact if you opt to use contacts and a contact group).
You could store a separate list of previously mailed users and filter those out.
This is just a different way of tracking the same data.
You could use a new file (ad hoc) each time that once they are mailed get appended to your mailed to list.
You could start looking at databases to track data, but that will only be worthwhile if your complexity grows.
In addition to the above, you could also potentially filter your list to allow you to select the last X entries (you enter X when the shortcut is run - you could even have a menu of options - All, last 5, last 10, last N (where you enter a number)). The assumption would be you always add your new records at the start or at the end each time. You then fetch the file content, split it into a list, reverse the list and filter it to your desired number.
There are apps that can help with this, but if you need to do it with native Shortcuts, there are a few “tricks” you can apply that are not at all obvious, but come in handy sometimes.
But ultimately, yes you can build in more flexibility, but flexibility introduces more complexity and given you note that you are a novice, maybe think about this now and consider it as part of a phase 2 once you have something in place that let’s you work for now.
Your shortcut is set to use the input to the shortcut which is what that block refers to. You can see it referenced in your Send action.
Here is the in app description for the Comment action.
The purpose is purely documentation.
I think fundamentally this one relates back to the post you referred to where you had found an answer I had posted. I believe this was simply a modification of Mike’s shortcut. the comment was his, and he was taking input to the shortcut. For my testing, as noted in the post, I put the content of the comment into a text action so I could use it rather than passing it in all the time. It simply kept the shortcut self contained, and Mike sould then just revert it easily to using the shortcut input if he wished - the comment still being in place.
I think that covers all of the requirements, issues and questions you had, but if I’ve missed anything, I can try and pick it up in a second pass.
sylumer, thank you so much for the detailed breakdown. However, I am still struggling with two areas.
When I replace your Quick Look instruction at the end with the Send to Email instruction I get an error that says “Send to Email failed because Shortcuts couldn’t convert from Text to Dictionary.” This is likely related to the new way you treated the Dictionary/keys/loop, but I am not sure.
The other issue is that I can’t figure out how to incorporate your CSV Shortcut into the Email Merge shortcut so that the data in the CSV gets automatically pulled in to the Merge shortcut. (Fwiw, I will always have data in my CSV so that part can probably be simplified by jumping straight to “Split by New Lines” right after “Get File.”) Ultimately I am still trying to avoid manual entry of hundreds of Dictionary items whereas the current Shortcut seems to still require that.
First off, you would not want to replace the “Quick Look” and the “Combine Text” and “Text” (for the separator). You would want to remove those and place your send mail action within the loop. The “Quick Look” was just an efficient way to show you the output with just one suitably sized pop up window.
If you have a conversion to a dictionary, I think that must be because you have explicitly added one to the shortcut, or you have left it in place if you were amending the old shortcut. Without seeing what you have it is an extremely lengthy process to diagnose. It is like a mechanic trying to fix a car by e-mail.
I would expect your shortcut to look something like this:
Note, not the same as this … as will become clear below.
The provided example creates an example file if the file does not already exist - it is a self contained example.
On the assumption you have your file all ready to go, then, as it sounds like you suspected, you would/should…
Point the first action, the “Get File”, to your CSV.
Remove the entire “If” action, as that is what creates (and reads in to prove it was reading from file even on the first run) the example CSV file.
Amend the “Split” action to work on the content of the file rather than the now non-existent “If” result.
Q) You note that you can’t figure out how to incorporate the CSV into the shortcut, but you have got it down to the “Get File” and “Split”. What is it that you haven’t figured out?
To me it sounds like you did figure it out, but something was not working. But there are no details to go on of what didn’t work for you, what happened, what didn’t happen, what your shortcut was, etc. If you can provide details on what you had, then we can probably suggest what the potential issue might be or have been.
sylumer, Here is my slightly modified version: Shortcuts
I set the CSV separator to be a comma without quotation marks because Numbers seems to remove the quotations marks when exporting to CSV and it was causing a problem with the Shortcut. It seems to be working fine this way. Is there any reason I shouldn’t remove the quotation marks?
There is a bit of odd behavior on the Privacy popup. It prompts me to allow the email to be sent. For testing purposes I chose “Allow Once.” Oddly, that allowed it to send the first two emails (to Steve and Bruce), and then I need to click “Allow Once” a second time for it to send the 3rd email to Tony. I don’t think it will be an issue once I choose “Always Allow,” but something seems awry.
Finally, I am curious what the purpose is of replacing ^“(.*)”$ with $1. I’m not sure what that part is doing.
This dialogue has been extremely helpful; thank you.
The double quotes are a standard delimiter for a CSV. It allows you to account for commas within data - such as if the address line contained a comma. If this will never be the case, it is safe to exclude the quotes.
Changing the split by "," to , would switch the split around. You could even include a check to see what separator/delmiter and separator is in use and split accordingly.
That is a regular expression that removes any leading and trailing double quote.
Choose From List now supports “Select All Initially” when a dictionary is passed in as input, and works from Siri on watchOS when “Select Multiple” is on
Looks like the Shortcuts team did consider this to be a bug.
