The magic of Applescript, I'm impressed!!

First off, Mods; please move this (or delete if that’s the case) to where it should be better listed. I wasn’t sure where to post this (and delete this line I guess!).

I’ve spent weeks, literally every day, working on automating a task I have put upon myself. I have to create data merged letters for the printers to print and send. Has to be done on a week-daily basis, and can be anything from less than 100 to upwards of 10,000 or more. The volume will be unknown on any given day. I also may not be at my Mac to do this at the end of each day, so it had to be self-running, and had to be fired off remotely.

There’s a total of 7 scripts running (one all Automator, four scripts saved as apps, fired off by Automator/Hot folders). In brief;

  1. moves the .csv from Dropbox to the mac
  2. Fires Excel which sorts and splits the output files
  3. Counts the contents of the folder, creates a temp .txt. files on the desktop and then moves each file to another folder and renames the file
  4. InDesign opens the template, merges the data, exports as a PDF and closes without saving
    (this bit repeats until the folder is empty)
  5. Checks the temp .txt file on the desktop and counts the contents of the folder. When it’s reached, then moves all files to the next folder
  6. Automator mushes the PDFs in to one, saves to a new folder
  7. PDF gets renamed with the date and time stamp, copies back to Dropbox (phone notification) and uploads to an FTP

By using Dropbox, it’s accessible from my iPhone, iPad etc etc, or any Mac or PC I can get hold of at the time, or if I can’t, then I have given access to 2 other people, who can trigger all this themselves. Very important that it can’t rely on me every day!!

With exception to the Excel spreadsheet sorting (as I didn’t write this), I’m more than happy to share any and all code I’ve written, and a huge thanks to everyone here that helped me achieve this. I don’t have a scripting or coding background (clearly!!), I’m a graphic designer who doesn’t quit. Except I’m quitting graphic design as I’ve just started a company, which part of the process of new users, is a confirmation letter, hence all this stuff!!

If you’ve got a couple of minutes to spare, the scripts in all their glory is happening here - https://dl.dropboxusercontent.com/u/38506318/Automation.mov
The only bits to note; it’s a bit slow, cos it’s an old Mac (2008 MacBook Pro!), and I’ve added delays all over the show to make sure that it’s completely done before the next task starts (I’ve ordered the new iMac which is going to annihilate this!!), and just working out how to close the Excel sheet when it’s finished. But aside from that, I didn’t touch a thing! Oh, and at the end, the file is copied to Dropbox so I’m notified (then I’ll delete that copy, cos there’s a copy on the Mac still) and there’s an instruction that’s been disabled for testing, that puts a copy on the FTP for the printer too.

Again, thank you so much for all your help, just wanna share what can be done!! (cos someone told me it couldn’t!!) :lol: