Scheduled Task: An Automated Weekly Portfolio Performance Digest

What This Builds

Getting a read on the whole portfolio right now means opening the accounting platform, a spreadsheet, and maybe a rent tracker, then holding the numbers in your head long enough to notice a pattern. This build runs on a timer inside Google Sheets: every Monday morning it pulls your latest per-property numbers, asks Gemini to summarize the week, and drops a plain-language cash flow and occupancy digest into a tab you can read in under a minute, coffee still hot.

Prerequisites

• A Business Standard account with a Google Sheet you already use to track per-property income and expenses (or a plan to build one, see the guide referenced above)
• A Advanced account with an API key from Google AI Studio
• Comfort pasting a short script into the Apps Script editor (no coding background required, just copy and paste)
• 90-120 minutes for setup, most of it spent getting your source data into a consistent weekly format

The Concept

A time-driven trigger in Google Apps Script works like an alarm clock for a script instead of a person. You tell it "run this function every Monday at 7am," and Google's own servers run your script at that time, whether or not you have the spreadsheet open, whether or not your laptop is even on. This build pairs that alarm clock with a call to Gemini's API: the script gathers your numbers, sends them to Gemini with instructions, and writes the response back into the sheet automatically.

You set it up once. After that, the digest just shows up every week.

Build It Step by Step

Part 1: Build the Source Data Tab

In your portfolio tracking spreadsheet, create a tab called WeeklyInput with one row per property, updated by you (or pulled from your accounting platform's export) each week: property name, this week's rent collected, this week's expenses, current occupancy status, and any notes worth flagging.

Create a second tab called Digest where the weekly summary will land. Leave it empty for now.

Caution: the WeeklyInput tab and the data sent to Gemini's API should carry portfolio-level numbers only. Never add tenant names, Social Security numbers, or bank account and routing numbers to this sheet or the prompt. Property nicknames and unit counts are enough for the digest to work. Review Google's data handling terms for the Gemini API before you send real financial figures through it on a recurring schedule.

Part 2: Get a Gemini API Key

Go to Google AI Studio and generate an API key. While you are there, open the model list and copy the exact model id for the current Gemini 3.1 Flash model. The id is the lowercase, hyphenated name the API expects, not the display name shown in the chat app. Keep both somewhere safe. You'll paste them into the script in the next step, and the key should never appear anywhere else in the sheet itself.

Part 3: Open the Script Editor and Add the Trigger

From your spreadsheet, open Extensions → Apps Script. This opens a separate script editor tied to this specific spreadsheet.

Paste in a script structured like this:

function weeklyPortfolioDigest() {
  const ss = SpreadsheetApp.getActive()
  const inputSheet = ss.getSheetByName('WeeklyInput')
  const digestSheet = ss.getSheetByName('Digest')
  const rows = inputSheet.getDataRange().getValues()

  let summaryInput = 'Weekly property data:\n'
  for (let i = 1; i < rows.length; i++) {
    summaryInput += rows[i].join(', ') + '\n'
  }

  const prompt = 'You are summarizing a residential rental portfolio for the ' +
    'owner. Using the data below, write a short digest covering total rent ' +
    'collected, total expenses, net cash flow, any property with an ' +
    'occupancy or payment issue, and one property to keep an eye on this ' +
    'week. Keep it under 200 words, plain language, no tenant names.\n\n' +
    summaryInput

  const apiKey = 'PASTE_YOUR_GEMINI_API_KEY_HERE'
  const modelId = 'PASTE_THE_MODEL_ID_FROM_AI_STUDIO_HERE'
  const url = 'https://generativelanguage.googleapis.com/v1beta/models/' +
    modelId + ':generateContent?key=' + apiKey

  const payload = {
    contents: [{ parts: [{ text: prompt }] }]
  }

  const response = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(payload)
  })

  const data = JSON.parse(response.getContentText())
  const digestText = data.candidates[0].content.parts[0].text

  digestSheet.appendRow([new Date(), digestText])
}

Click Save, name the project something like "Weekly Portfolio Digest," then run the function once manually (click the play button) to authorize the script and confirm it writes a row to the Digest tab.

Part 4: Add the Time-Driven Trigger

In the script editor, click the alarm-clock Triggers icon on the left. Click Add Trigger at the bottom right. Set the function to run as weeklyPortfolioDigest, and set the event source to Time-driven and then Week timer. Pick Monday and a time window such as 7am to 8am. Click Save.

Google's servers now run this function every Monday whether or not the sheet is open.

Real Example: Monday, Eight Properties

Setup: The WeeklyInput tab holds one row per property, updated each Sunday night from the accounting platform's export.

Trigger: The Monday 7am time-driven trigger fires automatically.

Output: A new row appears in the Digest tab: total rent collected across the portfolio, total expenses, net cash flow for the week, a note that one unit's rent is five days late, and a flag that a different property's water bill jumped and is worth checking for a leak.

Time saved: Instead of opening three tools and doing the mental math on a Monday morning, the read on the whole portfolio is waiting in one cell before the first cup of coffee is finished.

What to Do When It Breaks

• No new row appears on Monday and you don't notice for weeks → This is the failure that hides best, since a script that silently stops has no error message waiting in your inbox. Add one line to the script that also emails you a short confirmation (MailApp.sendEmail(...)) every time it runs, so a missing Monday email is your signal something broke.
• The script errors out on data.candidates[0] → This usually means the Gemini API call failed or returned an unexpected shape, often from an expired or mistyped API key. Check the execution log under Executions in the Apps Script editor for the actual error text.
• The digest reads too generic → Add more specific instructions to the prompt, like asking Gemini to name the exact property with an issue rather than describing it vaguely, or to compare this week's numbers to last week's if you keep a running history tab.
• Authorization keeps asking you to reconnect → Apps Script triggers run under the account that created them. If you change Google accounts or revoke access, you'll need to open the script and re-authorize it once.

Variations

• Simpler version: Skip the API key and Apps Script entirely, and paste the WeeklyInput tab into Gemini in Google Sheets by hand each Monday using its built-in sidebar. Less automatic, no script to maintain.
• Extended version: If you'd rather not touch Apps Script at all, ChatGPT's scheduled tasks feature can run a similar recurring prompt on its own schedule from inside ChatGPT, though the number of scheduled tasks and how precisely you can set the delivery time depends on your ChatGPT plan. It won't reach into your Sheet automatically the way this build does, so you'd still paste in the week's numbers yourself.

What to Do Next

• This week: Build the WeeklyInput and Digest tabs and run the script manually a few times until the summary reads the way you want.
• This month: Add the time-driven trigger so it runs unattended, and add the email confirmation line so you'll notice if it stops.
• Advanced: Extend the script to also flag any property whose net cash flow has dropped for two weeks running, so the digest calls out a trend, not just a snapshot.

Advanced guide for real estate investor professionals. These techniques use more sophisticated AI features that may require paid subscriptions.