# Twice-weekly PubMed paper tracker in Google Sheets

> Run your saved PubMed searches on a schedule and append any new papers to a Google Sheet, so your literature tracker stays current without manual work.

- Workflow type: code
- Services: PubMed / NCBI E-utilities, Google Sheets
- Categories: Operations, Personal Productivity
- Published: 2026-06-30

## What it does

- Runs every Monday and Thursday morning, reads a list of saved searches from a config tab in your Google Sheet, and looks up new PubMed papers for each one.
- For every new paper, captures the title, authors, journal, publication date, DOI, PMID, and a direct PubMed link.
- Appends one row per new paper to your tracker tab and skips anything already in the sheet, so the same paper never gets logged twice.
- Updates the last-run timestamp on each saved search so the next run only pulls papers added since the last sweep.

## What you'll need

- A Google account that can edit the tracker spreadsheet.
- A free NCBI account so PubMed lets you run more searches per minute. The signup link is in the connect screen.
- A Google Sheet with two tabs: a config tab listing your saved searches (query plus optional journal filter) and a tracker tab where new papers will be appended.

## Prompt

Build a code workflow that runs twice a week (Monday and Thursday at 7am ET via cron) and keeps a Google Sheets literature tracker in sync with PubMed. The workflow has no judgement steps. Every step is a structured call against a fixed list of saved searches.

Inputs the workflow expects (configurable when the workflow is installed): the spreadsheet ID of the tracker, the name of the config tab (default: Config), and the name of the tracker tab (default: Papers).

The Config tab has one row per saved search with these columns: query_name, query (a PubMed query string such as `("CRISPR"[tiab] AND base editing[tiab])`), journal (optional, e.g. `Nature` or blank), and last_run (ISO timestamp written back by the workflow). The Papers tab has these columns in order: query_name, PMID, title, authors, journal, pub_date, DOI, URL, added_at.

On each run:

1. Read the Config tab from Google Sheets (Get Values on `${configTab}!A:D` with the header row). Skip rows where query is blank.

2. Read the existing Papers tab once (Get Values on `${trackerTab}!B:B` to load the PMID column) and build an in-memory Set of PMIDs already logged. This is the dedup index.

3. For each Config row, compute the date window. Start = the row's last_run (or 90 days ago if blank), end = now. Format both as `YYYY/MM/DD` and append ` AND ("${start}"[edat] : "${end}"[edat])` to the query. If the journal field is non-empty, also append ` AND "${journal}"[ta]`.

4. Call PubMed ESearch on the pubmed database with `retmode=json`, `retmax=200`, and `datetype=edat`. If the returned `Count` is greater than 200, paginate using `retstart` in steps of 200 until all UIDs are collected.

5. Filter the returned PMID list against the dedup Set so we only fetch metadata for genuinely new papers.

6. For the new PMIDs only, call PubMed ESummary on the pubmed database with `retmode=json`, batching in chunks of 200 IDs per call. From each DocSum extract: title (`title`), authors (join `authors[].name` with `, `), journal (`fulljournalname` or fall back to `source`), pub_date (`pubdate`), DOI (find `articleids` entry where `idtype` is `doi`, else blank), and PMID (`uid`). Build the URL as `https://pubmed.ncbi.nlm.nih.gov/${PMID}/`.

7. Build one row per new paper in the column order [query_name, PMID, title, authors, journal, pub_date, DOI, URL, added_at] where added_at is the current ISO timestamp. Add the new PMIDs to the in-memory Set so a paper that matches two saved searches still only gets logged once per run.

8. After all Config rows are processed, append all collected rows to the Papers tab in a single Append Values call on `${trackerTab}!A:I` with valueInputOption=`USER_ENTERED`. If there were zero new rows, skip the append.

9. Write the new last_run timestamp (the same `now` captured at the start of the run) back to the Config tab. Use Update Values on the last_run cells for the rows that were processed (column D, rows 2..N). Do this even when a row returned zero new papers, so the next window starts from this run.

Logging: emit a summary like `5 saved searches, 12 new papers appended (3 duplicates skipped)`. Errors on a single PubMed query should be logged and the run should continue with the next Config row rather than aborting the whole sweep.

## How to customize

- Change the schedule. The default is twice a week, but you can run it daily, weekly, or only on weekdays.
- Edit the saved searches at any time by updating the config tab in the spreadsheet. No code changes needed.
- Add or rename columns on the tracker tab to capture extra fields, such as an abstract snippet or a notes column for your team.

## FAQ

### How does it avoid logging the same paper twice?

Before appending, the workflow reads the PMIDs already in your tracker tab and filters out any duplicates. The same paper will never show up on two different rows, even if it matches several of your saved searches.

### What goes in the config tab?

One row per saved search. Each row has a name for the search, the PubMed query you want to run, an optional journal filter, and a last-run timestamp that the workflow updates automatically after each sweep.

### Can I track papers across many different topics?

Yes. Add as many rows to the config tab as you like. Each row runs independently, so a lab can mix oncology, neuroscience, and methodology searches in a single tracker.

### Do I need a paid PubMed plan?

No. PubMed is free. The workflow uses NCBI's public search service, and a free NCBI account just gives you a higher rate limit so larger sweeps finish faster.

### What if a search returns hundreds of new papers at once?

The workflow pages through the results so nothing gets dropped. On the first run especially, you may see a large initial batch land in the tracker. After that it only pulls papers added since the previous sweep.

Use this prompt in General Input: https://www.generalinput.com/prompts/twice-weekly-pubmed-paper-tracker-in-google-sheets