Automating Numbers for Invoices

Oops…

You could use an invoice document as a template, then try something like this:

property theInvoiceNumber : 0

set theInvoiceNumber to (theInvoiceNumber + 1) as text

set theInvoiceNumber to text 1 through (5 - (counttheInvoiceNumber)) of "00000" & theInvoiceNumber

tell application "Numbers"

launch

activate

makenewdocumentwith properties {document template:template "New Invoice"}

set name of table 1 of sheet 1 of document 1 to "Invoice #" & theInvoiceNumber

tell application "System Events" to keystroke "a" using {shift down, command down}

end tell

thedodoman wrote:

- it gives me this error : "Numbers got an error: Can’t set cell \"A2\" to \"Invoice #00001\"." number -10006 fromcell "A2"

You should have written:

set value of cell "A2" of table 4 of sheet 1 of document 1 to "Invoice #" & theInvoiceNumber

thedodoman wrote:

That is super helpful. Is it possible to make a new sheet every time instead of a document?

Then, no need for a template document. Here's what I suggest: use a Numbers document whose first sheet will serve as a template for the other invoices. In the script below, I have put such a document onto the Desktop. The script seems to work flawlessly under OS X 10.11.3 with Numbers 3.6.1.

set myInvoices to alias "Macintosh HD:Users:username:Desktop:My invoices.numbers"

tell application "Numbers"

launch

activate

set thisDocument to openmyInvoices

-- Copy the tables of sheet 1:

tell thisDocument to set active sheet to sheet 1 -- sheet 1 is used as a template for the other sheets

tell application "System Events" to tell process "Numbers"

keystroke "a" using {command down} -- select all

keystroke "c" using {command down} -- copy

keystroke "a" using {shift down, command down} -- unselect all

end tell

-- Get the number of the next invoice (one sheet per invoice):

set N to (countsheets of thisDocument)

set theInvoiceNumberAsText to text 1 through (5 - (count (N + 1) as text)) of "00000" & N

-- Make a new sheet:

tell thisDocument to set active sheet to sheetN

tell thisDocument to makenewsheetwith properties {name:theInvoiceNumberAsText}

set theNewSheet to result

-- Replace the default table with the tables of sheet 1:

deletetable 1 of theNewSheet

tell application "System Events" to keystroke "v" using {command down} -- paste

-- Last steps:

set value of cell "A2" of table 4 of theNewSheet to theInvoiceNumberAsText

tell application "System Events" to keystroke "a" using {shift down, command down} -- unselect all

end tell

Please replace (N+1) with N in the previous script.

Here is a slightly different approach that also uses a "model" invoice in a sheet named "000000" (with all the necessary tables and formulas in place) and increments by 1 when adding a new invoice in a sheet. (If you don't want to start with invoice 000001 change the model sheet name and alsosetactive sheettosheet "00000" to the script).

The script assumes the "highest" invoice number is always the last sheet in the document, i.e. don't move it somewhere else.

SG

tell application "Numbers"

tell front document

set nsn to (last sheet's name) + 1 as text

set nsn to text 1 thru (5 - (nsn's length)) of "00000" & nsn

set newSheet to makenewsheetwith properties {name:nsn}

tell newSheet to deletetables-- remove default table

--copy tables from "model" sheet named "000000"

set active sheet to sheet "00000"

activate

tell application "System Events"

keystroke "a" using {command down}

keystroke "c" using {command down}

end tell

--paste tables into new sheet

activate

set active sheet to newSheet

tell application "System Events" to ¬

keystroke "v" using {command down}

-- insert invoice # in desired cell in table 4

tell newSheet to tell table 4 to ¬

set cell "B2"'s value to "Invoice #: " & parent's name

end tell

end tell

The invoice numbering works better if this line:

setnsntotext 1 thru (5 - (nsn'slength)) of "00000" & nsn

is replaced with this:

set nsn to text -5 thru -1 of ("000000" & nsn)

Also, I see there is no need to change the sheet name 00000 for the "template" invoice if you want to start with a higher invoice number than 00001. Just rename the new sheet after running the script the first time to the 5-digit "number" (with leading zeros) that you want. The script will increment from there.

SG

First, here's a slightly improved version of my previous script:

set myInvoices to alias "Macintosh HD:Users:username:Desktop:My invoices.numbers"

tell application "Numbers"

launch

activate

set thisDocument to openmyInvoices

-- Copy the tables of sheet 1:

tell thisDocument to set active sheet to sheet 1 -- sheet 1 is used as a template for the other sheets

tell application "System Events" to tell process "Numbers"

keystroke "a" using {command down} -- select all

keystroke "c" using {command down} -- copy

keystroke "a" using {shift down, command down} -- unselect all

end tell

-- Get the number of the next invoice (one sheet per invoice):

set N to (countsheets of thisDocument)

set theInvoiceNumberAsText to "Invoice #" & text 1 through (5 - (countN as text)) of "00000" & N

-- Make a new sheet:

tell thisDocument to set active sheet to sheetN

tell thisDocument to makenewsheetwith properties {name:theInvoiceNumberAsText}

set theNewSheet to result

-- Replace the default table with the tables of sheet 1:

deletetable 1 of theNewSheet

tell application "System Events" to keystroke "v" using {command down} -- paste

-- Last steps:

set value of cell "A2" of table 4 of theNewSheet to theInvoiceNumberAsText

tell application "System Events" to keystroke "a" using {shift down, command down} -- unselect all

end tell

Secondly, another script which requires cliclick 3.2 to be installed beforehand:

set myInvoices to alias "Macintosh HD:Users:username:Desktop:My invoices.numbers"

tell application "Numbers"

launch

activate

set thisDocument to openmyInvoices

-- Get the number of the next invoice (one sheet per invoice):

set N to (countsheets of thisDocument)

set theInvoiceNumberAsText to "Invoice #" & text 1 through (5 - (countN as text)) of "00000" & N

-- Duplicate sheet 1:

tell thisDocument to set active sheet to sheet 1 -- sheet 1 is used as a template for the other sheets

tell application "System Events" to tell process "Numbers" to ¬

set {x, y} to position of UI element 1 of scroll area 2 of window 1 whose name is "Sheet 1"

do shell script "/usr/local/bin/cliclick kd:ctrl c:" & x & "," & y & " ku:ctrl"

tell application "System Events" to keystroke "Duplicate" & return

set name of sheet 2 of thisDocument to theInvoiceNumberAsText

-- Last steps:

set value of cell "A2" of table 4 of the active sheet of thisDocument to theInvoiceNumberAsText

tell application "System Events" to keystroke "a" using {shift down, command down} -- unselect all

end tell

It was my pleasure. Many thanks for your feedback.

Hi there, where on a sheet would you insert this code?