Skip to content

SETUP.md

Latest commit

 

History

History
272 lines (186 loc) · 14.6 KB

SETUP.md

File metadata and controls

272 lines (186 loc) · 14.6 KB

Setup Guide

One-time setup to stand up the site from scratch. Reproducible in ~60 minutes. Once done, see MAINTENANCE.md for day-to-day operation.

  1. Prerequisites
  2. Google Sheet (the CMS)
  3. Google Form (public submissions)
  4. MapTiler (geocoding)
  5. Cloudflare Pages (hosting)
  6. Apps Script (automation)

1. Prerequisites

You'll need free accounts on:

Service Used for Free tier ceiling
Google Sheet (CMS), Form (submissions), Apps Script (automation) Generous — nothing to worry about at this scale
MapTiler Cloud Geocoding only 100k geocodes/month
Cloudflare Hosting (Pages) Unlimited bandwidth, 500 builds/month
GitHub Source control + Pages connector Free for private repos

Local dev needs:

  • Node.js 20+
  • A clone of this repo
  • A copy of .env.example saved as .env and filled in (you do this as you work through this guide)

2. Google Sheet (the CMS)

The sheet is the only place the admin ever touches.

2.1 Create the sheet

  1. Create a new Google Sheet at sheets.new, titled Social Impact Businesses.

  2. Rename the first tab to Locations (right-click the tab at the bottom → Rename). This name is load-bearing:

    • The build reads the sheet via ?sheet=Locations in the CSV export URL. Gviz silently falls back to the first tab if the name doesn't match, so the build might "work" on the wrong data without complaining — you'll notice only when rows don't appear.
    • The Apps Script's SHEET_NAME constant matches exactly Locations. If your tab is Sheet1, the Apps Script will log a loud warning in its Executions log but won't update the sheet or trigger deploys.
    • If you need a different tab name, update three places in lockstep: the tab itself, SHEET_NAME in scripts/apps-script.gs, and SHEET_TAB in your .env / Cloudflare env vars.

  3. Add these headers in row 1, in any order you like (code reads by name):

    Name | Slug | Address | City | State | Lat | Lng | Geocoded Address |
Website | Mission | Populations Served | Hiring Model | Photo URL |
Type | Featured | Status | Notes | Last Modified

  4. Freeze row 1 (View → Freeze → 1 row) and color it gray so it's visually obvious these are code-referenced.

  5. Paste this into the sheet description (⋮ menu on the tab → "Description"):

    ⚠️ The site reads this sheet by column header name. You can reorder columns freely but renaming a header will break the next build (site stays up on the previous build until fixed). Same goes for renaming the Locations or Types tabs.

2.2 Create the Types tab

The Type column on the Locations tab references values defined in a second tab called Types. This keeps the type catalog (labels, colors, icons) admin-editable without a code change.

  1. At the bottom of the sheet, click + to add a second tab. Rename it to Types (exact spelling).

  2. Add these headers in row 1 (order doesn't matter, code reads by name):

    key | label | color | icon

  3. Freeze row 1 and color it gray, same as the Locations tab.

  4. Seed the catalog with the four default rows below. The icon value must be a slug that exists in src/lib/location-type-icons.ts (currently: coffee, restaurant, shop, bag, bakery, heart, book, leaf, sparkle, hands). The build fails loudly on an unknown slug:

    key label color icon
    coffee-shop Coffee shop #5d4037 coffee
    cafe Cafe #c97551 restaurant
    bakery Bakery #8d6e63 bakery
    other Other #4a7c59 heart
    • key must be kebab-case (lowercase letters, digits, hyphens). It's what the Locations tab's Type column stores.
    • label is what users see in the filter picker and pill — plain title-case text.
    • color must be a 6-digit hex (#RRGGBB). This colors the pin on the map and the dot on the filter pill.
    • icon is a bare slug — no .svg extension, no path.

2.3 Data validation

Select whole columns on the Locations tab and add validation rules (Data → Data validation):

  • Slug (col B): formula — set cell B2 to =ARRAYFORMULA(IF(A2:A="", "", LOWER(REGEXREPLACE(TRIM(REGEXREPLACE(A2:A, "[^A-Za-z0-9]+", " ")), " ", "-")))) so slugs auto-derive from Name.
  • Type (col N): Dropdown with Criteria: Dropdown (from a range), range = Types!A2:A. Set "Reject input" on invalid. This keeps the Type column in sync with whatever you have on the Types tab — add a row there, and it shows up in the dropdown here.
  • Populations Served (col K): Dropdown, Allow multiple selections checked, values like Recovery, Reentry, Disability employment, Youth, Veterans (your list).
  • Hiring Model (col L): Dropdown, values like Direct hire, Training program, Apprenticeship.
  • Featured (col O): Checkbox.
  • Status (col P): Dropdown with Pending, Approved, Hidden. Set "Reject input".

2.4 Access control

File → Share → Anyone with the link → Viewer.

Reasoning: the Cloudflare build reads the sheet via its public CSV export. The URL acts as a weak secret — don't commit it to a public place. Personal data (submitter emails) is not in the sheet (it stays inside Google Forms' own response view), so the worst-case disclosure is your Notes column. Don't write secrets in Notes.

2.5 Copy the sheet ID

The sheet ID is the long string between /d/ and /edit in the URL. Paste it into .env:

SHEET_ID=14vfJwho2stohQTcERMvdCdNzran5P60rv8WdMIfy3sE

3. Google Form (public submissions)

  1. In the sheet, Tools → Create a new form. Google auto-links the form to the sheet — submissions append as new rows.

  2. Form title: Suggest a location. Add these questions, mapping 1:1 to sheet columns:

    • Name (short answer, required)
    • Address, City, State (short answer each)
    • Website (short answer, optional)
    • Mission (paragraph, required)
    • Populations Served (checkboxes — values match your sheet multi-select)
    • Hiring Model (dropdown — values match your sheet dropdown)
    • Photo URL (short answer, optional — paste a Google Drive or hosted image URL)
    • Type (multiple choice — values must match the key column of your Types tab; e.g. coffee-shop, cafe, bakery, other). Google Forms can't data-validate against a sheet range, so keep this list synced manually: whenever you add a row to the Types tab, also add the option here. If the admin wants looser validation, a short-answer field also works — the admin re-maps it to a valid key when approving.
    • Are you suggesting an edit to a location already on the map? (short answer, optional — paste URL or location name if yes)

  3. Do not include a Status question — the form leaves Status blank, and the admin sets it to Pending/Approved manually (or you can have Apps Script default it to Pending on form submit).

  4. Form → Responses → ⚙ → Collect email addresses — keeps submitter emails inside the form's response view only (never lands in the sheet).

  5. Form → Send → Embed HTML → copy the <iframe src="…"> URL and paste into .env:

    PUBLIC_FORM_URL=https://docs.google.com/forms/d/e/.../viewform?embedded=true

4. MapTiler (geocoding)

Used only for geocoding (the Apps Script calls it). Map tiles come free from OpenFreeMap — no key needed.

  1. Sign up at https://www.maptiler.com/cloud/
  2. Dashboard → Account → API Keys → use the default or create one named changeplease.
  3. Leave Allowed Origins blank. The Apps Script calls this from Google's servers, where there's no Origin header. Origin restriction would block it.
  4. Copy the key — you'll paste it into Apps Script in section 6.

(The browser-facing PUBLIC_MAPTILER_API_KEY is reserved but currently unused since tiles are free from OpenFreeMap. Leave it blank or set it later if you swap tile providers.)

5. Cloudflare Pages (hosting)

5.1 Connect the repo (one-time, in the dashboard)

This step must be done in the UI because the API can't install the Cloudflare GitHub App on your behalf.

  1. Sign up at https://dash.cloudflare.com/sign-up if you haven't.
  2. Cloudflare dashboard → Workers & Pages → Create application → Pages tab (NOT the default Workers tab). If you only see a "Deploy command" field, you're in the Workers flow — cancel and find the Pages tab, or go directly to https://dash.cloudflare.com/?to=/:account/pages/new.
  3. Connect to Git → select alwaysmap/changeplease.
  4. Build settings can be left as defaults — the script in 5.2 sets them.
  5. Skip the env-vars dialog — the script handles them.
  6. Save and Deploy. First build will FAIL (no env vars yet); that's expected, we fix it now.

5.2 Push env vars + deploy hook via script

Add three Cloudflare values to .env:

CLOUDFLARE_API_TOKEN=…    # dash.cloudflare.com/profile/api-tokens
                           # → Create Token → Custom → Permissions:
                           # Account → Cloudflare Pages: Edit
CLOUDFLARE_ACCOUNT_ID=…   # Right sidebar of any Cloudflare dashboard page
CLOUDFLARE_PAGES_PROJECT=changeplease   # Whatever you named the project

Run:

set -a; source .env; set +a
npm run setup:cloudflare

This:

  • Pushes SHEET_ID, PUBLIC_FORM_URL, PUBLIC_SITE_URL, NODE_ENV=production to Production + Preview scopes
  • Sets npm run build / dist as the build config
  • Creates the sheet-on-approve deploy hook (idempotent; won't duplicate)
  • Prints the deploy hook URL — you'll paste it into Apps Script in section 6

Re-run the script any time .env values change. Full sync, idempotent.

⚠️ The deploy hook URL is not synced automatically to Apps Script. If the hook is ever deleted and recreated (e.g. cleanup, or a different automation) the URL changes. Apps Script stores its copy in a Script Property — and will silently POST to the old, now-404'd URL until you update it. Whenever setup:cloudflare prints a hook URL that's different from what's in Apps Script, update the Script Property (section 6.2). This is the single most common "edits stopped deploying" trap.

5.3 (Optional) Custom domain

Cloudflare Pages project → Settings → Custom domains → Add.

6. Apps Script (automation)

This is what automates the whole thing: geocoding on approval, deploy-trigger on change, Last-Modified timestamps.

6.1 Open the Apps Script editor

In your sheet: Extensions → Apps Script. Delete the default Code.gs contents. Paste the full contents of scripts/apps-script.gs from this repo.

6.2 Set script properties

In the Apps Script editor sidebar: Project Settings (gear icon) → Script Properties → Add script property, twice:

Property Value
MAPTILER_API_KEY Your MapTiler key from section 4
CLOUDFLARE_DEPLOY_HOOK_URL The URL printed by npm run setup:cloudflare in section 5.2

6.3 Install the triggers

In the Apps Script editor sidebar: Triggers (clock icon) → Add Trigger. Create two:

Trigger 1 — on edit:

  • Function: handleEdit
  • Event source: From spreadsheet
  • Event type: On edit

Trigger 2 — on form submit:

  • Function: handleFormSubmit
  • Event source: From spreadsheet
  • Event type: On form submit

On each trigger you create, set "Failure notification settings" to "Notify me immediately". This makes Google email the script owner (you) whenever a trigger throws. Free early-warning system — no polling, no code, just a checkbox.

Google will ask you to authorize the script's permissions on first save. It needs: read/write the sheet, call external URLs (MapTiler + Cloudflare), read script properties. Authorize.

6.3b (Optional) Set up a daily health check

The script includes a dailyHealthCheck function that verifies end-to-end wiring (sheet accessible, all required headers present, MapTiler key working, deploy hook URL well-formed) and emails you if anything's off. Good insurance — even if you stop looking at the site for a while, you'll know when it breaks.

To enable:

  1. Apps Script → ⏱ Triggers → + Add Trigger
  2. Function: dailyHealthCheck
  3. Event source: Time-driven
  4. Type: Day timer, pick a time (e.g. 9am-10am)
  5. Failure notifications: Notify me immediately
  6. Save

On first run, Google prompts for additional Gmail-send permission (needed for the failure alert). Authorize.

6.3c Verify the deploy hook after any rotation

Whenever you re-run npm run setup:cloudflare and get a new deploy hook URL, paste it into the Script Property CLOUDFLARE_DEPLOY_HOOK_URL. Then verify it works without waiting for a real sheet edit:

  1. Apps Script editor → open the function dropdown at the top (next to Run / Debug)
  2. Select verifyDeployHook
  3. Click Run
  4. Executions log shows either PASS: deploy hook responded 200. URL is valid. or a specific FAIL: message with the problem

This catches the "I pasted the wrong URL" bug the instant it happens, not three days later when an admin edit silently fails to deploy.

6.4 Test end-to-end

  1. In the sheet, add a test row on the Locations tab: fill Name, Address, City, State, Mission, Type (pick one from the dropdown — e.g. coffee-shop). Leave Lat/Lng blank.
  2. Change Status to Approved.
  3. Watch the row: within a few seconds, Lat/Lng and Geocoded Address should populate automatically. Last Modified should update.
  4. Watch Cloudflare Pages → Deployments: a build should kick off within a few seconds.
  5. ~30 seconds later, the new location is live at your site URL under /location/<slug>, and its pin shows the color/icon from the corresponding row on the Types tab.

If nothing happens, check the Apps Script editor → Executions tab for logs from the trigger runs. Most common issue: script properties not set, or origin restriction still active on the MapTiler key.

6.5 Verify the geocode landed on the right spot

Compare the Address column with the Geocoded Address column. If MapTiler resolved to something surprising (e.g., just a city name because the street was ambiguous), paste correct coordinates from a Google Maps right-click directly into Lat/Lng. The Apps Script doesn't overwrite non-blank lat/lng.

Done

From here on: admin edits the sheet, the site updates itself. See MAINTENANCE.md for the day-to-day operator guide.