How to Randomly Pick from Lists in Google Sheets Guide

Overview

Randomly selecting from lists in Google Sheets sounds simple, but in real businesses it powers a lot: assigning hot leads fairly, choosing contest winners, rotating outreach accounts, sampling tickets for QA. Below are three levels of mastery—from quick manual tricks to fully automated AI‑agent workflows.

‍

1. Manual methods inside Google Sheets

These require no extra tools—just formulas. Perfect for one‑off draws or early experiments.

‍

1.1 Single random item from a column

Use INDEX + RANDBETWEEN.

1. Put your values in A2:A100 (no header in A1, or start at A2 and adjust).
2. In an empty cell, enter:=INDEX(A2:A100, RANDBETWEEN(1, COUNTA(A2:A100)))

3. Each time the sheet recalculates (edit any cell or press F9 equivalent), you’ll get a new random pick.

‍

This works because:

• COUNTA(A2:A100) counts how many non‑blank entries you have.
• RANDBETWEEN(1, that_count) chooses a random row number.
• INDEX returns the corresponding cell.

‍

Official docs: Google’s INDEX function guide – https://support.google.com/docs/answer/3098242 and RANDBETWEEN – https://support.google.com/docs/answer/3093507

‍

1.2 Random item when the range has blanks

If your list has empty cells, the basic formula can return blanks or under‑sample the end.

Use FILTER to strip blanks first:

=INDEX(FILTER(A2:A100, A2:A100<>""), RANDBETWEEN(1, COUNTA(FILTER(A2:A100, A2:A100<>""))))


Steps:

1. Keep your raw list in A.
2. Paste this formula into another cell.
3. The FILTER sub‑formula creates an in‑memory clean list; INDEX draws from that.

‍

1.3 Simpler but less flexible: INDIRECT

For a small, fixed range like A1:A10, you can use:

=INDIRECT("A" & RANDBETWEEN(1,10))


This literally builds a random address like A7. Fast, but brittle—if you insert rows, your logic breaks. Use it only for tiny, static sheets.

‍

1.4 Randomly shuffle an entire list

If you want a random order (for round‑robin assignments, for example):

1. Assume your list is in A2:A100.
2. In B2, enter:=SORT(A2:A100, RANDARRAY(COUNTA(A2:A100),1), TRUE)

3. Column B now holds a randomly shuffled version of A.

‍

Here:

• RANDARRAY generates a random number per row.
• SORT reorders A by those random keys.

‍

Docs: SORT – https://support.google.com/docs/answer/3093150 and RANDARRAY (Google Sheets) – https://support.google.com/docs/answer/9391278

‍

1.5 Random sample of N items

To grab, say, 5 random items:

=ARRAY_CONSTRAIN(
 SORT(A2:A100, RANDARRAY(COUNTA(A2:A100),1), TRUE),
 5,
 1
)


• SORT shuffles.
• ARRAY_CONSTRAIN returns only the first 5 rows.

‍

Docs: ARRAY_CONSTRAIN – https://support.google.com/docs/answer/3093197

‍

Pros (manual):

• Free, native, transparent.
• Great for one‑off use.

‍

Cons:

• You must open the sheet and trigger it.
• No logging or audit trail unless you build it.
• Error‑prone when tired or rushed.

‍

2. No‑code automation around Sheets

Here we keep Google Sheets as the “random brain” but let automation tools orchestrate when and how draws happen.

‍

2.1 Use Google Apps Script for a menu or button

Apps Script is built into Google Sheets and still counts as “no‑code-ish” for power users.

1. In your Sheet, go to Extensions → Apps Script.
2. Paste a function like:function pickRandomLead() {
 const ss = SpreadsheetApp.getActive();
 const sheet = ss.getSheetByName('Leads');
 const data = sheet.getRange('A2:A').getValues().filter(String);
 const idx = Math.floor(Math.random() * data.length);
 const pick = data[idx][0];
 ss.getSheetByName('Results').getRange('A2').setValue(pick);
}

3. Save, then in the editor choose Run once to authorize.
4. Back in the Sheet, add a custom menu:function onOpen() {
 SpreadsheetApp.getUi()
   .createMenu('Random Tools')
   .addItem('Pick random lead', 'pickRandomLead')
   .addToUi();
}

5. Reload the Sheet; use Random Tools → Pick random lead whenever needed.

‍

Docs: Apps Script + Sheets – https://developers.google.com/apps-script/guides/sheets

‍

2.2 Trigger random selection on a schedule with Apps Script

Turn your random sample into a daily or hourly routine.

1. Reuse pickRandomLead above.
2. In Apps Script, click the clock icon (Triggers).
3. Add a trigger:
   • Choose function: pickRandomLead
   • Event source: Time‑driven
   • Type: e.g. Day timer → every day at 8am.
4. Now your result cell is refreshed automatically on schedule.

‍

Use this for:

• Daily contest winners.
• Daily QA ticket sample.
• Rotating which rep gets the next batch of MQLs.

‍

Docs: Time‑driven triggers – https://developers.google.com/apps-script/guides/triggers/installable#time-driven_triggers

‍

2.3 Connect to external no‑code tools

‍

You can also:

• Expose the random pick cell via an Apps Script web app or an automation tool like Zapier/Make.
• Have the tool read that value and push it into Slack, email, or your CRM.

‍

High‑level flow:

1. Sheet uses formulas (from Section 1) to pick.
2. Zapier watches the sheet for changes.
3. When it spots a new value in Results!A2, it posts to Slack or creates a task.

‍

Pros (no‑code):

• Still simple and transparent.
• Time‑based or event‑based runs.

‍

Cons:

• Logic spreads across Sheets + scripts + external tools.
• Still limited to formula logic; no “real” computer‑use automation.

‍

3. Scaling with an AI computer agent

Now imagine you never touch the sheet at all. An AI computer agent (like those built with Sai) behaves like a power assistant at your keyboard.

Simular’s agents are designed to:

• Use the entire desktop, browser, and cloud apps—not just APIs.
• Run thousands to millions of steps with production‑grade reliability.
• Execute transparently: every click, formula edit, and copy‑paste is logged and inspectable.

‍

3.1 Agent as your “random ops” assistant

Example workflow for a sales agency:

1. On a schedule, the agent opens Chrome and navigates to Google Sheets.
2. It opens your lead sheet and:
   • Duplicates the day’s lead list into a working tab.
   • Inserts or refreshes the shuffle formula (e.g. using SORT + RANDARRAY).
   • Copies the randomized results as values to freeze them.
3. It assigns the top N leads to specific reps by writing into assignment columns.
4. It logs the run in a separate “Runs” tab (timestamp, formula used, # of leads).
5. It jumps into your CRM web UI, finds those leads, and updates owner fields—just like a human.

‍

Pros:

• End‑to‑end: from randomization to CRM update.
• Fully reproducible via the execution trace.
• Scales across multiple sheets, accounts, and clients.

‍

Cons:

• Requires initial setup and testing of the agent.
• Best suited when you’re ready for production‑grade automation, not just a weekend hack.

‍

3.2 Agent‑driven contest management

For marketing teams running recurring giveaways:

1. The agent opens the submission form responses Sheet.
2. It filters out duplicates or ineligible entries (e.g., based on country or purchase status), using UI steps or helper formulas.
3. It uses a random selection formula (INDEX + RANDBETWEEN or SORT + RANDARRAY) to pick winners.
4. It copies winners to a Winners tab, timestamps the draw, and exports a PDF report.
5. It sends personalized emails to winners via Gmail and posts a summary into Slack.

‍

You get:

• Locked‑in fairness and compliance.
• Zero manual fiddling with formulas at 11:59pm on campaign day.

To learn how Sai agents work across desktop and browser, see: https://www.sai.work/

In short: start with simple formulas, add Apps Script for repeatability, and when the workflow becomes mission‑critical and cross‑tool, let an AI computer agent run the entire Google Sheets random selection pipeline on your behalf.