AppleScript - How to take row 1 and use as file name and row 2 for content to create json files

Question

I have a large amount of data (367 columns of two rows, but row 2 has a lot in it!) to export into json files for web display.

I'm trying to automate the process of saving each one as a .json file - ideally they need to be named Row 1 Value.json, and then contain the value of the corresponding cell in Row 2.

I've got this far in AppleScript, but it seems to fail at trying to get to the worksheet - I get an error saying "Can't get worksheet "HARDCODEJSON" of missing value." number -1728 from <> "HARDCODEJSON" of missing value

Any suggestions as to how to modify the AppleScript would be well received. I've altered the file paths but that's it.

tell application "Microsoft Excel"
    set workbookName to POSIX file "/Users/myname/Documents/exportedstreams/AllStreamsFlatFile.xlsx"
    set sheetName to "HARDCODEJSON"
    set filePath to POSIX file "/Users/myname/Documents/exportedstreams/"
    
    -- Open the workbook file directly
    set workbookFile to open file workbookName
    set workbookObj to workbook of workbookFile
    
    -- Get the range of data from the specified sheet
    set sheetObj to get worksheet sheetName of workbookObj
    set dataRange to value of used range of sheetObj
    
    -- Loop through each column in the data range
    repeat with columnIndex from 1 to count of columns of dataRange
        set fileName to item columnIndex of item 1 of dataRange
        set fileContent to item columnIndex of item 2 of dataRange
        
        -- Save the content as a JSON file
        set fileRef to open for access ((filePath as text) & fileName & ".json") with write permission
        set eof fileRef to 0
        write (fileContent as text) to fileRef
        close access fileRef
    end repeat
    
    -- Close the workbook file
    close workbookFile saving no
end tell

Answer 1

Try this:

Excel sometimes uses 'name' when it means 'path+name' so I changed your variable to be more explicit.

set wbPath to POSIX file "/Users/myname/Documents/exportedstreams/AllStreamsFlatFile.xlsx"
set sheetName to "HARDCODEJSON"

tell application "Microsoft Excel"
    open wbPath -- née workbookName
    
    set wbName to name of workbook 1
    --> "AllStreamsFlatFile.xlsx"
    set wbObj to workbook wbName
    --> workbook "AllStreamsFlatFile.xlsx"
    
    set sheetObj to worksheet sheetName of wbObj
    set dataRange to value of used range of sheetObj
    
end tell

Notes: wbPath is a file url, not an excel object — you can't ask excel for its properties (e.g. workbook, worksheet) because it doesn't have any. Hence, your missing value error. Once the file has been opened, and thus become an excel object (a workbook), you can access its name and worksheet properties.

On another note, near the top of your script, you use 'posix file' with your filePath variable. I'd leave it as a string unless you actually need to use the file reference. Later in the script you coerce it to text anyway so there really isn't a point.

set filePath to "/Users/myname/Documents/exportedstreams/"

Alternate approach: open the spreadsheet as a workbook

set wbPath to POSIX file "/Users/myname/Documents/exportedstreams/AllStreamsFlatFile.xlsx"
tell application "Microsoft Excel"
    set wbObj to open workbook workbook file name wbPath
    --> workbook "AllStreamsFlatFile.xlsx"
    
    -- returns…
    wbObj
    --> workbook "AllStreamsFlatFile.xlsx" of application "Microsoft Excel"
end tell