Exporting PDF's in Numbers with AppleScript

Question

I'm adapting code I found on iWork automation to fit my needs with exporting PDF's from AppleScript and may have run in to some limitations of the software but wanted to see if anyone here can provide some help..

Problem 1 - I have 2 sheets in my spreadsheet. Sheet number 1 contains tasks, sheet number 2 is the invoice which references the first sheet to fill in the tables. With the code I have, it duplicates the spreadsheet and erases sheet #1 in order to only export the invoice - this is the only workaround I have found in terms of AppleScript exporting a single sheet. How would I be able to bake the formula in to the table of the invoice so that when sheet #1 is deleted, the contents stays?

Problem 2 - I'd like to add a custom tag on the exported PDF, i.e 'invoice pending payment', but as far as I know we are limited to using apple's integrated color tags, is this the case? I know there is the program 'Hazel' which achieves what I want, but I'd rather script it myself than use another app.

Problem 3 - Is there any way of changing this script so that it doesn't pull up Numbers in the GUI and completely run in the background on command line?

The script I am working with is shown below.

Thanks.

set {year:y, month:m, day:d} to (current date)
set dateString to (d) & "_" & (m as integer) & "_" & (y as text)
set destinationFolder to ("Macintosh HD:Users:Test:Finance:Income:Invoices:" & year of (current date) & ":") as text
set theFile to choose file of type "numbers"

tell application "Finder"
    set docName to name of theFile
    if docName ends with ".numbers" then ¬
        set docName to text 1 thru -9 of docName & "_" & dateString & "_invoice"
    set theDuplicate to duplicate file (theFile as text) -- create duplicate
    try -- try block, because maybe destination file already exists
        make new file at folder destinationFolder with properties {name:(docName & ".pdf")}
    end try
end tell

tell application "Numbers"
    activate
    set Doc to open (theDuplicate as text as alias) -- open the duplicate 
    delete sheet 1 of Doc
    set PDFExportFileName to destinationFolder & docName & ".pdf"
    export Doc to file PDFExportFileName as PDF
    close documents saving no -- quit without saving
end tell

-- delete the temporary duplicate (if need)
tell application "System Events" to delete file (theDuplicate as text)

Answer 1

Set-up: One doc, with two sheets, one table on each sheet. Cells of column 3 of sheet 2 contain formulae which refer to cells on sheet 1.

1. Will loop through these cells and replace the formula with the 'formatted value'. Then, delete sheet 1, and export the remaining document to the desktop. If you have multiple columns with such formulae, then add a repeat loop for the relevant columns within the 'tell t21' block.

2. Alas, applescript can't directly access a file's extended attributes, however it can manage a shell script that could edit them (see 'xattr' and 'kMDItemUserTags'). But that's a bear and should be its own question. There are several such questions on stackexchange already although I don't recall seeing a complete answer.

3. These activities require that Numbers be open. It shouldn't need to be the frontmost app however, so an 'activate' line is optional.

Note the assumptions above, and that there is no error handling here.

tell application "Numbers" -- v5.1
    set w1 to window 1
    set d1 to document 1
    set s1 to sheet 1 of d1
    set t1 to table 1 of s1
    set s2 to sheet 2 of d1
    set t21 to table 1 of s2
    
    -- overwrite formula with value
    tell t21 -- sheet 2 tab 1
        repeat with rc3 from 1 to (count of rows in column 3) -- arbitrarily put formulae in column 3; modify to suit your data;
            set value of cell ("C" & rc3) to formatted value of cell ("C" & rc3)
        end repeat
    end tell
    
    -- export to pdf
    delete s1
    set dPath to path to desktop as text
    export d1 as PDF to dPath & "expo.pdf"
    close w1 -- will ask to save, choose revert
    
end tell

BTW, you needn't actually duplicate the file — just don't save it (or revert to last saved if you accidentally do). However, while there's nothing wrong with duplicating, this script assumes that you're working with a usable document.

Answer 2

I tried to fix your original script as little as possible and also added tagging functionality. Testing is left to you:

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

set {year:y, month:m, day:d} to (current date)
set dateString to (d) & "_" & (m as integer) & "_" & (y as text)
set destinationFolder to "" & (path to home folder) & "Test:Finance:Income:Invoices:" & year of (current date) & ":"
set theFile to choose file of type "numbers"

tell application "Finder"
    set docName to name of theFile
    if docName ends with ".numbers" then ¬
        set docName to text 1 thru -9 of docName & "_" & dateString & "_invoice"
    try -- because maybe destination file already exists
        make new file at folder destinationFolder with properties {name:(docName & ".pdf")}
    end try
end tell

tell application "Numbers"
    open theFile
    tell (table 1 of sheet 2 of document 1) -- overwrite formula with value
        repeat with rc3 from 1 to (count of rows in column 3) -- arbitrarily put formulae in column 3; modify to suit your data;
            set value of cell ("C" & rc3) to formatted value of cell ("C" & rc3)
        end repeat
    end tell
    delete sheet 1 of document 1
    set PDFExportFileName to destinationFolder & docName & ".pdf"
    export document 1 to file PDFExportFileName as PDF -- export to pdf
    close documents saving no -- quit without saving
end tell

-- add text tag
set aURL to current application's |NSURL|'s fileURLWithPath:(POSIX path of PDFExportFileName)
aURL's setResourceValue:{"invoice pending payment"} forKey:(current application's NSURLTagNamesKey) |error|:(missing value)