Add free space of all drives to Excel document when they are connected

Hi.
I was looking at the following posting and responses regarding this, and wondered whether anybody could help me to adapt this to what I need.

http://macscripter.net/viewtopic.php?id=21991

I already have a hard drive log of 40+ external drives, on which I would like to document the current free space of each in one of the columns. I could do this by hand when I connect the drives up to look at the projects stored on them, but it would be amazing if I could find an automated way of adding this information to the existing document, and running the script to do so automatically when any volumes are mounted in Finder.

I wonder if there is a way of making the script find the mounted volume name’s corresponding row in an Excel sheet, and add the info gained through the script into an appropriate (fixed) column in that document? I guess the first part would be to get the info into a spreadsheet at all, as I imagine the plugging into specific columns based on another columns content may be a little more difflicult. Perhaps the creation of one document containing the raw data from the script, and then separate automation (perhaps within excel itself) to pull this information into the right place in the finished document would be possible.

The columns in the current finished document are basically as follows (underlined are current fields, and bold is the one required):

DRIVE NUMBER (also mounted volume name) // DRIVE MODEL // LOCATION // SIGNED OUT BY // PROJECTS CONTAINED ON DRIVE // *FREE SPACE ON DRIVE (GB & %)

The aforementioned post got quite far into this, and I wonder if there is a way of adapting the good work they have already done on this, and making it perform these last functions.

I hope somebody has the time to assist me with this, as it would be incredibly helpful for me at work.

Thanks a lot.

Kind regards,

  • Ian

Model: Macbook Pro Retina
AppleScript: Applescript Editor v2.5
Browser: Safari 536.26.17
Operating System: Mac OS X (10.8)

Hi Ian,

The script below should give you a start!
It relies on you having your spreadsheet open in Excel, that in column A the names of volumes are listed and column F is where the remaining space will be entered.

set last_drive_attached to do shell script "mount | tail -1 | cut -d' ' -f3"
set mountedVolName to do shell script "basename " & quoted form of last_drive_attached
set spaceRemaining to do shell script "df -h " & quoted form of last_drive_attached & " | grep '/Volumes/' | awk '{printf $4}'"

tell application "Microsoft Excel"
	set myref to active sheet of active workbook
	repeat with i from 2 to count of rows of used range of myref --> ignore row 1 as it's usually a header row
		set thisVolName to string value of cell ("A" & i)
		if mountedVolName is thisVolName then
			display dialog thisVolName & return & "was the last drive mounted"
			set value of cell ("F" & i) to spaceRemaining
		end if
	end repeat
end tell

If this works for you then you could maybe get it to run but incorporating it in a launch deamon that runs when a drive is attached.

Hope this helps,
Nik

Hi there,
Thanks so much for your response. I apologise but I didn’t sees that you had posted this for ages.

Unfortunately, when I try this out, the only events/replies in the applescript window are those at the bottom of this post.

I wonder if it is the first shell script that is not returning a response properly? Do you have any other thoughts?

Thanks very much.

Kind regards,

  • Ian

tell current application
do shell script “mount | tail -1 | cut -d’ ’ -f3”
→ “on”
do shell script “basename ‘on’”
→ “on”
do shell script “df -h ‘on’ | grep ‘/Volumes/’ | awk ‘{printf $4}’”
→ “”
end tell
tell application “Microsoft Excel”
get active sheet of active workbook
→ active sheet of active workbook
count every row of used range of active sheet of active workbook
→ 1
end tell

Hi,

try changing the -f3 value to -f4 and see if that makes any difference. It looks like it’s cutting the wrong section out of the mount log. It works fine on my machine but your mount log maybe different to mine. Here’s an example of an entry in my mount

afp_3fCr96000gj40000oM0000VU-1.2d00000f on /Volumes/chrisf (afpfs, nodev, nosuid, mounted by nikj)

as you can see -f3 refers to the 3rd word in the line which is the mount i.e. /Volumes/chrisf

Hope this helps.

Thanks,
Nik