How to automatically track Ask HN posts in Google Sheets (free, no server)

Ask HN threads like "Is there a tool that…?" or "How do you deal with…?" are some of the best places to find problems people want solved. The trouble is that they leave the front page within hours, and nobody has time to read HN every day.

So let's have a spreadsheet do the reading. In this guide we'll build a small Google Apps Script that:

• fetches every new Ask HN post once a day
• writes it to a Google Sheet, skipping posts it has already seen
• flags posts that contain "pain phrases" like is there a tool or struggling with

It runs inside your own Google account, so you don't need a server, API keys or a paid automation tool. Setup takes about 10 minutes.

Just want the finished sheet without building it? There's a ready-made version, HN Idea Radar, described at the end.

How it works

Hacker News has a free search API run by Algolia at hn.algolia.com/api/v1. The search_by_date endpoint lets us ask for posts tagged ask_hn that were created after a certain timestamp. No key is needed.

Google Apps Script can call that API with UrlFetchApp, write rows to a sheet, and run itself on a schedule with a time-based trigger. That's all we need.

Step 1: Create the sheet and open Apps Script

1. Create a new Google Sheet at sheets.new.
2. Click Extensions → Apps Script.
3. Delete the empty myFunction and paste the code below.

Opening Apps Script from inside the sheet matters. A script created directly at script.google.com isn't attached to any spreadsheet, so it has nowhere to write.

Step 2: The script

const SHEET_NAME = 'Ask HN';
const HEADERS = ['HN ID', 'Posted', 'Title', 'Points', 'Comments', 'Pain signal', 'Link'];

// Phrases that often mean "someone has a problem". Add your own.
const PAIN_PHRASES = [
  'is there a tool',
  'is there an app',
  'is there any',
  'alternative to',
  'looking for',
  'struggling',
  'frustrat',      // matches frustrated, frustrating, frustration
  'tired of',
  'wish there was',
  'anyone else',
  'how do you',
  'how do i',
  'what do you use',
  'best way to',
  'recommend',
];

function fetchAskHN() {
  const sheet = getSheet_();
  const lastRow = sheet.getLastRow();

  // First run: go back 7 days so the sheet isn't empty.
  // After that: the last 2 days (duplicates are skipped below).
  const days = lastRow > 1 ? 2 : 7;
  const since = Math.floor(Date.now() / 1000) - days * 24 * 60 * 60;
  const url = 'https://hn.algolia.com/api/v1/search_by_date'
    + '?tags=ask_hn&hitsPerPage=1000'
    + '&numericFilters=' + encodeURIComponent('created_at_i>' + since);

  const hits = JSON.parse(UrlFetchApp.fetch(url).getContentText()).hits;

  // IDs already in the sheet, so we never add the same post twice
  const existing = new Set(
    lastRow > 1
      ? sheet.getRange(2, 1, lastRow - 1, 1).getValues().flat().map(String)
      : []
  );

  const rows = hits
    // The API tags every text post as ask_hn. Keep only real "Ask HN:" posts.
    .filter(h => /^ask hn/i.test(h.title))
    .filter(h => !existing.has(String(h.objectID)))
    .map(h => {
      const text = (h.title + ' ' + (h.story_text || '')).toLowerCase();
      const signal = PAIN_PHRASES.find(p => text.includes(p)) || '';
      return [
        h.objectID,
        new Date(h.created_at),
        h.title,
        h.points,
        h.num_comments,
        signal,
        'https://news.ycombinator.com/item?id=' + h.objectID,
      ];
    });

  if (rows.length) {
    sheet.getRange(lastRow + 1, 1, rows.length, HEADERS.length).setValues(rows);
  }
}

function getSheet_() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  if (!ss) {
    throw new Error('No spreadsheet found. Open this script from your sheet via Extensions → Apps Script.');
  }
  let sheet = ss.getSheetByName(SHEET_NAME);
  if (!sheet) {
    sheet = ss.insertSheet(SHEET_NAME);
    sheet.appendRow(HEADERS);
    sheet.setFrozenRows(1);
  }
  return sheet;
}

// Run this ONCE to make the script run every morning
function createDailyTrigger() {
  ScriptApp.newTrigger('fetchAskHN')
    .timeBased()
    .everyDays(1)
    .atHour(7)
    .create();
}

A few details worth knowing:

• encodeURIComponent is needed because the > in the filter isn't a valid URL character.
• Deduplication uses the HN ID in column A. Each daily run looks back two days, so nothing slips through if the trigger runs late, and posts already in the sheet are skipped.
• The first run goes back a week, so you have something to look at right away instead of an almost empty sheet.
• The ^ask hn filter is there because the API tags every text post without a link as ask_hn, including things like "Hello World". Remove that line if you want them too.
• story_text is the body of the Ask HN post. Checking it as well as the title catches far more pain points, because people often put the actual question in the body.

Step 3: Run it once and allow access

1. Select fetchAskHN in the function dropdown and click Run.
2. Google asks for permission. Because this is a brand-new script in your own account, you'll see a "Google hasn't verified this app" screen. That's normal for any personal script: click Advanced → Go to (your project name) and allow it. The script needs access to your spreadsheet and to external URLs (the HN API).
3. Switch back to the sheet. You should see a new Ask HN tab with the last week's posts.

Step 4: Make it run every day

Select createDailyTrigger in the dropdown and click Run once. From now on the script runs every morning between 7 and 8, even when your computer is off.

Only run it once: every run creates another trigger. You can see and delete triggers under the clock icon (Triggers) in the Apps Script sidebar.

Step 5: Use the data

After a week you'll have a few hundred Ask HN posts in one place. Some things that work well:

• Filter on the Pain signal column to see only posts where someone is looking for something.
• Sort by Comments. A post with few points but many comments often means people care about the problem but nobody has a good answer.
• Add a Notes column to the right and write down ideas as you read.
• Tune PAIN_PHRASES. Look at the flagged posts that turned out to be noise and remove those phrases, and add phrases from good posts that weren't flagged.

Limitations of this version

This script is intentionally small, so a few things are left out:

• Points and comments are frozen at the moment the post was logged. A post logged at 7 a.m. may have 3 points in your sheet and 300 by the evening.
• It only tracks Ask HN, not Show HN, the front page or mentions of your own keywords.
• There's no summary. You have to open the sheet to see what's new.

All of these can be added with more Apps Script: re-fetching recent IDs to update their numbers, more tabs with other tags, and MailApp for a weekly email.

Want the finished version?

If you'd rather not build all of that yourself, I made HN Idea Radar: a ready-made Google Sheet with Ask HN pain points, Show HN launches, Keyword Watch, daily front-page history, live-updating points and comments, a settings tab and a weekly email digest. You copy the sheet, click a menu and it runs. It's on Gumroad.

Either way, I'd like to hear what phrases you end up adding to PAIN_PHRASES. That list is where most of the value is.