Google Sheets, Apps Script & Shortcuts: A Free Expense Tracking System with iOS Widget

For a long time, I wanted a way to easily track my expenses and income and be able to easily see monthly/weekly statistics on my laptop, tablet, and phone. I experimented with many apps and tried various ways to keep track of these, but I always remained unsatisfied. These methods weren’t good enough for various reasons:

• I had to put in too much effort to input expenses/income.
• I couldn’t easily see my statistics at a glance on my phone.
• They lacked certain features.
• …and the list goes on.

After years of trying different approaches and finally reaching a list of expense categories that finally gave me peace of mind (some of you will know this frustration!), I was fortunate enough to find the missing pieces of the puzzle online through the work of other people. I combined their ideas to create a way to track my finances.

So, it was thanks to these awesome people that I managed to put together this system, adapting their work to my own vision:

• This girl on YouTube for the Google Sheet.
• This guy on GitHub for the widget script.
• This user on Reddit for the Google Apps Script part and the Apple Shortcut for inputting expenses.
• The guy who created the Scriptable app which lets you create the iOS/iPadOS custom widgets (and so much more).

I will guide you further step by step how to set this up and all the tools that are required.

Requirements/Tools

• iPhone/iPad
• Google account in order to use Google Drive (Sheets), Google Forms, Google Apps Script
• Scriptable app (only for iOS/iPadOS)
• Apple Shortcuts app

The Google Part

You need to set up a Google Sheet by following this guide (this is the most time consuming part):

https://www.youtube.com/watch?v=ZEoqUqm14n8

For the Script Part

You need to use a Google API, specifically Google Sheets API v4:

1. Go to the Google Cloud Console.
2. Create a new project or select an existing one.
3. Navigate to the APIs & Services section.
4. Enable the Google Sheets API for your project.
5. In the Credentials tab, create an API key (which will be used in the Scriptable code to create the widget).

Apart from this API key, you’ll also need the Sheet ID required by the script to target your specific Google Sheet. Open your sheet and the ID can be found in the browser link:

https://docs.google.com/spreadsheets/d/here_is_the_id/edit?gid=2058213571#gid=2058213571

Also, in order for the API call to work you need to go File → Share → Share With Others → General Access and choose “Anyone with the link” in your Google Sheet.

Google Apps Script

Of course, you could simply save the Google Form webpage to your iOS/iPadOS Home screen and use that to enter expenses/income. This will update the sheet just fine. However, I find it faster and easier to do it with an Apple Shortcut. For this to work, you also need to create a Google Apps Script.

1. Go to Apps Script. Tap ”+” icon to create a new project.
2. In the code.gs file add the following code:

const transactionSheet = SpreadsheetApp.openById("your_sheet_id").getSheetByName("Expenses");

function doGet(payload) {
  return addTransaction(payload);
}

function addTransaction(payload) {
  // Validate the required parameters
  const vendor = payload.parameter.vendor;
  const cost = payload.parameter.cost;
  const category = payload.parameter.category;

  if (!cost || !category) {
    let missingFields = [];
    if (!cost) missingFields.push("cost");
    if (!category) missingFields.push("category");

    return ContentService.createTextOutput("Error: Missing required fields - " + missingFields.join(", "));
  }

  // Set the timezone and formatting for Bucharest time
  const timeZone = "Europe/Bucharest";
  const now = new Date();
  const firstTimestamp = Utilities.formatDate(now, timeZone, "dd.MM.yyyy HH:mm:ss");
  const secondTimestamp = Utilities.formatDate(now, timeZone, "dd.MM.yyyy");

  // Try to append to the spreadsheet and catch any errors
  try {
    transactionSheet.appendRow([firstTimestamp, secondTimestamp, vendor, cost, category]);
    return ContentService.createTextOutput("Success!");
  } catch (error) {
    return ContentService.createTextOutput("Error: Could not append data to the spreadsheet. Details: " + error.message);
  }
}

I’ve set my timezone, so you definitely have to change that in the code. If you want more variables, adjust depending on how you’ve customized the Google Sheet.

The Scriptable Widget

Next, install the Scriptable app on your iOS/iPad device.

Open the app and tap the ”+” icon to create the script. Copy the code below and paste it there:

// Function to parse values from the sheet
function parseValue(valueStr) {
  let cleanStr = valueStr.replace(/[^\d.,]/g, "").trim();
  cleanStr = cleanStr.replace(/,00$/, "");
  return cleanStr;
}

// Replace with your desired currency
const modificatori = { currency: "RON" };

const colori = {
  header: Color.dynamic(new Color("#343946"), Color.white()),
  testi: Color.dynamic(new Color("#6e7276"), new Color("#ebebeb")),
  refresh: Color.dynamic(new Color("#1d1d1b"), Color.white()),
  bg: Color.dynamic(new Color("#fafafa"), new Color("#191919")),
};

const font = {
  header: new Font("Helvetica Bold", 23),
  testi: new Font("Helvetica Bold", 13.5),
  cifre: new Font("Helvetica Bold", 22),
  refresh: new Font("Helvetica Light", 12),
};

// Get current date, determine Romanian month name and year
const monthNames = [
  "ianuarie", "februarie", "martie", "aprilie", "mai", "iunie",
  "iulie", "august", "septembrie", "octombrie", "noiembrie", "decembrie",
];
let currentDate = new Date();
let currentMonthName = monthNames[currentDate.getMonth()];
let currentYear = currentDate.getFullYear();
let currentMonthYear = `${currentMonthName} ${currentYear}`;

// Replace with your actual sheet_id and apiKey
const sheet_id = "YOUR_SHEET_ID";
const apiKey = "YOUR_API_KEY";
const range = "'Monthly Overview'!A1:AA13";

let widget = await createWidget();
Script.setWidget(widget);
Script.complete();

async function createWidget() {
  let w = new ListWidget();
  w.backgroundColor = colori.bg;
  w.respectScreenScale = true;

  const endpoint = `https://sheets.googleapis.com/v4/spreadsheets/${sheet_id}/values/${encodeURIComponent(
    range
  )}?key=${apiKey}`;

  async function loadItems() {
    let req = new Request(endpoint);
    let corpo = await req.loadJSON();
    return corpo.values;
  }

  let json = await loadItems();
  const headers = json[0];

  const monthIndex = json.findIndex(
    (row) => row[0] && row[0].trim() === currentMonthYear
  );
  if (monthIndex === -1) {
    w.addText(`No data found for ${currentMonthYear}`);
    return w;
  }

  const monthRow = json[monthIndex];
  const totalExpIndex = headers.indexOf(" Total Expenditure ");
  let totalExpenditureValue = "-";
  if (totalExpIndex !== -1) {
    totalExpenditureValue = monthRow[totalExpIndex] ? monthRow[totalExpIndex].trim() : "-";
  }

  const title = w.addText(`Expenses ${currentMonthYear}`);
  title.font = font.header;
  title.textColor = colori.header;
  w.addSpacer(10);

  let results = [];
  for (let i = 1; i < headers.length; i++) {
    if (i === totalExpIndex) continue;
    let category = headers[i].trim();
    let valueStr = monthRow[i] ? monthRow[i].trim() : "-";

    if (valueStr !== "-" && valueStr.length > 0) {
      let numberVal = parseValue(valueStr);
      if (numberVal !== null && numberVal > 0) {
        results.push({ category: category, value: numberVal });
      }
    }
  }

  if (totalExpenditureValue === "-" || totalExpenditureValue.length === 0) {
    w.addText(`No total expenditure data found for ${currentMonthYear}`);
  } else {
    let parsedTotal = parseValue(totalExpenditureValue);
    if (parsedTotal === null) {
      w.addText(`Invalid total expenditure data for ${currentMonthYear}`);
    } else {
      let totalLine = w.addText(`Total: ${parsedTotal} ${modificatori.currency}`);
      totalLine.font = font.testi;
      totalLine.textColor = colori.testi;
      totalLine.centerAlignText();
      w.addSpacer(5);
    }
  }

  if (results.length === 0) {
    w.addText(`No expenditures found for ${currentMonthYear}`);
  } else {
    for (let item of results) {
      let line = w.addText(`${item.category} - ${item.value} ${modificatori.currency}`);
      line.font = font.testi;
      line.textColor = colori.testi;
      w.addSpacer(3);
    }
  }

  w.addSpacer();
  const l1 = w.addText(`Last fetched on: ${new Date().toLocaleString()}`);
  l1.font = font.refresh;
  l1.textColor = colori.refresh;
  l1.textOpacity = 0.9;
  l1.centerAlignText();

  var refreshDate = Date.now() + 1000 * 60 * 180; // 5 hours
  w.refreshAfterDate = new Date(refreshDate);
  return w;
}

You have to replace the placeholders in the code:

• YOUR_SHEET_ID with the actual ID of your sheet.
• YOUR_API_KEY with your actual API key.
• “range” variable with the correct cell range in your sheet.

Additionally, you can customize:

• Desired currency
• Months in your preferred language
• Preferred refresh interval for the widget
• Colors, fonts, and layout tweaks

After finishing the script:

1. Long-press to add a new widget to the home screen.
2. Select the Scriptable app and choose a widget size.
3. Add the widget.
4. Long-press on the widget and choose the script you just created.

The widget should refresh the displayed data every 5 hours, but you can modify this interval as needed.

For the Shortcut Part

This is the Apple Shortcut used for inputting expenses.

The link will take you to the shortcut in the Shortcuts app. You’ll need to edit it and add the required information from your Google Apps Script project. You’ll also likely need to change the expense categories to match your own.

Save the shortcut on your Home screen and run it. This should update the Expenses tab in your Google Sheet.

Widget Considerations

Please note that I’m using an iPhone 13 Pro Max, so the widget’s appearance might differ on other devices. The widget only displays expense categories with entries in the current month. If you have many categories, you might need to adjust the font size to fit them all.

---

This system has completely changed how I track my finances. The combination of quick input via Shortcuts and at-a-glance visibility through the widget means I actually stick with it. If you set this up, let me know how it works for you!