Automate research with this script. It turns a Google Sheet into an AI that plans, researches, and writes detailed reports on any topic.
The Gemini App (gemini.google.com) has a sophisticated Deep Research agent whose capabilities are incredibly powerful, but they are designed to tackle one complex topic at a time. The manual process of running each query and collating the results can however be very slow.
What if you could deconstruct the capabilities of Deep Research and build a similar agent which is instead orchestrated from a Google Sheet? Thankfully, Google Workspace Developer Expert, Stéphane Giron, has designed and shared an elegant solution that transforms Google Sheets and Apps Script into a powerful, automated, bulk research assistant.
Stéphane’s approach is built on a “Divide and Conquer” strategy, which he outlines in his detailed article on the project.
Instead of just asking a single AI to answer a complex question, this script acts as a manager for a specialised AI team… The process is broken down into three distinct phases:
Phase 1: The Strategist (Plan): A powerful AI model analyses your main query and breaks it down into a logical plan of smaller, essential sub-questions.
Phase 2: The Researcher (Execute): A fast, efficient AI model takes each sub-question and uses targeted Google searches to find factual, concise answers.
Phase 3: The Editor (Synthesise): The strategist AI returns to act as an editor, weaving all the collected research and data into a single, cohesive, and well-written final report.
How the AI Research Team Works
The solution, which is available in full on GitHub, uses a clever combination of different Gemini models and tools, all orchestrated by Google Apps Script:
Input: The user simply lists all their research topics in a Google Sheet (e.g., in column A).
Phase 1 (Plan): The script loops through each topic. For each one, it calls the Gemini 2.5 Pro model, treating it as the “Strategist.” It uses Gemini’s function-calling capability to force the model to output a structured plan of sub-questions needed to answer the main query.
Phase 2 (Execute): The script then takes this list of sub-questions and calls the Gemini 2.5 Flash model for each one, treating it as the fast “Researcher.” This call uses the built-in Google Search tool (grounding) to find up-to-date, factual answers for each specific sub-question.
Phase 3 (Synthesise): Finally, with all the collected research, the script calls Gemini 2.5 Pro one last time. In this “Editor” role, the model receives the original query and all the question/answer pairs, synthesising them into a single, comprehensive report.
Output: The script creates a new Google Doc for this final report and places a link to it directly in the Google Sheet, next to the original topic.
This “AI team” approach is a fascinating pattern we’re beginning to see emerge in the community. It strongly echoes the “AI Scrum Master” concept shared by Jasper Duizendstra at the recent Google Workspace Developer Summit in Paris. Both solutions smartly move away basic prompting and instead orchestrate a team of specialised AIs, leading to a far more robust and scalable workflow.
Stéphane’s script is highly customisable, allowing you to set the number of sub-questions to generate, define the output language, and pass the current date to the models to ensure the research is timely.
This is a fantastic example of how to build sophisticated, autonomous AI agents inside Google Workspace. A big thank you to Stéphane Giron for sharing this project with the community.
Get Started
You can find the complete code, setup instructions, and a deeper dive into the architecture at the links below:
