Create new folder from excel spreadsheet column automatically

Hi Everyone,

I have been searching for days now to no avail.

I am very new to applescript / automator and I get the essence of it but have not found the right solution.

Here is my scenario that I am really hoping someone has a script they can share with me.

I constantly update a specific excel spreadsheet (all throughout the day).

When I update a specific column in excel I would like an automator script to know that I have added a new item in that column, and then make a folder with that name I added to that column.

I would then like to have a sub folder added to that newly created folder.
The sub folders will always have the same name.

So as an eg.

I add in column A1 of my spreadsheet an item called “posts”.

Automator by watching this spreadsheet knows that I have added a new item in A1

It makes a new folder (in a predetermined location) called “posts”, and inside this folder it creates a new folder - we can call it sub 1 for ease of use.

Then a few min later I add a new item called “Blog” to let’s say A5 (still same column but further down in the rows), it must then do the same as above.

Because I am constantly adding new items, I dont want it to look through a range - for eg. A1 - A50, as then it would most likely result in making 49 Untitled folders if only A1 had data.

I need it to only add a folder once I add a new item in the column

How do I do this?
And how do I set it as an automator task that runs in the background so I dont need to tell it each time the source spreadsheet and folder or even to run it. It must all happen automatically.

Im running Mac Catalina 10.15

Any help would be so hugely appreciated.

Thanks in advance
Stu

Firstly, I welcome you to this site.

Now, you are a beginner, but here it is recommended to offer your script first.
For my part, I can only say how everything can be done, but I will not begin to build a ready-made solution.

So 1) you should have an application that tracks changes in your column. It should be stay-open. You will have to write this application yourself 2) The Automator action will only have to start it, so I see no reason to use Automator 3) Then this application should create a new folder and subfolders using Finder. 4) All this will have to be synchronized with your actions in order to avoid problems.

In general, this task is not so simple and you have to start with the simple and gradually move to the complex. Along the way, you will probably be helped in figuring out the intricacies of each step.

Other, and as I think, better workflow is this: do not track changes with script, but yourself. So 1) you create one Quick Action (service) in the Automator which creates folder and subfolders, each time when you need. Then, you add new item on Excel, and immediately run this service using right-click of mouse.

Thank you so much KniazidisR. Your feedback is greatly appreciated.

Please see what I have so far below.

I have only set it to row 1-5 for testing purposes with data in row 1-3 for testing, so that I dont get huge amounts of empty “untitled” folders.

What I would like it to also do is if there is if the row is empty, for it to ignore it so that it doesnt create an “untitled folder” and skips to the next row where there is content.

Maybe it is also possible in this script to make it add specific sub folders at the same time (that would be amazing). For eg. in each new folder created I would have 2 sub folders with predetermined names?

The problem I am also having with the below script is once it has run once and made the folders, if I then say fill a new row that was previously empty, the script wont run as it says there is already an item with that name, so I need it to skip those that are there already and add the new items only. Is that possible?

Please see my script below and hope it helps.

Script:

set startRow to 1
set endRow to 5

set A_Collumn to getCellList(“A”, startRow, endRow) – Column 1
set myDoc to choose file with prompt “/Users/Documents/Work/Test/automate test.xlsx” – Choose Doc
set myFolder to choose folder with prompt “/Users/Documents/Work/Test” – Choose output folder
repeat with i from startRow to endRow
tell application “Microsoft Excel”
tell document (myDoc as string) to tell sheet 1 to set A_CellValue to (value of cell (item i of A_Collumn)) – value of cell i of column A
end tell

set folderName to (A_CellValue) -- Create name
tell application "Finder" to make new folder at myFolder with properties {name:folderName} -- Make new folder

end repeat

on getCellList(aLetter, startNumber, endNumber)
set myList to {}
repeat with i from startNumber to endNumber
set end of myList to (aLetter & i) as string
end repeat
return myList
end getCellList

Your script is very close to the goal. I tried to improve it a bit and add the functionality you requested:

-- IS ASSUMED THAT YOU HAVE OPENED THE FOLLOWING WORKBOOK
property myDoc : alias "Untitled:Users:123:Documents:My Workbook.xlsx" -- edit as need
property startRow : 1
property endRow : 5

-- Get "A" column's tracked cells list
set A_Collumn to my getCellList("A", startRow, endRow)
-- Get container folder of document file
tell application "Finder" to set sourceFolder to (container of myDoc) as alias

repeat with i from startRow to endRow
	tell application "Microsoft Excel" to tell document (myDoc as string) to tell sheet 1
		set A_CellValue to value of cell (item i of A_Collumn) -- value of cell i of column A
	end tell
	if A_CellValue is not "" then
		try
			tell application "Finder"
				set newFolder to make new folder at sourceFolder with properties {name:A_CellValue}
				make new folder at newFolder with properties {name:"myNewSubfolder_1"}
				make new folder at newFolder with properties {name:"myNewSubfolder_2"}
			end tell
		end try
	end if
end repeat

-- tell application "Microsoft Excel" to save active workbook in myDoc 

on getCellList(aLetter, startNumber, endNumber)
	set myList to {}
	repeat with i from startNumber to endNumber
		set end of myList to (aLetter & i) as string
	end repeat
	return myList
end getCellList

With just 5 rows it probably won’t make much of a difference, but with longer data sets it might be a good idea to get accessing Excel out of the loop part of your code. And per the original code all the values pulled from the worksheet are a contiguous range in a single column, so the getCellList() handler is completely unnecessary.

Here’s my version:

-- IS ASSUMED THAT YOU HAVE OPENED THE FOLLOWING WORKBOOK
property myDoc : alias "Untitled:Users:123:Documents:My Workbook.xlsx" -- edit as need
property startRow : 1
property endRow : 5

-- Get "A" column's tracked cells list
tell application "Microsoft Excel" to tell document (myDoc as string) to tell sheet 1
	
	-- just build the range reference as a string "A1:A5"
	set A_Collumn to string value of (range ("A" & startRow & ":A" & endRow))
	-- now A_Collumn is a list of lists {{"cell1"}, {"cell2"}, ... {"cell5"}}
		
end tell

-- Get container folder of document file
tell application "Finder" to set sourceFolder to (container of myDoc) as alias

repeat with i from startRow to endRow
	set A_CellValue to item 1 of (item i of A_Collumn)
	if A_CellValue is not "" then
		try
			tell application "Finder"
				set newFolder to make new folder at sourceFolder with properties {name:A_CellValue}
				make new folder at newFolder with properties {name:"myNewSubfolder_1"}
				make new folder at newFolder with properties {name:"myNewSubfolder_2"}
			end tell
		end try
	end if
end repeat

-- tell application "Microsoft Excel" to save active workbook in myDoc

Hi Guys,

The above is amazing and works for me.

I cant thank you both enough.

Now all i need to do is set it as an automatic script that just runs everytime I enter a new value in excel so that I dont have to physically run the script each time.

I presume I could do this by adding it into Automator correct?

No, to automate process fully you need other approach: make the script above stay-open application. Then you start it once, when you want begin make changes in excel document. The application will trigger folders creating each time you press Enter when you end with cell contents changing.

-- IS ASSUMED THAT YOU HAVE OPENED THE FOLLOWING WORKBOOK
property myDoc : alias "Untitled:Users:123:Documents:My Workbook.xlsx" -- edit as need
property startRow : 1
property endRow : 5
global A_Collumn, sourceFolder

on run
	-- Get "A" column's tracked cells list
	tell application "Microsoft Excel" to tell document (myDoc as string) to tell sheet 1
		-- just build the range reference as a string "A1:A5"
		set A_Collumn to string value of (range ("A" & startRow & ":A" & endRow))
		-- now A_Collumn is a list of lists {{"cell1"}, {"cell2"}, ... {"cell5"}}
	end tell
	-- Get container folder of document file
	tell application "Finder" to set sourceFolder to (container of myDoc) as alias
end run


on idle
	repeat with i from startRow to endRow
		set A_CellValue to item 1 of (item i of A_Collumn)
		if (A_CellValue is not "") and (active cell is not cell (item i of A_Collumn)) then
			try
				tell application "Finder"
					set newFolder to make new folder at sourceFolder with properties {name:A_CellValue}
					make new folder at newFolder with properties {name:"myNewSubfolder_1"}
					make new folder at newFolder with properties {name:"myNewSubfolder_2"}
				end tell
			end try
		end if
	end repeat
	delay 1
end idle

on quit
	tell application "Microsoft Excel" to save active workbook in myDoc
end quit

Thank you KniazidisR

I will investigate how to do that.

Much appreciated

Hi KniazidisR

One last question if I ma.

How would I make a further sub folder within this newly created folder?

     make new folder at newFolder with properties {name:"myNewSubfolder_1"}

I have also saved the script as a stay open application but for some reason it doesnt create a folder when i enter new fields in the spreadsheet. I need to re-open the application and run it again for it to pick up the new entries (defeating the point.)

Any further assistance on this would be amazing

Thank you in advance