How to Create a Login & Register System Using Google Apps Script and Google Sheets
Want to build a simple login and registration system without a backend server? You can easily do it using Google Apps Script and Google Sheets. In this tutorial, I’ll guide you through every step to build this system and integrate it with your website or web app.
🧰 What You’ll Need
- A Google Sheet (to store user data)
- Google Apps Script
- A basic HTML form (for login and register)
- Basic knowledge of HTML and JavaScript
📘 Step 1: Create a Google Sheet
- Go to Google Sheets
- Create a new sheet and name it
UserData. - In Row 1, add the following headers:
pgsqlCopyEditName | Email | Password
👉 Tip: You can add more fields like phone number, role, etc.
💻 Step 2: Open Google Apps Script
- Click on
Extensions>Apps Script - Delete any default code
- Rename the project to something like
LoginRegisterScript
🔧 Step 3: Add Apps Script Code
Paste the following code into your Code.gs file:
javascriptCopyEditfunction doGet(e) {
return HtmlService.createHtmlOutputFromFile('Page');
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
function registerUser(name, email, password) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('UserData');
const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
if (data[i][1] === email) {
return "Email already exists.";
}
}
sheet.appendRow([name, email, password]);
return "Registration successful!";
}
function loginUser(email, password) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('UserData');
const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
if (data[i][1] === email && data[i][2] === password) {
return "Login successful!";
}
}
return "Invalid credentials.";
}
📝 Step 4: Add the HTML Page
Create a new file: File > New > HTML and name it Page.html. Then paste the following code:
htmlCopyEdit<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
body { font-family: Arial; margin: 40px; background-color: #f2f2f2; }
.box { background: white; padding: 20px; border-radius: 10px; width: 300px; margin: auto; }
input, button { width: 100%; padding: 10px; margin: 5px 0; }
h2 { text-align: center; }
</style>
</head>
<body>
<div class="box">
<h2>Register</h2>
<input type="text" id="regName" placeholder="Name">
<input type="email" id="regEmail" placeholder="Email">
<input type="password" id="regPassword" placeholder="Password">
<button onclick="register()">Register</button>
<h2>Login</h2>
<input type="email" id="loginEmail" placeholder="Email">
<input type="password" id="loginPassword" placeholder="Password">
<button onclick="login()">Login</button>
<p id="response" style="text-align:center; color: green;"></p>
</div>
<script>
function register() {
const name = document.getElementById('regName').value;
const email = document.getElementById('regEmail').value;
const password = document.getElementById('regPassword').value;
google.script.run.withSuccessHandler(function(response) {
document.getElementById('response').textContent = response;
}).registerUser(name, email, password);
}
function login() {
const email = document.getElementById('loginEmail').value;
const password = document.getElementById('loginPassword').value;
google.script.run.withSuccessHandler(function(response) {
document.getElementById('response').textContent = response;
}).loginUser(email, password);
}
</script>
</body>
</html>
🚀 Step 5: Deploy as Web App
- Click
Deploy>Test deployments>Select type: Web app - Set:
- Execute as: Me
- Who has access: Anyone
- Click
Deploy - Authorize the script
- Copy the web app URL
You can now share this URL or embed it into your website with an <iframe> tag.
💡 Bonus: Embed on Your Website
You can embed this login/register system in your HTML website like this:
htmlCopyEdit<iframe src="YOUR_SCRIPT_URL_HERE" width="100%" height="600" style="border:none;"></iframe>
🔐 Security Notes
- This example stores passwords in plain text (not recommended for production).
- For better security:
- Hash passwords with
Utilities.computeDigest()(Apps Script has limited options). - Limit access by role or add email verification using Google Forms.
- Hash passwords with
✅ Final Words
This is a great lightweight login and registration system if you’re building tools with Google Sheets + Apps Script. It’s perfect for small internal tools, portals, or side projects.
Want to add OTP, authentication by Google account, or dashboards after login? Stay tuned for advanced tutorials!
Frequently Asked Questions (FAQs)
1. Is this login/register system secure?
Not entirely. This setup stores passwords as plain text in Google Sheets, which is not secure for sensitive or large-scale systems. For better security:
- Use hashing for passwords (e.g.,
Utilities.computeDigest()in Apps Script). - Limit sharing of the Google Sheet.
- Consider Firebase Authentication or OAuth for production-level apps.
2. Can I allow users to log in using their Google Account?
Yes! You can use Google Account Authentication through Google Apps Script + Google Sign-In. This requires additional coding and OAuth permissions, but it’s a more secure and user-friendly method.
3. Can I reset or change passwords?
Not in this basic version. You can:
- Add a “Reset Password” field and manually verify user identity before changing it.
- Create a password reset system with email OTP (requires integration with Gmail App Script and a token system).
4. Can I add user roles like Admin and User?
Absolutely. Just add a new column in the sheet called Role and assign values like Admin, User, etc. Then, use conditional logic in the Apps Script to redirect or restrict access based on the role.
5. Can I use this with my own website domain?
Yes! You can:
- Embed the web app using an
<iframe>as shown above. - Or use a redirect button on your site to open the Apps Script web app.
6. How many users can this system handle?
This system is best for small projects or internal tools (under 1000 users). Google Sheets has limits (like 10 million cells per sheet), and Apps Script has quotas. For large-scale apps, consider Firebase or Supabase.
7. Can I track login history or timestamps?
Yes, just add a new column in the sheet called Last Login and update it in the loginUser() function using sheet.getRange(row, column).setValue(new Date()).
8. Can I block users after multiple failed attempts?
Not in the default code, but you can:
- Add a
Failed Attemptscolumn - Track login failures
- Temporarily disable login after 3+ failed attempts
9. Can I connect this system with other sheets or dashboards?
Yes! Once a user logs in, you can redirect them to different pages (HTML files) or load data from other sheets based on their role or email.
doii Qboso drrxL