Original Project Overview We maintain a Google Sheets database of academic figures, each with the following columns:
Name Surname Email University Subject
We need to automatically fetch the latest two publications for each individual using: Google Scholar (via SerpAPI’s Scholar API) ORCID (public API) OpenAlex (public API)
The results must be inserted back into specified columns in the Google Sheet (F–Q), updated daily or on-demand.
Key Challenges Sorting by Latest in Google Scholar By default, Scholar returns top-cited or most-relevant articles, not necessarily newest. Must force a date sort (&sort=pubdate) or a separate fallback approach. Handling Ambiguous Names Multiple authors can share the same name. Potentially need to detect and filter out mismatched results. Ensuring Valid Data Types Google Sheets rejects structured or non-string data. Must parse out just the string or numeric year/date. Rate Limits and API Keys Respect daily or per-minute call limits for SerpAPI, ORCID, OpenAlex. Must handle errors gracefully.
Automation Should run as a script from a server or cloud environment (we have console access). Possibly on a schedule (e.g., daily cron job).
Responsibilities Set up a robust Python script or application that: Authenticates to Google Sheets (using service account credentials). DONE Retrieves our researcher data (Name, Surname, Email, University, Subject) from the “Pubs” or “Topics” sheet. Calls SerpAPI’s Google Scholar endpoint with date-sorted queries, including fallback logic if no results appear. Calls ORCID’s public API (optional, can parse structured date fields carefully). Calls OpenAlex’s public API to fetch the newest two works by each author. Inserts the results (title, year/date) back into the correct columns in the Google Sheet (F–Q). Handle name disambiguation or minimal validation so we don’t store obviously incorrect publications.
Deliverables Python script (or small app) that can be triggered manually or on a schedule. Successful write-back of 2 publications each from Google Scholar, ORCID, and OpenAlex (6 total) into the correct columns. Error handling: If no results, store “N/A” or a sensible fallback. Instructions on setup, environment variables, and usage (e.g., README).
What Still Needs To Be Done 1. Fix Google Scholar Fetch Main Problem: You're getting most cited or incorrect results.
Needs Fixing:
Ensure sort=pubdate is actually respected in SerpAPI query.
Add fallback logic: if pubdate query fails (returns nothing), retry without it.
Clean up irrelevant results using basic validation (e.g., check university or subject in result string).
2. OpenAlex Improvements Currently works for some authors. But still needs name disambiguation logic:
Validate author’s affiliation or match on email (if possible).
Fallback if OpenAlex returns wrong author.
3. ORCID Integration (Optional) You paused this due to structured date errors.
If you want to include it again later:
Parse structured publication-date fields into simple strings.
Validate the correct ORCID author before fetching works.
4. Error Handling Improve logging to show:
Which step failed (e.g. API call, parsing, writing to sheet).
Show why (e.g., "no publications found", "mismatched data", "bad year format").
Replace all "N/A" logic with something more transparent (e.g., "No match found for [X]").
5. Name Disambiguation Logic Currently just using Name + University + Subject in the query.
Needs:
Smarter matching logic to avoid irrelevant or repetitive publications.
Maybe strip long names or fuzzy-match affiliations if exact match fails.
6. Cleaner Sheet Writes Fix Google Sheets writing errors:
Avoid trying to write structured JSON (like ORCID publication-date objects).
Ensure all fields written are flat strings (title + year/date only).
7. Automation (Optional) Set up to run automatically (cron job, server scheduler) if needed.
React WhatsApp Cloud API Automation Category: Amazon Web Services, Backend Development, Docker, Frontend Development, JavaScript, Node.js, PHP, REST API Budget: $10 - $30 USD
26-Apr-2026 10:04 GMT
UrbanPlatter---Food-Delivery-App Category: Artificial Neural Network, Backend Development, CSS, Frontend Development, HTML5, Python, Web Application, Web Design, Web Development Budget: ₹12500 - ₹37500 INR
26-Apr-2026 10:04 GMT
Publicación Técnica de Libro en KDP Category: Book Design, Editing, Graphic Design, PDF, Print Design, Proofreading, Publishing, Technical Writing Budget: €30 - €250 EUR
26-Apr-2026 10:04 GMT
Accurate PDF/Excel Data in Access Category: Data Analysis, Data Entry, Data Extraction, Data Integration, Data Management, Data Processing, Database Management, Excel, Microsoft Access, PDF Budget: ₹100 - ₹400 INR
26-Apr-2026 10:02 GMT
Amazon Jewellery Listing & SEO Category: Amazon, Content Writing, Copywriting, Digital Marketing, EBay, Internet Marketing, Keyword Research, Product Descriptions, SEO Budget: ₹12500 - ₹37500 INR
26-Apr-2026 10:01 GMT
Corporate Video Editor Needed Category: Adobe Premiere Pro, After Effects, DaVinci Resolve, Final Cut Pro, Video Editing, Video Post Editing, Video Production, Video Services Budget: ₹600 - ₹1500 INR
26-Apr-2026 09:59 GMT
Instagram Content Creation & Management Category: Photoshop, After Effects, Animation, Content Creation, Graphic Design, Social Media Management, Video Editing, Video Production Budget: ₹600 - ₹1500 INR
26-Apr-2026 09:58 GMT
DQ200 UDS Diagnostic Test Bench Category: Data Analysis, Documentation, Embedded Systems, Microcontroller, Python, Regression Testing, Reverse Engineering, Signal Processing, Software Development, Test Automation Budget: €1500 - €3000 EUR
Full-stack WordPress Development for Arborman Category: Analytics, API Integration, Content Creation, Digital Marketing, Google Analytics, Lead Generation, SEO, Web Design, Web Development, WordPress Budget: $250 - $750 USD
Instagram Reels Video Editor Category: Adobe Premiere Pro, After Effects, CapCut, Final Cut Pro, Video Ads, Video Editing, Video Post Editing, Video Processing, Video Production, Video Services Budget: ₹600 - ₹1500 INR
26-Apr-2026 09:48 GMT
Comprehensive SEO Overhaul Needed Category: Analytics, Content Strategy, Conversion Rate Optimization, Google Analytics, Internet Marketing, Keyword Research, Link Building, SEO Budget: ₹1500 - ₹12500 INR
