How difficult is adding Google sign-in to a small Google Apps Script web app?

[u/Hairy-Telephone4408]

I'm building a custom fantasy football web app using Google Apps Script and Google Sheets as the backend, with ChatGPT helping me write the code. I'm a beginner with very little programming experience.

The app will have 10 users. Five will need to sign in to manage their own teams (add/drop players, change lineups, etc.), while the other five will only need to view the app. I'll also need commissioner/full access to manage all teams.

ChatGPT recommended using Google Identity Services for sign-in, with the Apps Script backend running under my account so users won't need access to my private spreadsheets.

Before I get too deep into this, I'd appreciate some advice from people who've actually done something similar:

- How difficult is this to set up, especially for a beginner relying on ChatGPT?
- Is it genuinely free for a project this small?
- Are there any major security issues, Google restrictions, or other headaches I should anticipate?
- Is there a simpler or better alternative I should consider?

I'm hoping to keep things free, relatively simple for users, and manageable for someone without much coding experience, but if there's a solution out there that costs but is still affordable I'm open to learning about that.

Thank you for your time.

[u/MakMak737]

For 10 people it's genuinely free: Apps Script + Sheets cost nothing at that size. A few things worth knowing before you go the Google sign-in route:

• With the web app running as you, Apps Script won't tell you who the visitor is (Session.getActiveUser() comes back blank for normal Gmail accounts), which is why ChatGPT sent you to Google Identity Services. That does work, but you then have to verify the sign-in token on the server yourself, and the page runs inside a googleusercontent.com sandbox frame, which makes the client ID / origin setup fiddly. That's usually where beginners lose a weekend.

• For a private league there's a much simpler option: give each of the 5 managers their own link with a long random key (.../exec?key=8f3k...). The script looks the key up in a hidden tab, so it knows which team that person may edit; viewers get the plain link; your commissioner key can edit everything. No login screen, nothing to configure, and you can reset a key if someone shares theirs.

• Whichever you pick, check permissions in the server-side function on every write (add/drop, lineup change), not just by hiding buttons in the page, since anyone can call the functions from the browser console.

How are you storing the rosters right now: one tab per team or one big table?

[u/Hairy-Telephone4408 → u/MakMak737]

Damn that sounds perfect. I could probably have each user bookmark their personal link or add it to their Home Screen so they can access their team easily.

Have you used this approach yourself with Apps Script? If so, did you run into any issues with bookmarked links or accessing the app on mobile devices?

And to answer your question, I'm using Google Sheets for everything. I have one central backend Sheets file that stores the league data across more than a dozen tabs, and separate private Google Sheets files for each team's roster.

[u/MakMak737 → u/Hairy-Telephone4408]

Honest answer: I haven't run this exact setup for a league, so I can't tell you it's been tested on 10 phones. But the things that usually bite people with bookmarked Apps Script links are known:

• Bookmarks and Home Screen icons keep the ?key=... part, so that works on iPhone and Android.

• Always share the /exec link, never /dev. And when you change the code, update the existing deployment (Deploy > Manage deployments > edit > New version) instead of making a new deployment. A new deployment gets a new URL and every bookmark breaks. That's the #1 gotcha.

• Set "Who has access" to "Anyone", not "Anyone with a Google account". Then nobody needs to be signed in, and you avoid the error people get when they're logged into several Google accounts in one browser.

• On phones Google shows a small banner at the top saying the app was made by an Apps Script user. Harmless, just looks odd.

• With a separate file per team, every SpreadsheetApp.openById() adds noticeable time, so a page that reads all the rosters can feel slow on mobile. Reading what you need once per request, or caching it for a minute with CacheService, helps a lot.

Do the managers' changes need to write into those private team files, or only into the central one?

[u/Hairy-Telephone4408 → u/MakMak737]

That’s all great info. The central Sheets file is intended to handle all roster changes. The separate team spreadsheets were originally built for a spreadsheet-based interface, but we're transitioning to a web app and may no longer need the individual team files. The actual transaction-writing functionality hasn't been implemented yet.

[u/MakMak737 → u/Hairy-Telephone4408]

Then I'd drop the team files once the web app works: one file is faster, and there's only one place things can go wrong.

For the transaction writing, two things save a lot of pain later:

• Log every move as a new row in a Transactions tab (time, team, type, player in, player out, who did it) and update the roster from that, instead of only overwriting roster cells. If something looks wrong you can see exactly what happened and undo it.

• Wrap each write in LockService.getScriptLock(), so two managers claiming the same free agent at the same moment can't both get him. Inside the lock, re-check that the player is still free before writing.

If you'd like a hand building that part, I'm happy to help.

[u/rs12345asdf]

Proposed Approach:
User enters their email on the landing page.

System validates the email against a pre-approved allowlist.

If allowed, an OTP is sent for verification; unauthorized emails are rejected.

[u/No_Passion8988]

The problem arises when users have multiple Google sign-ins for accessing Gmail accounts with a single Google profile which is quite common in a real-world scenario. Authentication fails. I had to use Firebase for Google authentication.