Simple CSV Import Workflow (for Unsupported Data Sources)

Google Sheets Show & Tell
1

What is the goal of your workflow? What problem does it solve, or how does it help you?
One of the challenges to importing CSV data from unsupported data sources is remapping the columns to match the Transactions sheet. Further, some columns (like Month, Week, Date Added, etc) contain derived data.

The goal of this workflow is to simplify and improve the accuracy of this process.

How did you come up with the idea for your workflow?
I built the Import CSV Line Items workflow in the Tiller Community Solutions add-on, but I know many users have CSV data sources that are not supported.

Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
This workflow requires you to add bound scripts to your spreadsheet to perform the following actions:

  • Requests sheet name of your imported CSV data
  • Maps imported data to Transactions sheet headers per the column mapping in the first row (see below)
  • Creates new rows at the top of the Transactions sheet & inserts the data

Detailed documentation & script files are available in my csv2transactions project on Github.

I’ve also posted a video demo to walk you through:

  1. Installing the project script files
  2. Configuring your column mapping for your CSV data source
  3. Running the import process
  4. Learning a few power-user tricks

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

Anything else you’d like people to know?
This script works with Tiller Money spreadsheets but it is a personal project not created, published or maintained by Tiller Money.

Let me know if you have any feedback! :smiley:

3

Thank you so much Randy! I just gave this a go with my Target RedCard.

I noticed something was slightly different for me:
From the GitHub instructions:

Once the spreadsheet tab reloads (be patient), you will see a new option in the menu bar “Simple CSV”. You have installed the script.

My menu option is “Simple Import”

For others who use this script, make sure to read the tip about Inverting Numbers but naming the column -Amount so your transactions stay consistent.

4

Great catch, @RachelB. Sorry about that. I changed the menu terminology about midway through development and the documentation didn’t keep pace. It’s fixed in the instructions now.

I’m glad the “inverting numbers” trick is working for you. Alternatively, you could map directly to a formula-driven “Amount” column if you use an ARRAYFORMULA() like this:

={"Inverted Amount";ARRAYFORMULA(IF(ISBLANK(A3:A),IFERROR(1/0),-A3:A))}
Don’t forget to remap the columns to match your data

Really happy to hear this has helped you through the Target outage.

1 Like
5

Can there be an option for excel users to easily import csv files into tiller money spreadsheets easily? @randy

1 Like
6

@omniiomega we hope to offer more of the tools for Excel that we have for Google Sheets, but the CSV import isn’t on deck just yet. This workflow specifically is built in a “bound script” and makes use of some easy UI components offered by Google. I’m not sure whether the same is easy/feasible in Microsoft Excel as a community built solution. I know there are “macros” in Excel, but our experience has been that things are much more complicated to set up and document how to use for Excel.

7

Great script - just what I needed. I have several bounded scripts already running on my Tiller Sheet to manage my autoCat rules and add metadata to my transactions sheet. I was able to integrate this nicely into my existing sheets. Your use of a UI element to select the import sheet was new to me - nice!. (Debugging does seem to be more of a challenge though with the UI class).
I did clone your repo and made a couple of changes in a new branch. First I added some basic logging (I am kind of anal about that) and an alert at the end to indicate the completion of the script.

Secondly I added a check to detect timezone discrepancies. Your appsscript.json sets the script timezone to LA time and this can be different than than the timezone setting for the bound sheet (mine is Eastern). I normally do not see any issues from this - but I noticed that the Month and Week values get calculated based on the script timezone, but then are translated to the sheet timezone when it is written. I saw cases where the value for month changed from 7/1 to 6/30! It was driving me crazy. It took me a while to figure out what was going on.

I can provide more details on my code if you are interested, or do a pull request to your Github repo.
Thanks again for this script.

8

Cool. It’s great to see you diving in @wmgillett.

Timezone issues can be pretty frustrating. If the change is small, send it to me and I’ll consider merging it.

9

Sure thing. Here is a gist with an updated version of the main file.

The checking of time zones is done in a sub-function (you will find it at the at end of your code).
An alert is displayed if a discrepancy is detected and the user has the option to cancel the import or continue it.
Logging is pretty basic.
There is a ui alert at the end of the import indicating the number of records added.

10

Cool. Pushed your changes to the main repo, @wmgillett.
Thanks for the enhancement.

1 Like
11

You are welcome - glad the change made sense. This enhancement was my first one to a public repo. So it is nice to make a small contribution for the first time. Thanks again.

1 Like
12

Honestly, it’s my first time merging code from a non-team member. I imagine there are ways to more formally assign credit to your contribution (I including your Git username in the commit). If there is a better way, I can redo the commit.

13

Given that this was novel for your team, I appreciate what you did.
From what I’ve read, I think if I opened an issue related to this (or another issue) in your repo Issues · scoover/csv2transactions · GitHub
and then offered a solution in that context, this might allow for more direct attribution of a contribution. I will try this next time I have an issue I want to publicize.

14

Sounds great. We’ll do that next time. Keep those enhancements coming.

15

Randy,

The instructions on Github - step 3 says " Click on Tools/Script editor in the menu bar."

I don’t see “Script” in the tools menu.

Screenshot 2024-02-10 at 11.47.36 PM
Screenshot 2024-02-10 at 11.47.36 PM778Ă—618 35 KB

Has it been moved?

Thank you.

Greg

16

Google changed their interface to move it under Extensions menu a few years ago.

1 Like
17

Wow! I finally have a way to easily import my bank transactions manually! I’ll be able to use this until the sync gets fixed. (5 months and counting). Your video was a tremendous help. Thank you.

1 Like
18

I know this is an older thread, but this looks awesome and something I definitely need to use. Video is really straightforward also. Several questions though. First, is it intended/acceptable to keep the Importer and Bound Script directly in the Foundation template? Also, some of the extra columns in Transactions sheet that are not there by default (like Notes, Metadata, etc.) should I add those manually first? Or, do they get added via the script? Thanks again!

19

@g2kk9phx the intent is that you’d install in the Foundation Template, yes.

As far as the extra columns, I’m not sure if the importer adds those for you or not, some of them are probably not even needed and maybe were just part of Randy’s set up.

I’d just create a named version in your Version History for right before you try to implement and then you have a point you can go back to if things blow up.

I use this in my personal sheet and it works really well though I think there was a community member who made some improvements to the script and I don’t know how/if those automatically show up in the Github files as the latest.

20

@randy thank you for publishing this solution. I need to manually import accounts from Europe so it’s super helpful.
One strange behavior is that I can convert the amount to USD using GOOGLEFINANCE() on the sheet with imported data. Once I execute the script, Amount comes up as N/A. Could it be a permission issue around calling external APIs?

21

@rem is there any error text if you hover over the N/A? That might give a clue as to what the issue is.

One thing is that if you have the amount being converted in a column with a formula it could have something to do with the script not being able to process the formula? or the formula breaking after running the script? What if you try pasting values into the amount column after the conversion instead?

1 Like