Collect results in your own Google Sheet

Optional. Without this, students email you their results link, and that works fine. With it, every finished sort also adds a row to a Google Sheet you own: name, date, Must Have, Would Be Nice, Want to Learn More, No Way, any cards marked private, colleges, and a link to the full report.

The data goes from the student's browser straight to your Sheet. It never touches this site. Setup takes about ten minutes and needs a Google account.

Steps

  1. Create a new Google Sheet. Name it something like Card Sort Results.
  2. In the Sheet, open Extensions → Apps Script.
  3. Delete whatever is in the editor, paste the code below, and click the save icon.
  4. Click Deploy → New deployment. Next to “Select type,” click the gear and choose Web app.
  5. Set Execute as to Me, and Who has access to Anyone. (Students don't have Google accounts tied to you, so “Anyone” is what lets their results reach the Sheet. It doesn't let anyone read the Sheet.)
  6. Click Deploy. Google will ask you to authorize the script. If you see “Google hasn't verified this app,” click Advanced, then Go to (your project name). This warning appears for any personal script; you're authorizing your own code to write to your own Sheet.
  7. Copy the Web app URL (it ends in /exec) and paste it into step 1 on the counselor page. Links you make from then on will send results to your Sheet.

If your school or company manages your Google account, an administrator may have turned off the “Anyone” option. If you don't see it, use a personal Google account, or skip this and rely on emailed links.

The code

/**
 * Show Your Hand: adds one row per finished sort to this Sheet.
 * Paste into Extensions → Apps Script, then Deploy → New deployment → Web app
 * (Execute as: Me · Who has access: Anyone).
 */
const SHEET_NAME = 'Card Sorts';
const HEADERS = ['Received', 'Student', 'Sorted by', 'Role', 'Finished',
                 'Must Have', 'Would Be Nice', 'Want to Learn More', 'No Way', 'Private (counselor only)',
                 'Colleges', 'Report link'];

function doPost(e) {
  const lock = LockService.getScriptLock();
  lock.waitLock(10000);
  try {
    const d = JSON.parse(e.postData.contents);
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sh = ss.getSheetByName(SHEET_NAME) || ss.insertSheet(SHEET_NAME);
    if (sh.getLastRow() === 0) {
      sh.appendRow(HEADERS);
      sh.setFrozenRows(1);
    }
    // Text only: a leading = + - or @ is neutralized so nothing runs as a formula.
    const clean = v => {
      const s = String(v == null ? '' : v).slice(0, 5000);
      return /^[=+\-@]/.test(s) ? "'" + s : s;
    };
    sh.appendRow([new Date()].concat(
      [d.student, d.name, d.role, d.date, d.must, d.nice, d.learn, d.noway, d.private, d.colleges, d.report].map(clean)));
    return ContentService.createTextOutput('ok');
  } catch (err) {
    return ContentService.createTextOutput('error');
  } finally {
    lock.releaseLock();
  }
}

Good to know