Google Cloud Setup — Automate Workshop Reflection Forms

Purpose: Create all MHP DE Workshop reflection Google Forms from

google-forms-manifest.yaml using the official

Google Forms API.

Related:

• google-forms-reflection-design.md — question design
• google-forms-reflection-design.html — design (HTML)
• Script: create-forms.mjs

You still link each form to Google Sheets manually (one click per form in the Form UI) unless you add a follow-up Drive API script.

> Cost: Creating a GCP project, enabling the Forms API, and running this script for a one-time workshop rollout is free under standard Forms API quotas. You do not need a billing account for typical use. Google may require billing only if you exceed elevated quotas later in 2026.

1. Open Google Cloud Console.
2. Click the project picker → New Project.
3. Name: mhp-de-workshop-forms (any name is fine).
4. Click Create and select the new project.

No billing account is required for this workflow.

Enable both in the same GCP project as your OAuth client (credentials.json).

Google Forms API (required)

1. APIs & Services → Library.
2. Search Google Forms API → Enable.

Google Drive API (required for --replace delete + banner)

1. APIs & Services → Library.
2. Search Google Drive API → Enable.

Direct link (replace project ID if needed):

https://console.cloud.google.com/apis/library/drive.googleapis.com

Or use the link from the error message, e.g.

https://console.developers.google.com/apis/api/drive.googleapis.com/overview?project=YOUR_PROJECT_ID

After enabling, wait 2–5 minutes, then re-run the script. No new credentials.json needed.

1. Go to APIs & Services → OAuth consent screen.
2. User type:

• Internal — if you use Google Workspace and only colleagues will run the script.
• External — if you use a personal Gmail account (add yourself as a test user).

1. Fill required fields:

• App name: MHP DE Workshop Forms
• User support email: your email
• Developer contact: your email

1. Scopes → Add or remove scopes → add:

• https://www.googleapis.com/auth/forms.body
• https://www.googleapis.com/auth/drive *(banner upload + --replace delete; drive.file alone often returns Insufficient Permission)*

1. Test users (External only) — required for personal Gmail:

• Click + ADD USERS
• Add every Google account that will run create-forms.mjs or open the OAuth link
• Example: juewei.jin@gmail.com must be listed if you sign in with that address
• Click Save

1. On the consent screen summary, confirm Publishing status shows Testing (that is OK).
2. Save through the summary screen.

> Do not click Publish app unless you are ready for Google’s full app verification (weeks; not needed for this workshop script).

Where to find it in Google Cloud Console (2025+ UI)

Google moved OAuth settings. Use one of these paths:

If you do not see Google Auth platform, use path C or the direct link in A.

Create the client

1. On the Clients page, click + Create client (or Create Credentials → OAuth client ID on the legacy Credentials page).
2. Application type: Desktop app (not “Web application”).
3. Name: MHP Forms CLI.
4. Click Create → Download JSON (download immediately—secrets may not be shown again).

Redirect URIs — Desktop vs Web (read this)

If you cannot find Authorized redirect URIs, your client is probably Desktop—that is normal. Skip redirect URI setup and go to Step 6.

If the client type column says Web application, either add the redirect URI on that client’s edit page or create a new Desktop app client and download a new credentials.json.

1. Save the JSON file as:

textworkshop-2026-v2/trainer/google-forms/credentials.json

From the repository root:

powershellcd /workshop-2026-v2/trainer/google-forms
npm install

This installs googleapis and js-yaml.

powershellnode create-forms.mjs

First run only:

1. The script prints a URL and tries to open your browser (keep the terminal running).
2. Sign in with the same Gmail address you added under Test users (e.g. juewei.jin@gmail.com).
3. Click Allow (scope: edit Forms). If you see Access blocked / Error 403: access_denied, see Fix: Access blocked (403) below.
4. After Allow, Google redirects to 127.0.0.1:53187 — you should see Authorization successful in the browser (not *connection refused*).
5. Return to the terminal; the script saves token.json and creates all forms.

If the browser shows This site can’t be reached / localhost refused to connect, see Fix: localhost refused to connect below.

Later runs reuse token.json — no browser step unless the token expired.

Dry run (no API calls)

powershellnode create-forms.mjs --dry-run

Prints how many questions would be created per form.

Single module only

powershellnode create-forms.mjs --module 4
node create-forms.mjs --module final

Replace previous forms (recommended after manifest changes)

powershellnode create-forms.mjs --replace

Deletes every form listed in forms-output.json, then recreates from the manifest (quiz keys, banner, Drive folder).

Move existing forms into one Drive folder

powershellnode create-forms.mjs --organize-folder

Uses drive_folder from google-forms-manifest.yaml. Does not create new forms.

Regenerate trainer answer key (Markdown)

powershellnode sync-grading-artifacts.mjs

Reads google-forms-grading.yaml → updates google-forms-reflection-answer-key.md.

Open forms-output.json (includes driveFolder.folderUrl — all forms are moved into that Drive folder):

json{
  "generatedAt": "2026-05-31T12:00:00.000Z",
  "forms": [
    {
      "module": 0,
      "title": "MHP DE Workshop — Mod 0 Reflection",
      "formId": "1FAIpQLS...",
      "editUrl": "https://docs.google.com/forms/d/1FAIpQLS.../edit",
      "responderUrl": "https://docs.google.com/forms/d/1FAIpQLS.../viewform"
    }
  ]
}

Share responder URLs after each module reflection (see module-delivery-pattern.md).

For each form:

1. Open editUrl from forms-output.json.
2. Responses tab → Link to Sheets → create or select a spreadsheet.
3. Suggested Sheet name: MHP-DE-2026-Mod{NN}-Responses.

Repeat for Mod 0–7 (and 8–9 if created).

In each form Settings:

• Turn off Restrict to users in your organization if trainees use personal Gmail.
• Choose whether to collect email addresses (or rely on the "Full name" question in the manifest).
• Confirm Accepting responses is on.

Generate QR codes from each responderUrl (any QR generator).

1. Edit google-forms-manifest.yaml.
2. Option A (recommended) — Replace via script (uses forms-output.json from your last successful run):

powershell   node create-forms.mjs --replace --dry-run   # preview deletes
   node create-forms.mjs --replace             # delete + recreate all
   node create-forms.mjs --module 4 --replace  # one module only

Deletes go to Google Drive trash (Forms file). Linked Sheets keep historical responses; point Sheets at the new forms if needed.

1. Option B — node create-forms.mjs --delete-only then create without --replace.
2. Option C — Manually edit existing forms in the Google Forms UI.

The script does not update existing forms in place; without --replace it adds new forms each run.

Quiz mode and auto-grading (Mod 0–9)

Reflection forms are created as quizzes using google-forms-grading.yaml:

• Correct answers and point values (radio = 1 pt, checkbox = 2 pt by default)
• Correct/incorrect feedback (when_right / when_wrong) shown after submit
• Scales, grids, Module 0.7 risk question, and final survey are not auto-graded

Regenerate trainer answer key after editing grading:

powershellnode sync-grading-artifacts.mjs

In Google Forms → Settings → Quizzes, set Release grade (e.g. immediately after submission).

After you click Continue, the browser may show:

> *This site can’t be reached* — *localhost refused to connect*

That means Google redirected to localhost but the script was not running a local server yet (or the terminal was closed).

Fix (Desktop client — most users)

1. Confirm OAuth client type is Desktop app on Clients — you do not need to add redirect URIs.
2. Delete old token: remove trainer/google-forms/token.json.
3. Run the script and keep the terminal open:

powershellcd /workshop-2026-v2/trainer/google-forms
node create-forms.mjs

1. Wait until the terminal prints: OAuth: waiting for browser callback on 127.0.0.1:53187 ...
2. Complete sign-in in the browser → you should see Authorization successful (not connection refused).

Fix (only if your client type is Web application)

1. Clients → click the Web client → Authorized redirect URIs → add:

text   http://127.0.0.1:53187/oauth2callback

1. Save, delete token.json, run node create-forms.mjs again.

Still failing?

If the browser shows:

> *Access blocked: … has not completed the Google verification process*

> *Error 403: access_denied*

the OAuth app is in Testing mode and your Google account is not on the Test users list (or you signed in with a different account).

Fix in Google Cloud Console (2 minutes)

1. Open Google Cloud Console → select project mhp-de-workshop-forms (or your project name).
2. APIs & Services → OAuth consent screen.
3. Scroll to Test users → + ADD USERS.
4. Enter the exact email you use in the browser, e.g. juewei.jin@gmail.com → Save.
5. Wait 1–2 minutes, then run node create-forms.mjs again and open a new OAuth URL (or incognito window).
6. Sign in only as that test user—not a work account alias unless that alias is also added.

Checklist

If you have Google Workspace (company account)

On OAuth consent screen, if Internal is available:

1. Switch user type to Internal (org-only; no Test users list).
2. Only accounts in your organization can authorize—use your @company.com account consistently.

What not to do

• Publish app to production without verification—you will hit Google’s review process; unnecessary for a trainer script.
• Add scopes beyond forms.body unless you need them—extra scopes slow consent and verification.

• credentials.json and token.json are not committed
• forms-output.json contains only public form IDs (safe to share with co-trainers)
• Workshop forms owned by a team Google account if possible
• Revoke access later: Google Account → Third-party access

textworkshop-2026-v2/trainer/google-forms/
├── google-forms-manifest.yaml          ← source of truth for questions
├── google-forms-grading.yaml           ← quiz keys + feedback (Mod 0–9)
├── google-forms-reflection-design.md
├── google-forms-reflection-design.html
├── google-forms-reflection-answer-key.md
├── google-forms-gcp-setup-guide.md     ← this document
├── google-forms-gcp-setup-guide.html
├── create-forms.mjs                    ← automation entry point
├── sync-grading-artifacts.mjs          ← answer-key.md from grading YAML
├── package.json
├── credentials.json                    ← you download (gitignored)
├── credentials.json.example
├── token.json                          ← created on first auth (gitignored)
└── forms-output.json                   ← URLs after successful run

powershellcd /workshop-2026-v2/trainer/google-forms
npm install
node create-forms.mjs --dry-run
node create-forms.mjs
node create-forms.mjs --replace
node create-forms.mjs --module 7 --replace
node create-forms.mjs --delete-only
node create-forms.mjs --organize-folder
node sync-grading-artifacts.mjs

Regenerate HTML companion for this guide:

powershellpython d:\GitRepo\MHPDataEngineerWorkshop\workshop-2026-v2\trainer\build_trainer_html.py google-forms/google-forms-gcp-setup-guide.md