search

Longhorn-Riichi / UvUManager

Discord bot for Longhorn Riichi's recurring special tournament: UTA vs UTD.
https://discord.gg/gSmwTZcChz
MIT License
2 stars 0 forks source link

Google Sheets Formula Protection #10

Open peter1357908 opened 1 year ago

peter1357908 commented 1 year ago

I noticed that editing parts of the sheet can unintentionally affect formulas elsewhere (this likely caused the formula deletion during our testing yesterday). Is there a way to ensure that this doesn't happen?

Relevant StackExchange here.

JellyfishSpot commented 1 year ago

Could you describe the issue a bit more? Overwritting any data in the "Calc" sheet or "Leaderboard" sheet will result in reference errors as array formula can't replace any existing values. Was this caused by an invalid input in "Registry" or "Score Dump" or from shifting the rows and columns around?

peter1357908 commented 1 year ago

One scenario that I remember is: I was testing the Google Sheets module by shifting the rows in the Registry to create empty rows in between filled rows, and suddenly the formula in both calc and leaderboard changed (and broke the sheet).

You can check out the broken sheet before I restored it in the version history (the broken version is on July 17, 3:39 AM).

Did you check out the StackExchange link? I think it proposed a potential fix by wrapping some formula with INDIRECT.

peter1357908 commented 1 year ago

By the way in a later meeting, let's go over things I updated/fixed in the "massive update" #11, like how I accounted for empty rows in the Registry sheet.

JellyfishSpot commented 1 year ago

Sounds good. I'll also look to see if using INDIRECT fixes these issues