Google Sheets Setup Guide

This guide will help you set up Google Sheets as a database for your application.

Step 1: Create a Google Cloud Project and Enable API

1. Go to the Google Cloud Console
2. Create a new project or select an existing one
3. Navigate to "APIs & Services" > "Library"
4. Search for "Google Sheets API" and enable it

Step 2: Create a Service Account

1. In your Google Cloud project, go to "APIs & Services" > "Credentials"
2. Click "Create Credentials" and select "Service Account"
3. Fill in the service account details and click "Create"
4. No need to grant the service account any roles (just click "Continue" and "Done")

Step 3: Create a Service Account Key

1. Click on the service account you just created
2. Go to the "Keys" tab
3. Click "Add Key" > "Create new key"
4. Select "JSON" as the key type and click "Create"
5. The JSON key file will be downloaded to your computer

Step 4: Set Up Your Google Spreadsheet

1. Go to Google Sheets and create a new spreadsheet
2. Create two sheets:
   • Rename "Sheet1" to "users" (for authentication data)
   • Create a new sheet named "user_data" (for user-specific data)
3. Import the CSV templates:
   • For the "users" sheet: Download and import sample_users.csv
   • For the "user_data" sheet: Download and import sample_user_data.csv

4. Share your spreadsheet with the service account:
   • Click the "Share" button in the top-right corner
   • Add the service account email (found in the client_email field of your JSON)
   • Set permission to "Editor" to allow both reading and writing to the sheet
     (This is important for future functionality like updating last login times or saving user data)
   • Uncheck "Notify people" and click "Share"
5. Set appropriate permissions in Google Cloud Console:
   • Go back to your Google Cloud Console
   • Navigate to "IAM & Admin" > "Service Accounts"
   • Find your service account and click on it
   • Go to the "Permissions" tab
   • Make sure it has at least the "Editor" role for the Google Sheets API
   • This ensures your service account can both read and write to your Google Sheets

Step 5: Get Your Spreadsheet ID

1. Look at the URL of your Google Sheet
2. The Spreadsheet ID is the long string between /d/ and /edit in the URL
3. Example: https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit

Step 6: Complete the Setup in the Application

1. Run the application and you'll be directed to the setup page
2. Paste the entire contents of your JSON key file
3. Enter your Spreadsheet ID
4. Click "Complete Setup"

What's Next?

After setup is complete:

1. Use the main page to view your spreadsheet data
2. Test the user login functionality
3. Explore user-specific data filtering

Sheet Structure

Users Sheet

The "users" sheet should have these columns:

Column Name	Description
id	Numeric identifier (1, 2, 3, etc.)
email	User's email address
password	User's password (in a real app, this would be hashed)
name	User's full name
role	User's role (e.g., user, admin, editor)
created_at	Date when the user was created
last_login	Date of the user's last login
status	User's status (e.g., active, inactive)

User Data Sheet

The "user_data" sheet should have these columns:

Column Name	Description
user_id	Numeric identifier matching the id in the users sheet
data_field_1	First data field (customize as needed)
data_field_2	Second data field (customize as needed)
data_field_3	Third data field (customize as needed)
date_created	Date when the data was created

Testing the Setup

1. Open the index.php file in your browser
2. You should be able to see and interact with your Google Sheets data
3. Try the authentication example (sheets_auth.php) to test user login
4. Try the user-specific data example (user_example.php) to see filtered data

---

Back to Main Application