Assessment survey scoring and results in Google Sheets

01Summary

1. A 15-question assessment across 5 domains is scored in Google Sheets, including reverse-scored items.
2. 10 test responses from 6 participants produce domain scores, gaps and a focus area.
3. Check-ins are compared to baseline (4 so far) and 1 incomplete response is caught before it counts.

10 test responses from 6 participants produce domain scores, gaps and a focus area.

Key point, computed from the sample files

02The scenario

In this scenario scores are worked out by hand from a survey export. Reverse-worded questions are easy to score the wrong way, and incomplete responses slip through.

03Issues in the data

04How I approached it

• Scoring logic for 15 questions across 5 domains, with reverse-scored items marked.
• A modular survey flow (10 blocks) and a field map from survey fields to sheet columns.
• A results tab with domain scores, gaps against each person's priorities, a focus domain and the change since their baseline.
• An Apps Script web app (doPost) that writes one row per response.

05How this applies to your business

1. Assess and designMap the current process, size the gaps from your own data, agree the design and a written scope.
2. Build and testBuild in your own tools with the checks built in, then run real cases end to end.
3. Document and hand overSOPs, a walkthrough and a handover, so your team runs it without me.

06Controls and checks

07What the files show

• 10 responses from 6 placeholder participants run through the scoring.
• The QA column flags 1 response with a missing answer instead of scoring it as zero.
• 205 formulas, so the results can be audited cell by cell.

08Risks and how the build handles them

09Appendix: files

A. Screenshots of the deliverable

Assessment-Scoring-and-Sheets-Results.xlsx › Results

Exhibit 8: Results tab: domain scores, gaps, focus domain, change since baseline and a QA flag on the incomplete response

Source: sample files

Assessment-Scoring-and-Sheets-Results.xlsx › Scoring

Exhibit 9: Scoring logic tab: 15 items across 5 domains with reverse-scored items marked

Source: sample files

PREVIEW.png · one-page overview

Exhibit 10: Overview of the survey-to-Sheets sample: scoring logic, survey flow and the results tab

Source: sample files

function doPost(e) {
  const lock = LockService.getScriptLock(); lock.waitLock(20000);
  try {
    const data = JSON.parse(e.postData.contents);
    const sh = SpreadsheetApp.getActive().getSheetByName(SHEET);
    const ids = sh.getRange(5, 1, Math.max(sh.getLastRow() - 4, 1), 1).getValues().flat();
    if (ids.indexOf(data.ResponseId) !== -1) return out({status: 'duplicate', id: data.ResponseId});
    const missing = FIELDS.filter(f => data[f] === undefined || data[f] === '');
    sh.appendRow(FIELDS.map(f => data[f] === undefined ? '' : data[f]));
    return out({status: missing.length ? 'saved_with_gaps' : 'saved', missing: missing});
  } finally { lock.releaseLock(); }
}

B. Downloadable files

Sample build: realistic data, built to show how I'd handle this work. Names are placeholders and no client data is used.