BudgetSheet Articles, News & Updates

<p><a href="https://developers.google.com/apps-script">Google Apps Script</a> is a pretty awesome tool for working with GSuite
programatically. It&#39;s the foundation of all the behind the scenes magic that powers
<a href="https://www.budgetsheet.net/">BudgetSheet</a>, a Google Sheets add-on I wrote and published for pulling real bank
transactions into your spreadsheet.</p>
<p>But as great as Google Apps Script is, sometimes <strong>working with spreadsheets to find and update specific rows of data can
be awkward and tedious. There is no direct built-in way to search for specific values in rows by headings</strong>. To do this
with the Google-supplied APIs, you have to keep track of row index numbers, column index numbers, and arrays of row data
while planning your updates. It&#39;s not fun code to write, and it&#39;s relatively error-prone, especially if you are deleting
rows, which causes the row index numbers to shift dynamically.</p>
<p>A few weeks ago, <a href="https://twitter.com/vlucas/status/1367288848573685762">I announced via Twitter</a> that I was writing a
Google Apps Script library to address this. The syntax looks like this:</p>
<p><img src="/img/posts/sheetquery/1.jpeg" alt="SheetQuery syntax for finding and updating records"></p>
<h2 id="querying-for-records">Querying for records:</h2>
<p>The <strong>getRows</strong> method will return an array of objects that are your spreadsheet rows mapped to column hedings.</p>
<script src="https://gist.github.com/vlucas/8a457daff1c5dda11561b41842036011.js"></script>

<h2 id="updating-records">Updating records:</h2>
<p>The <strong>updateRows</strong> method will update all matched rows in place. No row or column indices to worry about or track!</p>
<script src="https://gist.github.com/vlucas/6b302f6cce64890ce03d5d57e24687d0.js"></script>

<h2 id="deleting-records">Deleting records:</h2>
<p>The <strong>deleteRows</strong> method will delete the matching rows in your spreadsheet. Careful with this one!</p>
<script src="https://gist.github.com/vlucas/21f6300f3d059e44605af4ed9ee92575.js"></script>

<h2 id="install-and-use-sheetquery">Install and Use SheetQuery</h2>
<p>SheetQuery is available as a <a href="https://developers.google.com/apps-script/guides/libraries">Google Apps Script Library</a>.<br>Script ID: <code>1pbpOJxDDHVeVr6WQmR5TZKSqTsW4qwdIlcVKIM6UKYvswkivwHpPnHaO</code></p>
<p>If you build your scripts, you can <a href="https://www.npmjs.com/package/sheetquery">install SheetQuery via NPM</a>.</p>
<p>The full TypeScript source is available on the <a href="https://github.com/vlucas/sheetquery"><strong>SheetQuery GitHub Page</strong></a>. If
you are familiar with Google Apps Script, I would love your contributions.</p>


A simple and small Google Apps Script library for quickly and easily finding and updating records in Google Sheets with a familiar ORM-like syntax

SheetQuery: An ORM-Like Query Builder for Google Sheets and Google Apps Script

<p><a href="https://www.budgetsheet.net">BudgetSheet</a> now has user-specified category match patterns. The next time you open BudgetSheet and run an operation,
 your <strong>BSA_Categories</strong> sheet will get a new column named <strong>MatchPatterns</strong>.</p>
<h2 id="user-specified-transaction-category-matching">User-Specified Transaction Category Matching</h2>
<p>The <strong>MatchPatterns</strong> column is a simple comma-delimited list of <strong>Business</strong> names to match against for any incoming
transactions. This means that any new transactions that are fetched and appended to the <strong>BSA_Transactions</strong> spreadsheet
will have the <strong>Category</strong> column set to equal any matching or partially matching name from the new <strong>MatchPatterns</strong>
column in the <strong>BSA_Categories</strong> sheet. This new change allows convenient and full user control over automatically
categorizing all new transactions going forward.</p>
<p><img src="/img/posts/budgetsheet-v240-user-category-match-patterns/1.png" alt="User-Specified Transaction Category Matching"></p>
<p>Note that <strong>this only applies to new transactions going forward</strong> and will not re-categorize, change, or update any
existing or past transactions in your <strong>BSA_Transactions</strong> sheet.</p>
<p>Happy Budgeting!</p>
<ul>
<li>Vance L</li>
</ul>


BudgetSheet now has user-specified category match patterns. The next time you open BudgetSheet and run an operation, your BSA_Categories sheet will get a new column named MatchPatterns.

BudgetSheet v2.4.0 - User Category Match Patterns

<p>Account balance tracking is here with a <strong>new BSA_Balances sheet</strong>. This sheet will be created for you automatically the
next time transactions are fetched - either manually or automatically if you have turned on auto-fetch (which you should
 totally turn on if you haven&#39;t - it&#39;s awesome!).</p>
<p>The <strong>BSA_Balances</strong> sheet looks like this: 👇</p>
<p><img src="/img/posts/budgetsheet-v230-account-balance-tracking/1.png" alt="New BSA_Balances spreadsheet"></p>
<p>You can use this sheet to generate charts, pivot tables, make crazy formulas, or whatever else you need to do. I would
love to see what you do with it!</p>
<p>Thanks for supporting BudgetSheet.</p>
<ul>
<li>Vance L</li>
</ul>


Account balance tracking is here with a new BSA_Balances sheet. This sheet will be created for you automatically the next time transactions are fetched.

BudgetSheet 2.3.0 - Account Balance Tracking 📈

<p><strong>Auto-fetching has landed in BudgetSheet!</strong> Autofetch is not enabled by default, so if you want to set it up, click on the
&quot;Autofetch Settings...&quot; menu item in the BudgetSheet Add-Ons menu.</p>
<p><img src="/img/posts/budgetsheet-v220-autofetching/1.png" alt="Autofetch Menu Items"></p>
<p>When installed, the Autofetch feature will install a ClockTrigger in your spreadsheet that will run the Autofetch
function every single day to automatically fetch new transactions and append them to your BSA_Transactions sheet without
any intervention. This has been the single most highly requested feature since BudgetSheet was built, and I am very
happy to be able to ship it to everyone today.</p>
<p><img src="/img/posts/budgetsheet-v220-autofetching/2.png" alt="Autofetch Installation Panel"></p>
<p>Enjoy Autofetch!</p>
<ul>
<li>Vance L</li>
</ul>


Auto-fetching has landed in BudgetSheet! Autofetch is not enabled by default, so if you want to set it up, click on the "Autofetch Settings..." menu item in the BudgetSheet Add-Ons menu.

BudgetSheet 2.2.0 - Auto-Fetching is Here! 🎉

<p><img src="/img/posts/budgetsheet-v210-account-relinking/1.png" alt="You can now re-link accounts from the &quot;Manage Accounts...&quot; screen when they get out of sync"></p>
<p>You can now re-link accounts directly from the &quot;Manage Accounts...&quot; screen when they get out of sync!</p>
<p>When an account is out of sync with a bank (login expired, etc.), you will also now receive a nice warning message about
it when you fetch transactions. Any other banks that are still linked and active will fetch transactions successfully.</p>
<p><img src="/img/posts/budgetsheet-v210-account-relinking/2.png" alt="Accounts with login issues now report this when fetching transactions. Working accounts still import transactions
successfully."></p>
<p>Any errors or issues with one of your linked accounts will no longer affect fetching from your other accounts.</p>
<ul>
<li>Vance L</li>
</ul>


You can now re-link accounts directly from the "Manage Accounts..." screen when they get out of sync!

BudgetSheet v2.1.0 - Account Re-Linking + Fixes

<p>After a long time without a big update, <strong>BudgetSheet v2.0 is finally here. BudgetSheet v2.0 will be released THIS
WEEKEND</strong>.</p>
<h2 id="quick-overview-of-changes">Quick Overview of Changes</h2>
<ul>
<li>End-user Plaid Accounts are no longer required or able to be used with BudgetSheet (to stay in compliance with
Plaid&#39;s terms of service)</li>
<li>No more auto-sorting transactions and logs by date - they are always appended to the end of the sheet now</li>
<li>Account balances are now updated every time you fetch transactions</li>
<li>BudgetSheet is faster and more stable</li>
</ul>
<h2 id="changes-to-plaid-integrations-and-setup">Changes to Plaid Integrations and Setup</h2>
<p>This is a major code update that changes the way Plaid integrations work. You will no longer have to signup for your own
Plaid account to use BudgetSheet. It is now all built-in. According to Plaid&#39;s terms of service, this approach should
not have been used to begin with. This update is a major course-correct that fixes that part of BudgetSheet.</p>
<h2 id="a-faster-more-stable-budgetsheet">A Faster, More Stable BudgetSheet</h2>
<p>The net result of changing the Plaid integration is that BudgetSheet should also be faster and more stable. Plaid will
no longer have to review and approve your individual developer account before everything works well. A lot of the code
that does the &quot;heavy lifting&quot; was also moved out of BudgetSheet and into a new high-performance web service that can run
code orders of magnitude faster than it was running in Google&#39;s sandboxed processes.</p>
<h2 id="reconnect-your-accounts-sorry">Reconnect Your Accounts (Sorry)</h2>
<p>The downside of having to make these adjustments is that <strong>you will have to reconnect all your accounts</strong>, and
transactions you have imported will no longer match up with the same transactionIds as the ones you will import from
here on out (because the bank account will be linked through a different Plaid account). <strong>This means you may end up
with a lot of duplicate transactions when you first import using your new linked bank accounts. There is no way around
this</strong>, because these changes were required for BudgetSheet to stay in compliance with Plaid&#39;s terms of service.</p>
<h2 id="going-forward-v2-and-beyond">Going Forward (v2 and beyond)</h2>
<p>While this shift in painful for current users now, this is a one-time deal. You will not be forced to re-connect all
your accounts like this again. Furthermore since I am maintaining and monitoring the new web service behind BudgetSheet,
 I am now able to have a lot more insight into things that go wrong, and can help troubleshoot issues a lot more
 effectively going forward.</p>
<p><strong>Several more feature updates are planned</strong> to BudgetSheet in the near future with the most requested features, and now
that this update is released, I will be able to follow up with quicker, smaller updates a lot more easily.</p>
<p><strong>THANK YOU for being a BudgetSheet customer</strong>. I really appreciate your financial support in the past, and look forward
to a continued business relationship in the future as well.</p>
<ul>
<li>Vance L</li>
</ul>


The all-new BudgetSheet. No Plaid account required anymore.