I have a massive spreadsheet with a titanic number of rows/columns (e.g. ~250 columns, many thousands of rows) that I'm trying to convert into PDFs by looping through each row with AppleScript, copying that row's ~250 variables to TextEdit set to Rich Text (for bold formatting etc), and then using System Events to save the txt as a PDF. Here's a summary of the code:
on run
set initialRow to 1
tell application "System Events"
tell application process "TextEdit"
set frontmost to true
end tell
end tell
repeat
tell application "Microsoft Excel"
-- CLEAR MY ~250 VARIABLES FROM PREVIOUS ROW'S VALUES TO MAKE SURE NOTHING IS CARRIED OVER BY MISTAKE
-- THEN SET MY ~250 VARIABLES TO THE NEXT ROW'S VALUES
if exampleValue is "" then exit repeat
end tell
tell application "TextEdit"
set the text of the front document to ""
-- THEN SET FIRST PARAGRAPH TO MY FIRST VARIABLE PLUS A LINE BREAK SO THEN THERE'S A NEW PARAGRAPH FOR THE NEXT VARIABLE, ETC
-- THEN GO THROUGH ALL OF MY VARIABLES TO IMPORT THE IMPORTANT ONES INTO TEXTEDIT, SET SOME FORMATTING, ETC.
end tell
delay 1
tell application "System Events"
click menu item "Export as PDF…" of menu 1 of menu bar item "File" of menu bar 1 of application process "TextEdit"
delay 1
keystroke exampleValue -- SYSTEM EVENTS TYPES THE NAME OF THE PDF
delay 1
key code 36
delay 1
end tell
set myRow to (myRow + 1)
end repeat
end run
This all runs great, no bugs (seemingly!), no issues at all in small doses. The problem, however, is that something happens as the script runs where it seems to be tying up more and more memory somewhere; everything is fine for the first hundred or so rows, but at some point my Mac stops running anything at all, i.e. whether I let the script run until it starts producing super random errors (I could collect them if helpful, but it's like a random different error each time so not much help there) or even if I let the script run for a while and then stop it before it errors out - it will let me stop the script but then I can't actually quit out of Script Editor or TextEdit or Excel, my keyboard stops working, I can't Force Quit anything, can't Reset the computer, etc. It's just a complete meltdown of the machine unlike anything I've encountered, and the only way to get back to work is to force a hard boot with the power button.
I've never had this problem with my other scripts, but I also don't usually use System Events, so my hunch is that it's something to do with that. Do I need to be 'resetting' System Events somehow, or clearing out the memory for some reason, or...? Thanks for the help!!
