Wednesday, November 20, 2019

#1 2019-10-21 09:11:11 am

Stu_Guy
Member
Registered: 2019-10-17
Posts: 5

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


Filed under: excel, Automator

Offline

 

#2 2019-10-21 12:52:17 pm

KniazidisR
Member
Registered: 2019-03-03
Posts: 720

Re: Create new folder from excel spreadsheet column automatically

Stu_Guy wrote:

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.


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.

Last edited by KniazidisR (2019-10-21 01:15:47 pm)


Model: MacBook Pro
macOS Mojave -- version 10.14.4
Safari -- version 12.1
Firefox -- version 70.0

Offline

 

#3 2019-10-22 08:42:08 am

Stu_Guy
Member
Registered: 2019-10-17
Posts: 5

Re: Create new folder from excel spreadsheet column automatically

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


KniazidisR wrote:


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.



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

Offline

 

#4 2019-10-22 10:23:04 am

KniazidisR
Member
Registered: 2019-03-03
Posts: 720

Re: Create new folder from excel spreadsheet column automatically

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

Applescript:


-- 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

Last edited by KniazidisR (2019-10-22 01:39:16 pm)


Model: MacBook Pro
macOS Mojave -- version 10.14.4
Safari -- version 12.1
Firefox -- version 70.0

Offline

 

#5 2019-10-22 09:26:15 pm

roosterboy
Member
Registered: 2010-04-19
Posts: 10

Re: Create new folder from excel spreadsheet column automatically

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:

Applescript:


-- 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

Offline

 

#6 2019-10-23 06:33:53 am

Stu_Guy
Member
Registered: 2019-10-17
Posts: 5

Re: Create new folder from excel spreadsheet column automatically

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?

Offline

 

#7 2019-10-23 07:06:50 am

KniazidisR
Member
Registered: 2019-03-03
Posts: 720

Re: Create new folder from excel spreadsheet column automatically

Stu_Guy wrote:

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.

Applescript:


-- 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

Last edited by KniazidisR (2019-10-23 07:24:54 am)


Model: MacBook Pro
macOS Mojave -- version 10.14.4
Safari -- version 12.1
Firefox -- version 70.0

Offline

 

#8 2019-10-23 07:21:02 am

Stu_Guy
Member
Registered: 2019-10-17
Posts: 5

Re: Create new folder from excel spreadsheet column automatically

Thank you KniazidisR

I will investigate how to do that.

Much appreciated

Offline

 

#9 2019-10-25 08:59:01 am

Stu_Guy
Member
Registered: 2019-10-17
Posts: 5

Re: Create new folder from excel spreadsheet column automatically

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

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)