Weekly institution publication tracker in Google Sheets
Every Monday morning, pull your university or research lab's new papers from OpenAlex and append them to a living Google Sheet, no manual logging.
Integrations
OpenAlex
Google Sheets
Type
Categories
- Operations
- Marketing
Build me a deterministic code workflow that keeps a Google Sheet up to date with every new paper published by my institution, according to OpenAlex. No reasoning or LLM steps, just a fixed pipeline.
Trigger: a cron schedule that runs every Monday at 6am in my local time zone.
Inputs (configured once at setup): the OpenAlex institution ID or ROR I want to track (for example W or I-prefixed OpenAlex ID, or a ror.org ID for my university or research lab), the Google Sheets spreadsheet ID, and the tab name where new papers should land.
Step 1: call the OpenAlex Search Works operation with filter=authorships.institutions.id:<institution ID> combined with from_publication_date set to the date of the previous run (default to 7 days ago on first run). Use select=id,doi,display_name,publication_date,authorships,primary_location,cited_by_count,open_access to keep the payload small. Paginate through all matching results.
Step 2: call the Google Sheets Get Values operation against the configured tab to read back the existing OpenAlex Work IDs that are already logged (the column that stores the OpenAlex ID per row).
Step 3: in code, drop any OpenAlex result whose ID is already present in the sheet. This is the deduplication step; never write a paper that's already there.
Step 4: for the remaining new papers, call the Google Sheets Append Values operation once, passing one row per new paper with these columns in this order: publication date, title, DOI, first author display name, all author institutions (comma-joined), journal or source display name (from primary_location.source.display_name), open access status (open_access.oa_status), citation count, and the OpenAlex URL (https://openalex.org/<id>). Use valueInputOption USER_ENTERED so dates and links render correctly.
If there are no new papers, the workflow should finish cleanly without writing anything. Handle the empty-sheet case on first run (no existing IDs to dedupe against, so every result is appended). Keep the whole thing simple, deterministic, and easy for a non-technical research office or comms team owner to re-run or inspect.
What it does
- Runs every Monday at 6am and pulls every paper published in the last week that lists your institution as an author affiliation.
- Skips any paper that's already in your spreadsheet so you never get duplicate rows.
- Appends one row per new paper with publication date, title, DOI, first author, all author institutions, journal or source, open access status, citation count, and the OpenAlex link.
- Gives a research office or comms team a living, auditable record of their institution's published output with zero manual work.
What you’ll need
- A Google account with edit access to the spreadsheet you want the papers logged into.
- A free OpenAlex account (sign-up takes a minute and gives you a daily credit allowance that easily covers a weekly run).
- The OpenAlex ID or ROR identifier for the institution you want to track (for example, your university or research lab).
How to customize it
- Change the cadence from weekly to daily, twice a week, or monthly by editing the schedule.
- Track multiple institutions in the same sheet, or split each one into its own tab.
- Add or remove columns, for example funder, topic, or co-author institutions, by tweaking which fields the sheet writes.
- Narrow the results to a specific type of work (journal articles only, or include preprints), or filter to open access papers only.
Use cases
- Research & Monitoring
- Data Sync
