Recommend personalized learning paths

Northwind runs a learning programme with a catalogue of courses, and the awkward question every learner asks is the same one: “what should I do next?” A coach can answer it well, but a coach cannot answer it for 300 people every week. So most learners guess, pick something too easy or too hard, and drift.

This script closes that gap. It reads each learner’s completed courses and stated goals, hands both to Claude alongside the full catalogue, and asks for the single best next course with a one-sentence reason. The recommendation lands in a column on the learners sheet, ready for a coach to glance at or for a weekly email to pick up. It only fills blank cells, so a learner keeps the same suggestion until they finish something new.

What you’ll need

• A Learners Google Sheet with a header row and these columns: name, completed (a comma-separated list of finished courses), goals (free text), and an empty nextRecommendation column for the output.
• A Catalogue Google Sheet with a header row, one course per row, and the columns title and summary.
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.

The script

// The sheet of learners — progress in, recommendation out.
const LEARNERS_SHEET_ID = '1abcLearnersId';

// The sheet of courses Claude is allowed to recommend from.
const CATALOGUE_SHEET_ID = '1abcCatalogueId';

// 1-based column index for the nextRecommendation output column.
const RECOMMENDATION_COL = 5;

/**
 * Reads each learner's progress and goals, asks Claude to pick the best
 * next course from the catalogue, and writes the answer back to the sheet.
 * Skips any learner who already has a recommendation.
 */
function recommendNext() {
  // 1. Read the learners and the course catalogue.
  const learners = readSheet(LEARNERS_SHEET_ID);
  if (!learners.length) {
    Logger.log('No learners to process — nothing to do.');
    return;
  }

  // 2. Flatten the catalogue into one block of "title: summary" lines
  //    so the whole option set fits in a single prompt.
  const catalogue = readSheet(CATALOGUE_SHEET_ID)
    .map((c) => c.title + ': ' + c.summary)
    .join('\n');
  if (!catalogue) {
    Logger.log('Catalogue is empty — nothing to recommend.');
    return;
  }

  // 3. Hold a reference to the output sheet for the write-back.
  const sheet = SpreadsheetApp.openById(LEARNERS_SHEET_ID).getSheets()[0];

  learners.forEach((l, i) => {
    // 4. Skip learners who already have a recommendation.
    if (l.nextRecommendation) return;

    // 5. Build a prompt with the learner's history, goals, and the catalogue.
    const prompt =
      'Given a Northwind learner who has completed: ' + l.completed +
      '. Goals: ' + l.goals +
      '. Pick the single best next item from this catalogue and explain ' +
      'why in one sentence.\n\nCatalogue:\n' + catalogue;

    // 6. Sonnet does the matching — it needs to reason over the whole list.
    const out = callClaude(prompt, 'claude-sonnet-4-6', 200);

    // 7. Write the recommendation next to the learner (row i + 2 for header).
    sheet.getRange(i + 2, RECOMMENDATION_COL).setValue(out);
  });

  Logger.log('Finished recommending for ' + learners.length + ' learners.');
}

/**
 * Reads a sheet's first tab into an array of objects keyed by the header row.
 */
function readSheet(id) {
  const [h, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((r) => Object.fromEntries(h.map((k, i) => [k, r[i]])));
}

/**
 * Minimal Anthropic API call. The key lives in Script Properties — it
 * is never pasted into the code.
 */
function callClaude(prompt, model = 'claude-haiku-4-5-20251001', maxTokens = 400) {
  const key = PropertiesService.getScriptProperties()
    .getProperty('ANTHROPIC_API_KEY');
  const res = UrlFetchApp.fetch('https://api.anthropic.com/v1/messages', {
    method: 'post',
    contentType: 'application/json',
    headers: { 'x-api-key': key, 'anthropic-version': '2023-06-01' },
    payload: JSON.stringify({
      model,
      max_tokens: maxTokens,
      messages: [{ role: 'user', content: prompt }],
    }),
  });
  return JSON.parse(res.getContentText()).content[0].text.trim();
}

How it works

1. recommendNext reads the learners sheet into objects keyed by header, and bails out early if there are no learners.
2. It reads the catalogue and flattens it into one block of title: summary lines — the whole option set Claude is allowed to choose from. If the catalogue is empty it stops, since there is nothing to recommend.
3. For each learner, it skips anyone who already has a value in nextRecommendation, so existing suggestions are never overwritten.
4. It builds a prompt carrying the learner’s completed courses, their goals, and the full catalogue, then asks for one course plus a one-sentence reason.
5. It calls Claude Sonnet, which is worth the extra cost here because picking the best fit means weighing every catalogue entry against the learner’s history.
6. It writes the reply into the nextRecommendation column at row i + 2 — the + 2 accounts for the header row plus the zero-based index.

Example run

Say the Learners sheet holds rows like these:

name	completed	goals	nextRecommendation
Priya Shah	Intro to Apps Script, Sheets Basics	Automate monthly reports	(blank)
Tom Reilly	Intro to Apps Script	Build a Gmail workflow	(blank)

After a run, the blank column is filled:

name	nextRecommendation
Priya Shah	Triggers and Scheduling — it teaches the time-driven runs you need to send reports automatically.
Tom Reilly	Working with GmailApp — it is the natural follow-on once you know the basics and want to handle email.

Each learner gets one concrete next step with a reason a coach would agree with.

Trigger it

This is a good fit for a weekly schedule, so new learners and anyone who has just finished a course gets a fresh suggestion:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose recommendNext, a Time-driven source, and a Week timer — for example, every Monday morning.

Because the script only fills blank cells, re-running it costs nothing for learners who already have a recommendation.

Watch out for

• Recommendations go stale. The script never overwrites a filled cell, so a learner keeps the same suggestion until they complete it. Clear the nextRecommendation cell when their completed list changes to force a refresh.
• The catalogue rides in every prompt. A large catalogue makes each call bigger and slower. If it grows past a few dozen courses, pre-filter to the courses relevant to a learner’s goals before building the prompt.
• Claude can only pick what it is shown. If a course is missing from the Catalogue sheet it will never be recommended — keep the catalogue current.
• One learner per call means one API call each. For a few hundred learners that is fine on a weekly run; for thousands, batch several learners into one prompt and ask for a JSON array of recommendations.