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

 

[lxskllr]

Just a shout out to @Ken g6 for being all kinds of awesome. The spreadsheet has been working flawlessly. I still haven't dug into it to see how it works, but it definitely works. Thanks again for making my life better!

 

[RossMAN]

Just a shout out to @Ken g6 for being all kinds of awesome. The spreadsheet has been working flawlessly. I still haven't dug into it to see how it works, but it definitely works. Thanks again for making my life better!

@Ken g6 Awesomeness confirmed and official.

 

[igor_kavinski]

@Ken g6 Awesomeness confirmed and official.

I'm guessing Aries because I once saw this Aries guy solve a complex Excel problem within minutes with a concise formula. It would've taken me half a day or more and my solution would've been very inelegant compared to his. I also had a complicated C program that took days to implement. He re-implemented it within hours in Excel VBA without needing access to my code. All he needed was the sample input and output.

It saddens me that his intellect failed in binary trading. He lost everything and ended up having to try to transfer roughly $1.5 million out of our company account into some private bank. He almost succeeded but thankfully the intermediary bank got suspicious and put the amount on hold and we got it back. The guy left on a boat to Iran and has never been heard from since. We got a call from his family a year later asking about his whereabouts.

 

[nakedfrog]

I'm guessing Aries because I once saw this Aries guy solve a complex Excel problem within minutes with a concise formula. It would've taken me half a day or more and my solution would've been very inelegant compared to his. I also had a complicated C program that took days to implement. He re-implemented it within hours in Excel VBA without needing access to my code. All he needed was the sample input and output.

It saddens me that his intellect failed in binary trading. He lost everything and ended up having to try to transfer roughly $1.5 million out of our company account into some private bank. He almost succeeded but thankfully the intermediary bank got suspicious and put the amount on hold and we got it back. The guy left on a boat to Iran and has never been heard from since. We got a call from his family a year later asking about his whereabouts.

Uh, yeah, I don't think living where you do and trying embezzlement on that level is a winning move (unless, of course, you're already very wealthy).

 

[Ken g6]

Aries? No, that's not my sign, but I used to drive one long ago.

 

[igor_kavinski]

Uh, yeah, I don't think living where you do and trying embezzlement on that level is a winning move (unless, of course, you're already very wealthy).

The scariest part is that he ALMOST got the money. Or at least, whoever he conspired with. It was a private bank (under different regulations so not part of the usual banks under the purview of Central Bank) so they would've simply refused to share any details regarding the transfer, citing customer confidentiality as the excuse. There's also another scary rule here (not sure if it's the same in other jurisdictions): once you get money in your account, you can refuse to return it even if it's an erroneous transfer, unless legal action is involved. By then, the guilty party can close up shop and be out of the country. I was on vacation and that idiot was my reliever and he just plain stabbed me in the back like that. The weirdest thing is, I saw him just the day before. He invited me and another guy to Abu Dhabi Breakwater (kind of like a beach) for some friendly discussion. I forgot exactly what it was about but we were just chilling there in the evening and enjoying a cup of coffee together with a light breeze blowing. I could not have suspected in a million years that he would've done that to me.

The aftermath was seriously funny. My boss accused ME of teaching him enough to be able to do that!

I kept quiet because I know he can't be reasoned with. He normally vents like that when it's his own fault (where were the controls to prevent something like this from happening?). He was the big boss and it was his job to ensure that the relevant checks and balances were in place to prevent fraud. And I'm happy to report that there are still no controls. I have the keys to the Kingdom and can actually do what that guy did, except I have zero interest in becoming a fugitive.