TL;DR In Shortcuts or AppleScript, how can I read the contents of the A2 cell in an excel file?
Hello Automators,
I’m hoping one of you clever people will have run into this before. Here’s my issue:
My work often involves ordering parts, and I have two systems that won’t talk to each other so I’m making a shortcut to save me some hand-editing of excel files. When I export a .xlsx file from our inventory system, it’s got a bunch of junk data in it and all I need is two of the columns, so I’ve made a Shortcut to use AppleScript to delete all columns except C and E, and also delete row 1 to get rid of headers (credit to Dr. Drang for some excellent examples posted on the forum!). So far so good.
The issue comes because I’m expanding the shortcut to deal with more suppliers, and they want different formats. I could just ask for the user to tell me which supplier this is for, but I’d like to detect the supplier and trim accordingly without bothering the user.
The name of the supplier is in the A2 cell. Is there a way to read this information using AppleScript? Once I have it I’d like to either A) pull it out into shortcuts and use an if statement to run different AppleScript actions to transform the file, or B) use logic in the AppleScript to do the same thing.
For some context, here is my hastily modified AppleScript that trims the file, mostly copy-pasted from this post by Dr. Drang:
on run {input, parameters}
tell application "Microsoft Excel"
activate
open input -- This is the filepath
tell active sheet
tell used range
set lastCol to count of columns
end tell
repeat (lastCol - 7) times
delete column 8
end repeat
delete column 1
delete column 1
delete column 2
delete column 2
delete column 2
delete row 1
end tell -- active sheet
save
close active workbook
end tell -- Excel
return input
end run
Here is my current pass at reading the A2 cell from the excel file, not working for reasons I don’t understand:
on run {input, parameters}
tell application "Microsoft Excel"
activate
open input
tell active sheet
set output to (cell "A2")
end tell
save
close active workbook
end tell
return output
end run