I wanted two things that sounded simple: to log an expense from my phone in a few seconds, and to receive a useful financial analysis once a week without opening five apps or leaving my laptop running.

In practice, that became a small integration system. Lunch Money is the source of truth, an iOS Shortcut is the fast input layer, Google Apps Script exports the data to Google Sheets in the cloud, and a ChatGPT Scheduled Task reads the spreadsheet, analyses the latest week, and sends the same report through Gmail.

This article documents the implementation we actually built, including the parts we initially got wrong: why exactly 1,000 transactions were imported, why new rows ended up at the bottom, why a category name is not enough for the API request, and why a v2 request-body example can become outdated only a few versions later.

Lunch Money
     ↑
iOS Shortcut — fast transaction entry
     ↓
Lunch Money v2 API
     ↓
Google Apps Script — daily cloud sync
     ↓
Google Sheet "Lunch Money Data" in Drive
     ↓
ChatGPT Cloud Scheduled Task
     ↓
Weekly analysis in ChatGPT + Gmail

This is neither a banking product nor an accounting system. It is a personal layer for faster data entry, better access to my own data, and regular analysis.

What you need

  • a Lunch Money account and an API access token;
  • an iPhone with the Shortcuts app;
  • a Google account with Google Sheets and Apps Script;
  • a ChatGPT account with Scheduled Tasks and the Google Drive and Gmail apps available;
  • 30–60 minutes for the initial setup, plus some patience for the peculiarities of Shortcuts.

One clarification before we start: this setup uses Google Sheets, not Google Docs. The Apps Script project is attached to a specific spreadsheet and runs in Google Cloud. Once the setup is complete, neither the Mac nor the phone participates in the daily sync or the weekly analysis. I only need the phone when I add a new transaction through the Shortcut.

Step 1: Keep Lunch Money as the source of truth

Before building the automation, I stopped thinking about two-way synchronisation. New transactions are created in Lunch Money, while the Google Sheet is a readable cloud mirror used for analysis. If I edit an old transaction in Lunch Money, the next sync updates the spreadsheet. If I delete it, it disappears from the mirror as well.

In Lunch Money, open Settings → Developers and create an access token. Do not email it, paste it into an article or screenshot, or leave it inside a publicly shared Shortcut. API v2 uses Bearer authentication and this base URL:

https://api.lunchmoney.dev/v2

Authorization: Bearer YOUR_ACCESS_TOKEN

The current Lunch Money v2 overview explains authentication, strict request validation, and an important detail: v2 transaction responses are “non-hydrated.” They include category_id, manual_account_id, plaid_account_id, and tag_ids, but not every human-readable name. That is why the spreadsheet later gets separate tabs for categories and accounts.

There is another detail that can invert the entire analysis: in transaction objects, a positive amount is an expense and a negative amount is income. The “Show Debits as Negative” setting in the interface does not change the API behaviour. Lunch Money documents this in Amounts & Balances.

Step 2: Build the iOS Shortcut for fast transaction entry

This was the hardest part. Not because the HTTP request is complicated, but because Shortcuts mixes Magic Variables, dictionary values, lists, and automatically inferred content types. If one field is interpreted as a File instead of Text, an If action starts offering conditions such as File Size, and a simple comparison suddenly looks much more mysterious than it is.

The finished Shortcut follows this sequence:

Ask for Number → amount
Ask for Text → payee
Current Date → yyyy-MM-dd
GET /v2/categories
Get "categories"
Repeat → collect category names
Choose from List
Repeat → find the ID for the selected name
POST /v2/transactions
Show Notification

2.1. Amount, payee, and date

  1. Create a new Shortcut, for example 💸 Add Expense.
  2. Add Ask for Input with the prompt Amount and Input Type Number.
  3. Immediately after it, add Set Variable and name the variable amount.
  4. Add a second Ask for Input with the prompt Where / Payee and Input Type Text.
  5. Add Set Variable after it and name the variable payee.
  6. Add Current Date.
  7. Add Format Date → Custom → yyyy-MM-dd.
  8. Save the result with Set Variable as transactionDate.

The explicit Set Variable actions are not strictly required, but they prevent a confusing Shortcuts behaviour where two JSON keys point to the same Magic Variable and renaming one appears to rename the other as well.

2.2. Load the categories dynamically

Do not hard-code category IDs. The Shortcut can fetch the current assignable categories every time it runs:

GET https://api.lunchmoney.dev/v2/categories?format=flattened&is_group=false

Authorization: Bearer YOUR_ACCESS_TOKEN
Accept: application/json

In Shortcuts:

  1. Add Get Contents of URL.
  2. Paste the URL above.
  3. Set Method to GET.
  4. Under Headers, add Authorization with the value Bearer YOUR_ACCESS_TOKEN.
  5. After the request, add Get Dictionary Value with the key categories.
  6. Save the returned list with Set Variable as categories.

The current v2 endpoint returns nested categories by default. The combination format=flattened&is_group=false gives us a flat list containing only categories that can actually be assigned to a transaction. The official v1 → v2 migration guide covers this difference as well.

2.3. Display a real category picker

Add Repeat with Each and use categories as its input. Inside the Repeat block, add:

  1. Get Dictionary Value;
  2. Key: name;
  3. Dictionary: Repeat Item.

You do not need to append the result to another list manually. After End Repeat, Shortcuts automatically creates Repeat Results from all the returned names. Add Choose from List, pass it Repeat Results, and save the chosen name as selectedCategoryName.

This is Apple’s documented pattern for using Repeat with Each on a JSON list.

2.4. Convert the selected name back to category_id

Lunch Money does not accept a category name when creating a transaction. It needs an integer category_id. We therefore run a second loop over the original categories list:

  1. Add Repeat with Each with categories as the input.
  2. Inside it, add Get Dictionary Value → key name → Dictionary Repeat Item.
  3. Add Set Variable → currentCategoryName.
  4. Tap the variable token and set Type to Text if Shortcuts has interpreted it as a File.
  5. Add If currentCategoryName is selectedCategoryName.
  6. Inside the If block, add Get Dictionary Value → key id → Dictionary Repeat Item.
  7. Add Set Variable → categoryID.
  8. Close the blocks with End If and End Repeat.
Repeat with each item in categories
    Get Value for "name" in Repeat Item
    Set variable currentCategoryName

    If currentCategoryName is selectedCategoryName
        Get Value for "id" in Repeat Item
        Set variable categoryID
    End If
End Repeat

If the If action shows File Size, do not add a second condition. Delete the If action, set currentCategoryName to Text, and create the comparison again. Apple explains JSON dictionaries and rich list choices in Using Dictionaries in Shortcuts.

2.5. Build the current v2 POST body

There is an important version-dependent detail here. In an early v2 iteration, we sent a single transaction object directly as the body. The current official v2 examples and the generated Lunch Money SDK use an outer transactions array. For a reproducible new installation, use the current shape:

{
  "transactions": [
    {
      "date": "2026-08-23",
      "payee": "Example shop",
      "amount": 23.45,
      "currency": "eur",
      "category_id": 12345
    }
  ],
  "apply_rules": true
}

In Shortcuts, add a Dictionary containing:

  • date → transactionDate;
  • payee → payee;
  • amount → amount as Number;
  • currency → eur, or your actual ISO 4217 currency;
  • category_id → categoryID as Number.

After the Dictionary, add a List containing one item: that Dictionary. Then add the final Get Contents of URL action:

  • URL: https://api.lunchmoney.dev/v2/transactions;
  • Method: POST;
  • Header Authorization: Bearer YOUR_ACCESS_TOKEN;
  • Header Content-Type: application/json;
  • Request Body: JSON;
  • transactions → the Magic Variable produced by the List;
  • apply_rules → Boolean true.

Finally, add Show Notification with text such as ✅ Transaction added. For the first test, use an amount of 0.01, check the result in Lunch Money, and then delete the test transaction.

Security trade-off: iOS Shortcuts has no equivalent of Script Properties. The token remains inside the Shortcut on the phone. Do not share the Shortcut, do not expose the token in screenshots, and revoke it if either the device or the Shortcut is compromised.

Step 3: Create the Google Sheet and Script Property

  1. In Google Drive, create a spreadsheet named Lunch Money Data.
  2. Open Extensions → Apps Script.
  3. Open Project Settings → Script Properties.
  4. Add a property named LUNCH_MONEY_TOKEN.
  5. Paste the Lunch Money access token as its value.

The token never appears in the code and is not written to the spreadsheet. Apps Script reads it through PropertiesService.getScriptProperties(). Google’s official Properties Service documentation explains this mechanism.

The script creates four tabs:

  • Transactions — transactions enriched with category and account names;
  • Categories — the local id → name lookup;
  • Accounts — manual and Plaid accounts;
  • Sync Status — SUCCESS/ERROR, timestamp, row counts, and an error message.

Step 4: The complete Google Apps Script

This is the production version I use as the cloud mirror. It:

  • walks through every page using limit, offset, and has_more;
  • uses the maximum page size of 2,000;
  • loads categories and accounts separately;
  • enriches numeric IDs with readable names;
  • sorts by date descending and then by ID descending;
  • writes the entire mirror in a single batch operation;
  • uses a Script Lock so that two triggers cannot overlap;
  • leaves a clear status for the Scheduled Task.

Our first naive request returned 1,000 transactions, and the spreadsheet contained exactly 1,001 rows: 1,000 records plus the header. That is the default page size, not missing history. The current Lunch Money pagination documentation specifies a default of 1,000, a maximum of 2,000, and the need to check has_more.

const LM_BASE_URL = 'https://api.lunchmoney.dev/v2';

const SHEETS = {
  TRANSACTIONS: 'Transactions',
  CATEGORIES: 'Categories',
  ACCOUNTS: 'Accounts',
  STATUS: 'Sync Status',
};


/**
 * Main entry point for the time-driven trigger.
 *
 * 1. Downloads categories.
 * 2. Downloads manual and Plaid accounts.
 * 3. Downloads every transaction with pagination.
 * 4. Enriches IDs with readable names.
 * 5. Rebuilds the Transactions body in one batch, newest first.
 * 6. Writes a machine-readable sync status.
 */
function syncLunchMoney() {
  const startedAt = new Date();
  const lock = LockService.getScriptLock();

  if (!lock.tryLock(30000)) {
    writeSyncStatus_({
      status: 'ERROR',
      startedAt,
      finishedAt: new Date(),
      apiTransactions: '',
      inserted: '',
      updated: '',
      totalRows: '',
      message: 'Another Lunch Money sync is already running.'
    });
    return;
  }

  try {
    const token = getLunchMoneyToken_();

    const categories = fetchCategories_(token);
    const accounts = fetchAccounts_(token);

    writeCategories_(categories);
    writeAccounts_(accounts);

    const categoryMap = buildCategoryMap_(categories);
    const accountMap = buildAccountMap_(accounts);

    const transactions = fetchAllTransactions_(token);

    const result = writeTransactionsMirror_(
      transactions,
      categoryMap,
      accountMap
    );

    writeSyncStatus_({
      status: 'SUCCESS',
      startedAt,
      finishedAt: new Date(),
      apiTransactions: transactions.length,
      inserted: result.inserted,
      updated: result.updated,
      totalRows: result.totalRows,
      message: ''
    });

    console.log(
      `Lunch Money sync complete. ` +
      `${transactions.length} fetched, ` +
      `${result.inserted} new IDs, ` +
      `${result.updated} existing IDs.`
    );

  } catch (error) {
    writeSyncStatus_({
      status: 'ERROR',
      startedAt,
      finishedAt: new Date(),
      apiTransactions: '',
      inserted: '',
      updated: '',
      totalRows: '',
      message: error.stack || String(error)
    });

    throw error;

  } finally {
    lock.releaseLock();
  }
}


/* =========================================================
 * AUTH / HTTP
 * ========================================================= */

function getLunchMoneyToken_() {
  const token = PropertiesService
    .getScriptProperties()
    .getProperty('LUNCH_MONEY_TOKEN');

  if (!token) {
    throw new Error(
      'Missing LUNCH_MONEY_TOKEN in Script Properties.'
    );
  }

  return token;
}


function fetchJson_(url, token) {
  const response = UrlFetchApp.fetch(url, {
    method: 'get',
    headers: {
      Authorization: `Bearer ${token}`,
      Accept: 'application/json'
    },
    muteHttpExceptions: true
  });

  const code = response.getResponseCode();
  const body = response.getContentText();

  if (code < 200 || code >= 300) {
    throw new Error(
      `Lunch Money API ${code}\nURL: ${url}\n${body}`
    );
  }

  return JSON.parse(body);
}


/* =========================================================
 * TRANSACTIONS
 * ========================================================= */

function fetchAllTransactions_(token) {
  const all = [];
  const limit = 2000;

  let offset = 0;
  let hasMore = true;

  while (hasMore) {
    const url =
      `${LM_BASE_URL}/transactions` +
      `?limit=${limit}` +
      `&offset=${offset}`;

    const data = fetchJson_(url, token);
    const transactions = data.transactions || [];

    all.push(...transactions);

    console.log(
      `Transactions: ${transactions.length} ` +
      `at offset ${offset}; total ${all.length}`
    );

    hasMore = data.has_more === true;
    offset += limit;

    if (hasMore) {
      Utilities.sleep(100);
    }
  }

  return all;
}


function writeTransactionsMirror_(
  transactions,
  categoryMap,
  accountMap
) {
  const sheet = getOrCreateSheet_(SHEETS.TRANSACTIONS);

  const headers = [
    'id',
    'date',
    'amount',
    'currency',
    'payee',

    'category_id',
    'category',

    'account_type',
    'account_id',
    'account',
    'institution',

    'notes',
    'tag_ids',

    'status',
    'is_pending',

    'recurring_id',
    'split_parent_id',
    'group_parent_id',

    'created_at',
    'updated_at'
  ];

  const existingIds = getExistingIds_(sheet);

  transactions.sort((a, b) => {
    const dateCompare = String(b.date || '')
      .localeCompare(String(a.date || ''));

    if (dateCompare !== 0) {
      return dateCompare;
    }

    return Number(b.id || 0) - Number(a.id || 0);
  });

  const rows = transactions.map(t =>
    transactionToRow_(t, categoryMap, accountMap)
  );

  const inserted = transactions.reduce(
    (count, t) =>
      count + (existingIds.has(String(t.id)) ? 0 : 1),
    0
  );

  const updated = transactions.length - inserted;

  replaceSheet_(sheet, headers, rows);

  return {
    inserted,
    updated,
    totalRows: rows.length
  };
}


function getExistingIds_(sheet) {
  const ids = new Set();
  const lastRow = sheet.getLastRow();

  if (lastRow < 2) {
    return ids;
  }

  sheet
    .getRange(2, 1, lastRow - 1, 1)
    .getValues()
    .forEach(row => {
      if (row[0] !== '' && row[0] != null) {
        ids.add(String(row[0]));
      }
    });

  return ids;
}


function transactionToRow_(
  t,
  categoryMap,
  accountMap
) {
  const categoryName =
    t.category_id != null
      ? categoryMap.get(String(t.category_id)) || ''
      : '';

  let accountType = '';
  let accountId = '';
  let accountName = '';
  let institution = '';

  if (t.manual_account_id != null) {
    accountType = 'manual';
    accountId = t.manual_account_id;

    const account = accountMap.get(
      `manual:${t.manual_account_id}`
    );

    if (account) {
      accountName = account.name;
      institution = account.institution;
    }

  } else if (t.plaid_account_id != null) {
    accountType = 'plaid';
    accountId = t.plaid_account_id;

    const account = accountMap.get(
      `plaid:${t.plaid_account_id}`
    );

    if (account) {
      accountName = account.name;
      institution = account.institution;
    }
  }

  return [
    t.id ?? '',
    t.date ?? '',
    t.amount ?? '',
    t.currency ?? '',
    t.payee ?? '',

    t.category_id ?? '',
    categoryName,

    accountType,
    accountId,
    accountName,
    institution,

    t.notes ?? '',
    Array.isArray(t.tag_ids)
      ? t.tag_ids.join(',')
      : '',

    t.status ?? '',
    t.is_pending ?? '',

    t.recurring_id ?? '',
    t.split_parent_id ?? '',
    t.group_parent_id ?? '',

    t.created_at ?? '',
    t.updated_at ?? ''
  ];
}


/* =========================================================
 * CATEGORIES
 * ========================================================= */

function fetchCategories_(token) {
  const url =
    `${LM_BASE_URL}/categories` +
    `?format=flattened&is_group=false`;

  const data = fetchJson_(url, token);

  return data.categories || [];
}


function buildCategoryMap_(categories) {
  const map = new Map();

  categories.forEach(category => {
    map.set(
      String(category.id),
      category.name || ''
    );
  });

  return map;
}


function writeCategories_(categories) {
  const sheet = getOrCreateSheet_(SHEETS.CATEGORIES);

  const headers = [
    'id',
    'name',
    'group_name',
    'is_income',
    'exclude_from_budget',
    'exclude_from_totals',
    'archived_at',
    'updated_at'
  ];

  const rows = categories.map(c => [
    c.id ?? '',
    c.name ?? '',
    c.group_name ?? '',
    c.is_income ?? '',
    c.exclude_from_budget ?? '',
    c.exclude_from_totals ?? '',
    c.archived_at ?? '',
    c.updated_at ?? ''
  ]);

  replaceSheet_(sheet, headers, rows);
}


/* =========================================================
 * ACCOUNTS
 * ========================================================= */

function fetchAccounts_(token) {
  const manualData = fetchJson_(
    `${LM_BASE_URL}/manual_accounts`,
    token
  );

  const plaidData = fetchJson_(
    `${LM_BASE_URL}/plaid_accounts`,
    token
  );

  const manualAccounts =
    manualData.manual_accounts || [];

  const plaidAccounts =
    plaidData.plaid_accounts || [];

  const manual = manualAccounts.map(a => ({
    source: 'manual',
    id: a.id,
    name: a.display_name || a.name || '',
    institution: a.institution_name || '',
    type: a.type || '',
    subtype: a.subtype || '',
    currency: a.currency || '',
    status: a.status || '',
    updated_at: a.updated_at || ''
  }));

  const plaid = plaidAccounts.map(a => ({
    source: 'plaid',
    id: a.id,
    name: a.display_name || a.name || '',
    institution: a.institution_name || '',
    type: a.type || '',
    subtype: a.subtype || '',
    currency: a.currency || '',
    status: a.status || '',
    updated_at: a.updated_at || ''
  }));

  return [...manual, ...plaid];
}


function buildAccountMap_(accounts) {
  const map = new Map();

  accounts.forEach(account => {
    const key = `${account.source}:${account.id}`;

    map.set(key, {
      name: account.name || '',
      institution: account.institution || ''
    });
  });

  return map;
}


function writeAccounts_(accounts) {
  const sheet = getOrCreateSheet_(SHEETS.ACCOUNTS);

  const headers = [
    'source',
    'id',
    'name',
    'institution',
    'type',
    'subtype',
    'currency',
    'status',
    'updated_at'
  ];

  const rows = accounts.map(a => [
    a.source,
    a.id,
    a.name,
    a.institution,
    a.type,
    a.subtype,
    a.currency,
    a.status,
    a.updated_at
  ]);

  replaceSheet_(sheet, headers, rows);
}


/* =========================================================
 * SYNC STATUS
 * ========================================================= */

function writeSyncStatus_(info) {
  const sheet = getOrCreateSheet_(SHEETS.STATUS);

  const rows = [
    ['Property', 'Value'],
    ['Status', info.status],
    ['Started', info.startedAt],
    ['Finished', info.finishedAt],
    ['API transactions', info.apiTransactions],
    ['Inserted', info.inserted],
    ['Updated', info.updated],
    ['Rows in sheet', info.totalRows],
    ['Message', info.message]
  ];

  sheet.clearContents();

  sheet
    .getRange(1, 1, rows.length, 2)
    .setValues(rows);

  sheet.setFrozenRows(1);
}


/* =========================================================
 * SHEET HELPERS
 * ========================================================= */

function getOrCreateSheet_(name) {
  const spreadsheet =
    SpreadsheetApp.getActiveSpreadsheet();

  let sheet = spreadsheet.getSheetByName(name);

  if (!sheet) {
    sheet = spreadsheet.insertSheet(name);
  }

  return sheet;
}


function replaceSheet_(sheet, headers, rows) {
  sheet.clearContents();

  sheet
    .getRange(1, 1, 1, headers.length)
    .setValues([headers]);

  if (rows.length > 0) {
    sheet
      .getRange(2, 1, rows.length, headers.length)
      .setValues(rows);
  }

  sheet.setFrozenRows(1);
}

Step 5: Run the first sync manually

  1. In Apps Script, select the syncLunchMoney function.
  2. Click Run.
  3. Approve the requested Google permissions.
  4. Wait for the execution to finish.
  5. Return to the spreadsheet and open Sync Status.

You should see:

Status              SUCCESS
API transactions    [expected count]
Rows in sheet       [the same count]
Message             [empty]

Then make three checks:

  • the newest dates appear at the top of Transactions;
  • the category and account columns contain names, not only IDs;
  • there are no duplicate transaction IDs.

Our first row-by-row upsert placed new transactions at the bottom. We could have sorted the sheet after every append, but with thousands of rows that design also leaves thousands of individual update operations. The final version sorts the entire in-memory array first and writes the mirror in one batch. For a few thousand personal transactions, this is simpler and faster, and it reflects deletions as well as edits.

Step 6: Add a daily time-driven trigger

In Apps Script:

  1. Open Triggers.
  2. Click Add Trigger.
  3. Function: syncLunchMoney.
  4. Deployment: Head.
  5. Event source: Time-driven.
  6. Type: Day timer.
  7. Choose an evening or overnight window before the weekly analysis runs.

Google describes these as installable time-driven triggers. They run in Google’s infrastructure and do not depend on a laptop being switched on. There is one important caveat: the chosen time window may be slightly randomised. Do not treat it as a cron job accurate to the minute. See Google’s Installable Triggers documentation.

Step 7: Connect Google Drive and Gmail to ChatGPT

In ChatGPT, open Apps and verify that Google Drive and Gmail are connected to the correct Google account. Do not give the Scheduled Task the Lunch Money token. It only needs to read the finished spreadsheet from Drive.

There are two separate delivery mechanisms here:

  • Task notification/email setting — a platform notification that the task ran;
  • Gmail send action — an actual email containing the complete financial analysis.

This integration needs the second one. The task must explicitly use Gmail and report if sending the message was not possible.

According to the current OpenAI documentation for Scheduled Tasks, web tasks run in the background and can use connected tools available to that particular chat. They cannot see a local folder on a Mac. Google Drive is therefore the stable cloud hand-off point.

Step 8: Create the weekly Scheduled Task

Before creating a schedule, run the prompt once in a normal chat. Confirm that ChatGPT finds the correct spreadsheet, reads Sync Status, and does not mix investments with normal consumption.

Here is a public template. Replace the placeholder email address inside your private task, not in a public article:

Every week, create a financial analysis of the Google Sheet
"Lunch Money Data" from the connected Google Drive.

Use only the data in these tabs:
- Sync Status
- Transactions
- Categories
- Accounts

Before the analysis:
1. Verify that Sync Status is SUCCESS.
2. Check the Finished timestamp. If the sync is stale, missing, or ERROR,
   do not silently use old data. State clearly that the analysis is not
   reliable, explain why, and do not send a misleading report.
3. Use the last 7 completed calendar days through yesterday.
4. Compare them with the previous 28 days, normalised to a weekly average.

Include:
- total outflow and the change versus the four-week average;
- spending by category and the categories with the largest increase;
- unusual or large transactions;
- recurring payments and possible duplicates;
- discretionary spending;
- a short forecast through the end of the month;
- three concrete observations or actions.

Rules:
- In Lunch Money v2, a positive transaction amount is an expense
  and a negative amount is income.
- Do not treat transfers as consumption.
- Keep Investment separate from actual consumption and exclude it
  from the lifestyle-spending forecast.
- Do not invent category or account names; use the spreadsheet data.
- Flag missing or ambiguous data.
- Write the final report in practical, concise Bulgarian, without
  financial-marketing language.

Publish a summary in the task chat and send the same full text through Gmail
to [email protected] with the subject:
"Weekly financial analysis — [start date]–[end date]".

If the Gmail action is unavailable or sending fails, state explicitly
that the email was not sent and explain why.

I scheduled the task for Monday morning in the Europe/Sofia time zone, after the daily Apps Script trigger had enough time to update the spreadsheet.

Step 9: Test the entire chain

Do not wait for the first Monday. Run a manual end-to-end test and verify that:

  1. the Shortcut creates one test transaction with the correct date, amount, payee, and category;
  2. syncLunchMoney places it at the top of Transactions;
  3. Sync Status is SUCCESS and the row count matches;
  4. ChatGPT finds the correct spreadsheet in Drive;
  5. the analysis uses the last seven completed days;
  6. Investment is separated from consumption;
  7. Gmail receives the same complete text, not merely a platform notification.

In our actual test, the final path was:

Google Drive → Lunch Money Data → analysis → Gmail

The test matters for another reason: “the task exists” does not automatically mean “the result will arrive by email.” The Gmail action and task notifications are separate mechanisms.

What broke along the way

The spreadsheet contained exactly 1,001 rows

No data had disappeared. The first API request returned the default 1,000 transactions, and the first row in the spreadsheet was the header. The fix was real pagination using has_more, not increasing an arbitrary limit and hoping for the best.

New transactions appeared at the bottom

The API response order was not the main problem. Our row-by-row upsert appended previously unseen IDs at the bottom. The final mirror sorts the complete array by date descending and ID descending before the batch write.

Shortcuts decided the category name was a File

The Magic Variable had been inferred with the wrong content type, so the If action offered File Size. The fix was an intermediate Set Variable currentCategoryName, Type set manually to Text, and one simple comparison.

The selected name was not a category_id

Choose from List returned “Groceries,” but the POST endpoint expects an integer ID. The second Repeat block looks up the selected name in the original JSON and stores its corresponding id.

The v2 request body changed

Lunch Money v2 is still evolving. Our early setup sent a single object directly, while the current official schema and SDK examples use { "transactions": [...] }. If strict validation returns a 400 error, check the current v2 documentation before blindly “fixing” the Shortcut.

Troubleshooting checklist

  • 401 Unauthorized: check the Bearer prefix and confirm that the token is still active.
  • 400 Bad Request on POST: check the transactions wrapper, Number/Text types, and unexpected keys.
  • The categories are groups: use ?format=flattened&is_group=false.
  • Repeat Item is missing: confirm that the action is inside Repeat and that Repeat reads categories, not an empty Dictionary.
  • Only 1,000 transactions: check has_more and offset += limit.
  • New rows are at the bottom: use the final mirror code, or sort the complete data range after appending.
  • Apps Script shows ERROR: inspect Message in Sync Status and the execution log.
  • The Scheduled Task cannot see the spreadsheet: check its name, the Google account, and Google Drive availability in the task chat.
  • No email arrives: check the Gmail app and confirm that the prompt explicitly requires a Gmail send action.
  • The forecast is absurd: separate income, transfers, and investments from actual consumption.

Security and limitations

The Lunch Money token provides access to personal financial data. In Apps Script, it lives in Script Properties. In the Shortcut, it is stored locally inside an action, which is the weaker security boundary. Do not publish a Shortcut export while the token is still embedded.

ChatGPT never receives the Lunch Money token. It sees the spreadsheet mirror through the Google Drive app. That is a cleaner separation of responsibilities, but the spreadsheet still contains sensitive information: payees, amounts, categories, and dates. Do not make it public or share it through an unrestricted link.

The other practical limitations are:

  • the v2 API can change schema details, so watch the migration guide and changelog;
  • an Apps Script day timer does not promise minute-level precision;
  • Scheduled Tasks can only use tools and apps available to the relevant account and workspace;
  • AI analysis is not accounting, tax, or investment advice;
  • poor categorisation in Lunch Money leads to poor analysis, no matter how good the prompt is.

The reusable lesson

The most useful part of this project is not the particular Shortcut or financial prompt. It is the architecture:

fast human input
→ one source of truth
→ predictable cloud mirror
→ freshness/status gate
→ periodic AI analysis
→ separate delivery channel

When every stage has one clear responsibility, the system becomes easy to test. If there is no transaction, the problem is in the Shortcut or the API POST. If there is no row in the spreadsheet, the problem is in the Apps Script sync. If the analysis is stale, Sync Status tells us. If there is no email, the issue is Gmail delivery, not the financial logic.

That predictability is what turned the integration from an entertaining experiment into a personal tool I genuinely use.