Batch edit xlsx files in iOS?

I’ve got hundred of .xlsx files I downloaded out of Google sheets. I need to delete all columns but one (Column B) from all of them. Is there a way to do this with iOS, Numbers app, Etc? Also, open to OSX suggestions as well.

I’m pretty sure this can be done on a Mac using Numbers and AppleScript. I can give it a shot this weekend, but…

It would be much easier to do this—and could even be done on iOS—if the spreadsheets can be converted to CSV files. I’m guessing that’s out of the question, as you probably can’t automate the redownloading of all those spreadsheets, but if can be done, I’d rather not mess around with AppleScript.

2 Likes

Actually, the files are still in google sheets and can be downloaded one at a time as csv. I’m learning keyboard maestro now and think there might be a way to automate downloading them into that format.

Will try that first and report back here when I figure that out. Thanks for chiming and giving that idea.

Agree on do it with the CSV if at all possible - particularly as Python support for CSV is pretty good and there is Pythonista on iOS. (I rely on it in my code.)

I would, however, be interested in some Applescript @drdrang, partly because I want similar function but also because there are so few good Applescript examples for Office Suite products.

Since @Martin_Packer asked for it, and I see by a question at the Keyboard Maestro forum that @Bounce is having trouble getting CSV versions of his spreadsheets, here’s an AppleScript that opens a folder full of Excel files and saves Column B of each of them to a subfolder. Here are the rules/caveats:

  1. All of the Excel folders must be at the top level of the folder, not in subfolders. The original files are left as-is. The new files with only Column B are saved to a subfolder called “Column B.” That subfolder must not exist before you run the script.
  2. The new files are in Numbers format. If you prefer them in Excel format, you can comment out the three lines that end with “for saving as Numbers” and uncomment the two lines that end with “for exporting to Excel.”
  3. The new files have the same name as the original. If you want to change that, you’ll have to fiddle with the code inside the repeat with f in excelFiles block.
  4. The script assumes Numbers is already running. It does not include a delay to accommodate the time needed to launch Numbers.
  5. You may need to add a delay line to handle the time it takes Numbers to open a large Excel file. There was no need for a delay in my tests, but I don’t have any large Excel files to test on.
  6. When you run the script, it starts by asking you to select the folder that contains the Excel files. After that, it runs on its own.

Good luck!

use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions

-- User selects the folder with all the Excel files
set the excelFolder to choose folder with prompt ¬
	"Which folder?" default location (path to desktop folder)

-- Create new subfolder for the Column Bs and set the names of the new files
tell application "Finder"
	set colBFolder to make new folder at excelFolder with properties {name:"Column B"}
	set excelFiles to every file in excelFolder whose name extension is "xlsx"
	set colBNames to {}
	repeat with f in excelFiles
		set oldName to name of f
		set newName to text 1 thru ((get offset of ".xlsx" in oldName) - 1) of oldName & ".numbers" -- for saving as Numbers
		set end of colBNames to newName -- for saving as Numbers
		-- set end of colBNames to oldName -- for exporting to Excel
	end repeat
end tell -- Finder

-- Open each original file in Numbers, delete all the columns except B
-- and save to the "Column B" subfolder
repeat with i from 1 to count of excelFiles
	set oldFile to (item i of excelFiles) as alias
	set newFileName to (colBFolder as text) & (item i of colBNames)
	tell application "Numbers"
		activate
		open oldFile
		tell table 1 of sheet 1 of document 1
			set lastCol to column count
			delete ( columns 3 thru lastCol )
			delete column 1
		end tell -- table/sheet/document
		save document 1 in file newFileName -- for saving as Numbers
		-- export document 1 to file newFileName as Microsoft Excel -- for exporting to Excel
		close document 1 saving no
	end tell -- Numbers
end repeat
2 Likes

Thank you! I hope you had a fun weekend doing this. :slight_smile:

Maybe you can’t answer this @drdrang but is the automation support better in Numbers than in Excel-on-Mac?

I would consider round-tripping an Excel spreadsheet through Numbers if I thought it would buy me much.

(Ultimately the reality of my situation is it’s unrealistic to store my data permanently in Numbers files; I’m going to have to end up in Excel anyway. But the less of my processing I do there probably the better.)

I prefer the AppleScript dictionary in Numbers because it’s more Apple-y and is therefore more like what I’m used to. For example, when rewriting the script for Excel, I got errors when trying to refer to the frontmost spreadsheet as document 1. That construct has worked with every other application I’ve dealt with, but as you’ll see, I couldn’t use it with Excel.

That said, it wasn’t difficult to convert the script from Numbers to Excel. Looking back, it was silly of me to write it to use Numbers in the first place. Of course someone with a bunch of Excel files is likely to be more comfortable with an Excel solution Not sure what I was thinking.

use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions

-- User selects the folder with all the Excel files
set the excelFolder to choose folder with prompt ¬
	"Which folder?" default location (path to desktop folder)

-- Create new subfolder for the Column Bs and set the names of the new files
tell application "Finder"
	set colBFolder to make new folder at excelFolder with properties {name:"Column B"}
	set excelFiles to every file in excelFolder whose name extension is "xlsx"
	set colBNames to {}
	repeat with f in excelFiles
		set end of colBNames to (name of f)
	end repeat
end tell -- Finder

-- Open each original file in Numbers, delete all the columns except B
-- and save to the "Column B" subfolder
repeat with i from 1 to count of excelFiles
	set oldFile to (item i of excelFiles) as alias
	set newFileName to (colBFolder as text) & (item i of colBNames)
	tell application "Microsoft Excel"
		activate
		open oldFile
		tell active sheet
			tell used range
				set lastCol to count of columns
			end tell
			repeat (lastCol - 2) times
				delete column 3
			end repeat
			delete column 1
		end tell -- active sheet
		save as active sheet filename newFileName
		close active workbook
	end tell -- Excel
end repeat
1 Like

I’m going to have a look at this after work today, thank you!

After finding time to try it out last night, wanted to chime in and thank you again. It seems to work great. I realized that some files I need to save columns C as well, so I will dig through the details of the Apple script and try to do that. Cheers