How I Automated My Monthly Reporting with Apps Script and Gemini?

The Problem: 3 Hours of Copy-Pasting Every Month

Every end of month, the same ritual: extract data from multiple Google Sheets, consolidate it into a summary table, generate a few charts, write a comment for each KPI, and assemble everything into a Slides deck for management.

Manual, time-consuming, and error-prone. A perfect candidate for automation.

The Solution Architecture

1. Google Sheets as the data source (operational dashboards)
2. Apps Script to consolidate and transform data automatically
3. Gemini API to generate analytical comments for each KPI

Step 1: Consolidate Data With Apps Script

The script reads key ranges from each source sheet and assembles them in a "Summary" tab.

function consolidateData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const summary = ss.getSheetByName('Summary');
  const sources = ['Sales', 'Marketing', 'Support'];
  summary.clearContents();
  sources.forEach(sheetName => {
    const sheet = ss.getSheetByName(sheetName);
    const data = sheet.getRange('A2:E50').getValues()
      .filter(row => row[0] !== '');
    summary.getRange(summary.getLastRow() + 1, 1, data.length, data[0].length)
      .setValues(data);
  });
}

Step 2: Call the Gemini API for Comments

function generateComment(kpi, value, target, trend) {
  const prompt = `You are a business analyst. Write a concise comment (2-3 sentences)
for this KPI: ${kpi} — Value: ${value} — Target: ${target} — Trend: ${trend}.
Professional and factual tone. Include one actionable recommendation.`;
  const apiKey = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
  const url = 'https://generativelanguage.googleapis.com/v1beta/models/gemini-2.0-flash:generateContent?key=' + apiKey;
  const res = UrlFetchApp.fetch(url, {
    method: 'post', contentType: 'application/json',
    payload: JSON.stringify({ contents: [{ parts: [{ text: prompt }] }] })
  });
  return JSON.parse(res.getContentText()).candidates[0].content.parts[0].text;
}

Step 3: Assemble the Slides Report Automatically

The script duplicates a Slides template, replaces placeholders like {{KPI1}} and {{COMMENT1}} with real values, and sends a link by email. Monday morning at 8am, the report is ready — automatically generated the previous night.

The Result: From 3 Hours to 20 Minutes

The remaining 20 minutes are used to review the Gemini comments, tweak 2-3 visual elements, and validate before sending to management. The heavy lifting is done.

What Process Could You Automate?

If a task is repetitive, Google-data-based, and you do it every week or month — it's probably automatable with Apps Script. Feel free to reach out if you'd like to know more about implementation.