Sheet

mash.sheet · Files & Storage

mash.sheet fills in an existing Excel workbook and saves it, leaving everything you didn’t touch exactly as it was: charts, pivot tables, conditional formatting, macros. Use it for a template that people have already made look right. Use mash.xlsx to read rows out of a workbook, or to build a plain new one.

How it works

Open, change, save. mash.sheet.open(file) takes an .xlsx or .xlsm file and gives you a workbook handle. set("B2", value) queues a change. get("B2") and values("A1:C10") read cells, including your pending changes. save(folder, name) writes the result into a folder. The original isn’t changed unless you save over it.

Formulas recalculate. The workbook engine works the formulas out when you read a cell or save. It covers the common functions. A formula it can’t work out keeps its last saved value and is counted by flagged(). Excel recalculates those the next time somebody opens the file.

sheet picks the sheet. Every set and get after sheet("Costs") applies to that sheet, until you name another. Without one, the first sheet is used.

It needs the workbook engine. The workbook engine is part of the appliance and SaaS images. If it isn’t available the call stops with a message saying why.

Common tasks

Fill a template and save a copy next to it:

set book to mash.sheet.open(mash.files.get("Sales", "Templates/quote.xlsx"))
book.sheet("Quote")
book.set("B2", "Acme Ltd")
book.set("B3", 12)
book.set("B4", "=B3*49.5")
printline "Total: {{book.get('B4')}}"
book.save(mash.files.get("Sales", "Quotes"), "Quote Acme.xlsx")

Read a block of cells:

set book to mash.sheet.open(mash.files.get("Sales", "Reports/january.xlsx"))
each book.values("A1:C5") as row
    printline mash.collect(row).join(" | ")
end

Watch out for

• Nothing is written until save. A script that stops before save changes nothing.
• save overwrites a file of the same name unless you pass false as the third argument.
• Changes are queued. Cell values must be a number, text, true or false, or text that starts with = for a formula. A list or a date object is an error. null or "" clears the cell.
• A macro workbook keeps its macros only if it’s saved as .xlsm. The name you give save decides the extension.

See also

mash.xlsx, Files and folders.

Methods

open

sheet.open(file: File) → Handle

Open a workbook (.xlsx/.xlsm) for reading, filling and saving.