Create a Calc file with one sheet per item in a text list

[ultome]

I’m a teacher. I currently have two classes of the same level.

I’m trying automate the process of turning a simple text file containing the list of the names of students in one class into a spreadsheet with a page per student, and that follows a pre-established model, like so:

So far I wasn’t able to automatize it, and copying pages and names by hand takes a lot of time…

I have been considering writing a Bash script in Linux but the very kind folks over there at Stack Overflow dismissed my question within a few hours…

To summarize, I have a fixed list of student, that I can shape into any kind of list-supporting filetype, and I want to be able to copy a template (the details of the marks from a given text) to separate sheets with just the name changing according to my list, all of that in only one .ods file. I am mostly a Linux user, and I’m not afraid of a few lines of codes.

I hope I’m making sense, and I’m looking forward for your help.

[schiavinatto]

Take an example, like this text list.

[Wanderer]

If one wishes to avoid macros, you can reference a list of names on the first sheet via formula from the other sheets. I used the Sheet-Number here as reference.

=VLOOKUP(SHEET()-1;$Names.$A$1:$B$40;2;0)

Just copy the last empty sheet of the attached example to get a sheet for the next student.
.
Actually I recommend the macro-solution here, because it is more robust, if one deletes a sheet in the chain. An id# for the students may help also for this.

Sheet_per_Student.ods (13.4 KB)

[ultome]

Amazing, this works perfectly and it is super simple in addition to that. Thank you!

@Wanderer

Actually I recommend the macro-solution here, because it is more robust, if one deletes a sheet in the chain. An id# for the students may help also for this.

Erm, I’m willing to try the macro method, but I can’t figure out how to upload/write my script in LibreOffice… I’ve installed apso and jre, and I can select “Manage Python scripts” under “Tools"→"Marcros”, but from there how do I create my script? (@karolus I want to test your script)

Normally I’m a Linux person but right now my computer is in repairs so I’m temporarily working on Windows 11.

Thanks for your help!

[karolus]

Hallo
open your .ods file with predefined Template-sheet, and run:

def create_sheets_from_textfile(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sheets = doc.Sheets
    template_name = "Name of the Template-Sheet"
    with open("/home/ultone/Documents/students.txt") as students:
        for line in students:
            name = line.strip()            
            sheets.copyByName(template_name, name, sheets.Count)
            sheets[name]["A1"].String = name

edit the code to your needs, and run it, you may install apso.oxt for organizing you python-scripts

[ultome]

Amazing! So basically the language for… macros? — looks a lot like Python? (I have worked a lot with Python and I like that language)

A thousand thanks for the quick and straight to the point answer, I will try it first thing tomorrow!

And what a relief to have a forum with people that actually want to help, so different from the developers forums I’m used to, full of condescension and shaming…

I’ll keep you updated and come back here either to get more help or mark this answer as solution if I manage to make it work by myself the first time!

[Wanderer]

@ultome

So basically the language for… macros? — looks a lot like Python?

Actually this is Python. @karolus have chosen the language to implement his solution. The real obstacle is to find out the necessary functions of the big API (UNO) and get access to the necessary parts of the document (DOM in html or Javascript).
.
Don’t miss the hint at the end on APSO.

[ultome]

@Wanderer’s solution being much simpler, I will use it instead of your suggestion. But thank you anyway!

EDIT: (s)he seems to think your solution is more robust. I’m willing to try it but (see comment above) I can’t figure out how to begin… A little help is required!