Investment Balances to Budget Sheet Without Transactions

[m00]

Overview

I have 2 Tiller sheets, one for Investments and one for Budgeting. I would like to have the balances of my Investments sheet show up in my Budgeting sheet, but do not want to add them as accounts, and get all of their many transactions cluttering up my main Budgeting sheet.

So this is a script to get just those balances imported daily into my Budgeting sheet.

This was inspired by a question from @stevation in this thread.

Also, @brettanicus made a template/formula based solution here. That would be a good fit if you don’t want to mess with Apps Script, and don’t mind a little manual work.

Installation & Setup

If this is your first time using Apps Scripts, check out the Beginner Installation & Setup Tips

It is a Google Apps Script function.

1. Copy and paste the code into any *.gs file within ‘Apps Script’ from the Extension menu in your Budget sheet.
2. Set Variables
3. Save
4. Run the investmentBalancesToBudgetSheet() function
5. Set a Trigger to run on your desired frequency

Set Variables

There are a couple of things to update in the script so that it will work for your sheets.

Set a Trigger

Usage

This runs automatically at your trigger frequency. No manual actions needed.

Permissions

Is it ok for others to copy, use, and modify your workflow?
Yup.

The Code

Code is here on GitHub.

Also a static version:

function investmentBalancesToBudgetSheet() {
  // ========================================
  // Must Review and Update These Variables
  // ========================================
  var sourceSpreadsheetId = "aY2Lrvvq8BYrxjyD8EhltKpoQB6bO1NMRkgmAGaTjHRW"; // Change this to your source sheet's (Investment Sheet) ID
  var accountColumnIndex = 3; // Column D (3) is "Account" column on my sheet. Update so the number is for the Account column on your sheet. (zero-based index: A=0, B=1, C=2, D=3, etc.)
  
  // ==============================================
  // Less likely to need changes to these variables
  // ==============================================
  var sourceSheetName = "Balance History"; // Source spreadsheet (Investment Sheet)
  var targetSheetName = "Balance History"; // Destination sheet (Budget Sheet)

  // ==============================================
  // Script Logic - No changes needed below this point
  // ==============================================
  var sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);
  var sourceSheet = sourceSpreadsheet.getSheetByName(sourceSheetName);
  var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheetName);

  if (!sourceSheet || !targetSheet) {
    Logger.log("One or both sheets not found!");
    return;
  }

  var data = sourceSheet.getRange(2, 1, 500, sourceSheet.getLastColumn()).getValues(); // Read first 500 rows (starting from row 2)
  var uniqueAccounts = new Set();
  var filteredRows = [];

  // Iterate through rows and store unique accounts
  for (var i = 0; i < data.length; i++) {
    var accountName = data[i][accountColumnIndex]; // Use configurable column for "Account"

    if (!uniqueAccounts.has(accountName)) {
      uniqueAccounts.add(accountName);
      filteredRows.push(data[i]);
    }
  }

  var numNewRows = filteredRows.length;
  if (numNewRows === 0) {
    Logger.log("No new unique rows to insert.");
    return;
  }

  // Step 1: Insert new rows between row 2 and 3 (some conditional formatting or array formulas may not like row 2)
  targetSheet.insertRowsBefore(3, numNewRows);

  // Step 2: Copy row 2 and paste it to the last new empty row
  var row2Values = targetSheet.getRange(2, 1, 1, targetSheet.getLastColumn()).getValues();
  targetSheet.getRange(2 + numNewRows, 1, 1, row2Values[0].length).setValues(row2Values);

  // Step 3: Paste new unique account data into row 2
  targetSheet.getRange(2, 1, numNewRows, filteredRows[0].length).setValues(filteredRows);

  Logger.log("Successfully inserted " + numNewRows);
}

Notes

Make sure both Balance History sheets have the same columns in the same order.

“How do I get the ID for my Investment sheet?”
It is in the URL bar when you are in that sheet:

Beginner Installation & Setup Tips

<--- Click the Arrow for Install & Setup Tips

1. In Budget sheet, we are going to “pull” data from your Investment sheet.

2. So from the Budget sheet, open the Extensions menu, and select Apps Script.
   

   

3. In either the default Code.gs file, or one you create, add all the code from above.

4. Set Variables
   a. Make sure to get and set the ID for your Source sheet (Investment Sheet). It should go between the set of double quotes after the sourceSpreadsheetId =
   b. Review what column is your ‘Accounts’ column, and set the accountColumnIndex = accordingly.

5. Save (The button may say Save project to Drive)

6. Run the investmentBalancesToBudgetSheet function:
   

   

7. If this is your first time using Apps Script, Google may pop up a window for authorization. There are many variations of how this process looks, but here are some examples.
   a. It may look like this if your browser blocks it: (You can allow the Google popup)
   

   
   
   b. It may take you straight to this:
   
   
   
   c. Likely you will have to approve your ‘app’ that you just created. It will have your own email address in the “developer” section. Takes 2 clicks:
   
   

8. Check that the balances were added to your Budget sheet Balance History tab.

9. Set a Trigger to run on your desired frequency.

10. Close the Apps Script tab.

[brettanicus]

I’m having a few bumps installing the solution, which I think are solved by adding some steps to your instructions (for users like me who are not experienced with scripts).

Since we’re dealing with two spreadsheets, source and target (or Investment and Budget), you should specify that the script should be copied to the target sheet side (Budget) if that’s correct. I copied it to my Investment sheet and was wondering why I’m getting duplicate balance history records and nothing going to my Budget sheet

Also, before you can set up the trigger, the user needs to save the project to drive first, because the new function doesn’t show up in the “Choose wich function to run” dropdown until it’s saved.

But now I’m getting the following error when trying to run the script the first time:

[m00]

Thanks for the feedback, and definitely a moment when you see a bunch of dupes in your Investment Balance History.

I made some changes:

1. Updated the code to make the variable setting easier
2. Linked some sections in the first post to find what you need for setup
3. Created a Beginner guide for install and setup

For your exact issue, I could not replicate on a new account with new sheets, so you may want to close your browser, and then start fresh with a new .gs file

If you don’t catch the popup for authorization right away, it can do weird things.

[brettanicus]

It’s working great now. I somehow threw a wrench in the cogs when I changed the spreadsheet ID because none of the normal fixes worked. I resorted to deleting the Project and starting with a clean slate, and everything went as planned from there. Thanks for creating this!