Unable to load spreadsheet due to Google Drive file sharing restrictions

[liambeveridge]

Hello,

We are running into a new issue regarding school cybersecurity restrictions and Pyret. Our school does not allow students to share docs with "anyone with the link." Instead, we allow students to share only within the institution, or with allowlisted domains. This is causing a problem where students are unable to load Google Sheets in Pyret because they can't share their sheets with "anyone with the link." Is there a specific account that Pyret uses to load sheets? That way, we could allowlist the account, and have students share sheets that need to be loaded directly with that account so that they can load their sheets into Pyret.

Thank you in advance for your assistance, and please let me know if I can clarify anything regarding this issue!

[jpolitz]

Thanks for reaching out and for the clear report.

There are a few ideas I have here (the one you suggested about sharing with a designated account is good, but unfortunately not how Pyret works, so it won't help as described).

First, clarification:

• To be clear, you have students who are using their own sheets and importing them into their code?
• Do the students only do this for sheets they own, or do you want them to see other students sheets (after sharing) as well?
• Do you have issues using already-made-and-public sheets, like the ones in the starter files for Bootstrap Data Science?

Then, what you can do right now (which you may have thought of already):

An (annoying, unscalable) workaround is to share with a teacher, have them make a copy and make it public, and use that as a link. May or may not work depending on how many students/sheets, and how dynamic the sheets are (e.g. if they are the results of Google Forms and change daily). If you just have a handful, maybe that would work. Could also have the teacher make them and share them up front and then have students edit them if that's possible.

Then, what we can do:

I think we can make a version of load-spreadsheet that works with the ID of any spreadsheet the account has permission to. I'll know if it's easy in a few days; that would be the best thing to do. There is a my-spreadsheet function, but it doesn't quite do the right thing for your use case.

I'll keep thinking if there's something more clever and immediate we can do, but unfortunately I think we need to change something to support this (which I'm willing and happy to do, it'll just take a bit of work).

[liambeveridge]

Hello,

Thank you for your help with this! I appreciate your detailed response.

Currently students are making their own copies of teacher-created sheets and editing them (so essentially making their own sheets). Those are the only sheets that we are having trouble with. Your workaround is helpful though. We suggested to teacher that importing sheets is possible if they open up the sharing settings for students, or if they have students use teacher-created sheets.

We will make due with working around this for the time being, and look forward to that change if you have the opportunity to implement something. Thanks again!

[jpolitz]

@liambeveridge Just a heads up that I looked into this.

A few years back we restricted Google Drive permissions to “View and manage Drive files and folders that you open or create with an app.”

This is an improvement for security over “View and manage all of your Drive files.”

However, this means that on a normal login there's not a great way to bless the spreadsheets as being “opened or created by Pyret”. This means it's sort of public or just not doable.

We need a user interface like the picker for images ("Insert" in the top menu bar) to allow picking spreadsheets to set that permission correctly. We've had something like this in the past, it might be doable to bring it back.

So I know what to do, we just need to find time to do it.

[liambeveridge]

Hello,

Thanks so much, I appreciate you taking the time to investigate this!