Spreadsheet Tricks - How To?

[lxskllr]

Figured I'd ask here to get spoonfed before looking, cause doing this manually is easy enough, but it would be better having the computer do it. I have a spreadsheet that looks like this...



Each sheet is dedicated to Trek series. Each week, I make a list in a text file that looks like this...



What I would like to do is automate this. It's a daily schedule for H&I. It's basically take 1 show from each sheet I specify, append it in a text file in order, increment by 1, and do it again 5 more times for 6 blocks total. Then remember it's place for the next time I do it. I suppose I could do a year of sheets in advance and give each sheet a unique name(maybe weeknn.txt), but my preference is for a single file that gets modified each week.

Anyone know how to do this, or have ideas for starting research? This isn't a huge priority cause it isn't difficult to manually do, but I wouldn't mind putting some time into it to make it even easier.

Edit:
If anyone wants the spreadsheet, it'll be up here for 30 days...

Lufi - Disroot file uploader

upload.disroot.org

Maybe someone will find it useful for something. Everything is hyperlinked to wikipedia when available.

And if anyone wants to get involved in the mastodon watch party, you can follow this group for notifications...

Mastodon

mastodon.world

 

[Red Squirrel]

If you can export it as a CSV, it should be fairly easy to just script something, maybe using python.

I assume you're doing this to track which shows you already watched? Another totally different option might be to just setup a Jellyfin server. You can access it from a web browser and it will track what you watched already.

 

[lxskllr]

I assume you're doing this to track which shows you already watched?

It's the daily H&I schedule that plays Sunday-Friday. The leader of the group posts the schedule each day, and I post the same with the season and episode numbers for people that don't have H&I. Someone expressed interest in this, and I commented that someone clever could probably scrape wikipedia and arrange the data. I was laying in bed that night thinking "I'm probably clever enough to do that" and took up the job. Instead of learning computer tricks though, I just manually did it. It isn't something I'd ever use for anything else, and by the time I learned how to automate it, I could have just done it by hand, so that's what I did.

edit:
and the reason I do a week at a time is so I don't have to keep track on a daily basis. I make a txt file, put it on nextcloud, and I can post from my phone if I'm not near a computer. I always post as a reply to the Captain's post as a sanity check for proper listing. They /usually/ go in broadcast order, but sometimes they mistakenly list the wrong episode(I'm correct), or they intentionally go out of order for a reason(much rarer). Also, rarely the Captain's wrong, and I pull up the H&I schedule to check. Just going in broadcast order works most of the time though.

 

[BoomerD]

Not my forte, but this looks like something that would be better in a database than spreadsheet. (MS access rather than Excel)

Since you use libre office... https://www.libreoffice.org/discover/base/

 

[lxskllr]

I'll probably go the csv route if I take it on. I was thinking that anyway. With csv you can use ordinary text manipulation tools, and gnu/linux has lots of those built in.

I chose a spreadsheet to compile the data cause it makes nice orderly lists. The workflow to get the stuff in a txt file is pretty straightforward, and it only takes about 10 minutes to setup a week of shows.

 

[Kaido]

Anyone know how to do this, or have ideas for starting research? This isn't a huge priority cause it isn't difficult to manually do, but I wouldn't mind putting some time into it to make it even easier.

Just have ChatGPT build a custom PowerShell script & add it to the Task Scheduler! (or Bash & Crontab, or AppleScript & Automator). Python also plays well with AI!

 

[lxskllr]

That's cheating! :^D

I didn't consider that, but I may try that route. It goes against my 'fuck ai' policy though. That'll be my last attempt if I make an attempt at all.

 

[Ken g6]

I think I got it! I had to add a distinct episode number to each table, and do a vlookup based on TODAY(). "Based on" being the tricky part, because I happen to know H&I shows six episodes over 7 days - nothing on Saturday. So, like ((floor(TODAY()/7) * 6) + {OFFSET}).

StarTrekBroadcast.ods

drive.google.com

The ep_listing tab is what you want. The ep_calc tab does the calculations. Change the offsets on that page if things get out of sync.

 

[lxskllr]

Thank you! I'm gonna have to spend some time studying it to see exactly what you did, but it works!

 

[KLin]

I would have skipped all of this by signing up for Paramount plus. But that's just me.

https://www.paramountplus.com/collections/star-trek/

 

[lxskllr]

That's why you need my list! It's a watch party pegged to H&I, so people that don't have H&I can follow.

I actually don't watch with everyone. When I watch Trek, I watch a single series at a time, and I watch in order starting from ep01. I just do this to be useful. Voyager is catching up to where I left off last fall, so I may watch it with others when it hits the ep I left off on. I lost the mood for video then, and just want to listen to music and mindlessly click around online. Maybe watching with others will get me back in the groove.

 

[BoomerD]

That's why you need my list! It's a watch party pegged to H&I, so people that don't have H&I can follow.

I actually don't watch with everyone. When I watch Trek, I watch a single series at a time, and I watch in order starting from ep01. I just do this to be useful. Voyager is catching up to where I left off last fall, so I may watch it with others when it hits the ep I left off on. I lost the mood for video then, and just want to listen to music and mindlessly click around online. Maybe watching with others will get me back in the groove.

There's a channel for that crap? I get watching the Star Trek stuff...some is very good, some sucks...but most of the crap on their lineup...just junk.

 

[nakedfrog]

Had to do a search to see what this H&I OP kept referencing is (Heroes and Icons channel apparently)

 

[lxskllr]

Had to do a search to see what this H&I OP kept referencing is (Heroes and Icons channel apparently)

H&I | Schedule

www.handitv.com