Skip to content

SETUP.md

Latest commit

 

History

History
232 lines (161 loc) · 5.8 KB

SETUP.md

File metadata and controls

232 lines (161 loc) · 5.8 KB

Voter Survey Workflow - Setup Guide (Google Sheets)

Prerequisites

  • Node.js (v14 or higher)
  • Google Cloud Platform account
  • Twilio account
  • Typeform account

Step 1: Install Dependencies

npm install dotenv papaparse googleapis twilio express nanoid libphonenumber-js

Step 2: Set Up Google Sheets API

Create a Google Cloud Project

  1. Go to Google Cloud Console
  2. Create a new project (or select existing)
  3. Enable the Google Sheets API:
    • Navigate to "APIs & Services" > "Library"
    • Search for "Google Sheets API"
    • Click "Enable"

Create a Service Account

  1. Navigate to "APIs & Services" > "Credentials"
  2. Click "Create Credentials" > "Service Account"
  3. Fill in the details:
    • Name: voter-survey-workflow
    • Description: Service account for voter survey automation
  4. Click "Create and Continue"
  5. Grant role: "Editor" (or create custom role with Sheets access)
  6. Click "Done"

Generate 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. Choose JSON format
  5. Download the file and save it as credentials.json in your project directory

⚠️ Important: Keep this file secure! Add it to .gitignore

Create Your Spreadsheet

Option 1: Create manually

  1. Go to Google Sheets
  2. Create a new spreadsheet
  3. Name it "Voter Survey Database" (or your preferred name)
  4. Copy the Spreadsheet ID from the URL: 
    https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit

Option 2: Create via test script

node test-workflow.js create-spreadsheet

Share Spreadsheet with Service Account

  1. Open your spreadsheet
  2. Click "Share" button
  3. Add the service account email (found in credentials.json under client_email)
  4. Grant "Editor" permissions
  5. Uncheck "Notify people"
  6. Click "Share"

Step 3: Set Up Twilio

  1. Sign up at Twilio
  2. Get a phone number with SMS capabilities
  3. Find your credentials in the Twilio Console:
    • Account SID
    • Auth Token
    • Phone Number (format: +1XXXXXXXXXX)

Step 4: Configure Environment Variables

Create a .env file in your project root:

# Google Sheets Configuration
GOOGLE_CREDENTIALS_PATH=./credentials.json
GOOGLE_SPREADSHEET_ID=your_spreadsheet_id_here
GOOGLE_SHEET_NAME=Voter Survey

# Twilio Configuration
TWILIO_ACCOUNT_SID=your_account_sid
TWILIO_AUTH_TOKEN=your_auth_token
TWILIO_FROM_NUMBER=+1234567890

# Survey Configuration
SURVEY_BASE_URL=https://yourform.typeform.com/to/form_id
UTM_CAMPAIGN=jerseycity_survey_oct2025

# Webhook Configuration (optional)
PORT=3000

Step 5: Test Your Setup

Run the test suite:

# Test all connections
node test-workflow.js all

# Test individual components
node test-workflow.js config         # Validate configuration
node test-workflow.js sheets         # Test Google Sheets connection
node test-workflow.js twilio         # Test Twilio connection

Step 6: Generate Sample Data (Optional)

node test-workflow.js generate-csv

This creates sample-voters.csv with test data.

Step 7: Run Your First Import

# Import without sending SMS
node voter-survey-workflow.js import sample-voters.csv

# Import and send SMS
node voter-survey-workflow.js import sample-voters.csv --send-sms

Step 8: Set Up Typeform Webhook

  1. In Typeform, go to your form settings
  2. Navigate to "Connect" > "Webhooks"
  3. Add webhook URL: https://your-domain.com/webhook/typeform
  4. Make sure to include utm_id as a hidden field in your form

Testing Webhooks Locally

Use ngrok to expose your local server:

# Start the webhook server
node voter-survey-workflow.js webhook

# In another terminal, start ngrok
ngrok http 3000

# Use the ngrok URL in Typeform webhook settings
# Example: https://abc123.ngrok.io/webhook/typeform

Security Best Practices

  1. Never commit credentials:

    echo "credentials.json" >> .gitignore
echo ".env" >> .gitignore

  2. Use environment-specific credentials:

    • Development: Use test Twilio numbers
    • Production: Use production credentials

  3. Limit service account permissions:

    • Only grant access to specific spreadsheets
    • Use custom IAM roles if possible

  4. Rotate credentials regularly:

    • Generate new service account keys periodically
    • Update Twilio auth tokens if compromised

Troubleshooting

Google Sheets API Errors

Error: "The caller does not have permission"

  • Verify service account email is shared with the spreadsheet
  • Check that the Google Sheets API is enabled
  • Ensure service account has proper IAM roles

Error: "Unable to parse range"

  • Check that GOOGLE_SHEET_NAME matches the sheet tab name
  • Sheet names are case-sensitive

Twilio Errors

Error: "Unable to create record: The 'From' number is not a valid phone number"

  • Ensure phone number is in E.164 format (+1XXXXXXXXXX)
  • Verify the number is SMS-enabled in Twilio console

Error: "Authentication Error"

  • Double-check Account SID and Auth Token
  • Ensure no extra spaces in .env file

CSV Import Issues

"No valid records to process"

  • Check that CSV has required columns: FirstName, Voter File VANID, Home Phone or LandlinePhone
  • Verify phone numbers are in valid formats
  • Review cleaned data logs for skipped records

Next Steps

  • Set up automated imports with cron jobs
  • Configure production webhook endpoints
  • Set up monitoring and logging
  • Create backup procedures for your spreadsheet

Support

For issues with: