search

e13h / gsheets-plaid

Bringing bank transactions into Google Sheets with Plaid
GNU General Public License v3.0
14 stars 2 forks source link

I hit a snag on the final step of the instructions. Able to help? #12

Closed aeble12 closed 2 years ago

aeble12 commented 2 years ago

Greetings. I stumbled upon this project while Googling. It looks like really cool project. I followed the instructions in an attempt to see if I could get it to work, but I hit a snag in the final step. This is the output I get. Able to help?

Last login: Mon Sep 12 11:54:17 on ttys000 Mac@Macs-MacBook-Pro ~ % python3 -m gsheets_plaid Using Flask session manager

e13h commented 2 years ago

Hi! Thanks for checking out my project. In a previous release I managed secrets in a .env file, but I learned from experience that this workflow isn't very smooth for a package distributed on pip. I've now removed references to this .env file in the code and updated the README to illustrate how to add the right environment variables.

Try upgrading to the latest release 0.4.0 pip install --upgrade gsheets-plaid and check out the updated README. Hopefully this will resolve your issue!

aeble12 commented 2 years ago

Thanks! I installed the latest release 0.4.0 and followed the updated README. But I'm still getting the "No local .env or GOOGLE_CLOUD_PROJECT detected. No secrets found" error. I've checked (via the code pasted immediately below) that the file paths I'm using that point to the JSON files that record the values for GOOGLE_CLOUD_CLIENT_CONFIG and GOOGLE_APPLICATION_CREDENTIALS variables are found/valid paths. Both paths I'm using return true.

from pathlib import Path file_path = Path('[path name]') print(file_path.exists())

I read that closing the Terminal can wipe out the value of the FLASK_SECRET_KEY environment variable that was set, so to avoid that issue, I'm intentionally leaving the Terminal window open. As far as I can tell, everything in Google Cloud is setup according to the README and the tutorial references mentioned in the README. I'm not sure what my next troubleshooting step ought to be.

aeble12 commented 2 years ago

Hang on a sec. I've made some progress. I'm embarrassed to say that I wasn't on 0.4.0 when I thought I was. I uninstalled the prior release package and then installed the current release 0.4.0. Upon trying again after the installation of relaese 0.4.0, I'm getting a different error: "jinja2.exceptions.TemplateNotFound: login.html error." Might the resolution to this error be to move certain files around? Maybe this is a consequence of not installing in a virtual environment (?) Here is the output from the Terminal:

Using Flask session manager

aeble12 commented 2 years ago

UPDATE: I uninstalled and then reinstalled in a virtual environment to see if that would resolve... Same error as before: "jinja2.exceptions.TemplateNotFound: login.html". Here is the output from Terminal:

Using Flask session manager

e13h commented 2 years ago

@aeble12 Sorry for any frustration this may have caused, and thanks for including your commands and terminal output. This is my first PyPI package, so I appreciate your patience :sweat_smile:

I was able to reproduce the same error on my machine with a new virtual environment. It turns out I didn't have a manifest file configured to include the non-python files in the package. I've added that and tested it a couple of times, so I think it will work now 🤞🏼

Try updating to the latest release 0.4.1 and let me know if it works

aeble12 commented 2 years ago

No problem at all. Thank you for all your amazing work on this. I downloaded and installed 0.4.1 on a fresh virtual environment. I'm so close now. I get stuck on a completely blank (aside from the title bar and the the address bar) Sign In - Google Accounts window. There's nothing for me to enter/press/confirm in the window that is raised. I wonder if this issue might be a settings related thing that needs to be tweaked in my Google Cloud account. This is the output from the Terminal...you can see where it gets stuck:

Using Flask session manager

e13h commented 2 years ago

The terminal output is looking good so far. The login page is very barebones, so you should just see a sign in with Google button. Does the button fill the entire width of your browser window though? If so, something is configured wrong (either the environment variable isn't set properly or something in the Google Cloud console). Here's an example of this:

wrong

If it is working correctly, the button should be much smaller and in the top left corner, like this:

right

After clicking on the button, a new little popup should appear with the opportunity to log in with a Google account, like this (my personal info is blacked out, but you get the idea):

popup

Make sure you log in with a Google account that you've whitelisted as a "test user" in the Google Cloud console under the OAuth consent screen page.

aeble12 commented 2 years ago

Well noted. Thanks! Yes, I do get the Sign in with Google button whose width extends to the boundaries of the browser window (indicating that something is wrong) as opposed to a button with shorter width positioned in the upper left corner of the browser window. I can "click" the giant-width Sign in with Google button and it does raise a subsequent Sign in Window (to overlay the first browser window), but that newly raised window is also blank. Just to confirm, setting the FLASK_SECRET_KEY environment variable in this context merely amounts to specifying the string value as in the fourth line of text (pasted immediately below) that gets entered in the Terminal, correct?

GOOGLE_CLOUD_CLIENT_ID=your_google_cloud_client_id_here \ GOOGLE_CLOUD_CLIENT_CONFIG=/path/to/oauth/client/credentials/file.json \ GOOGLE_APPLICATION_CREDENTIALS=/path/to/service/account/key.json \ FLASK_SECRET_KEY=super_secret_string \ python3 -m gsheets_plaid

In other words, there isn't anything else that needs to occur as far as setting the FLASK_SECRET_KEY environment variable, correct? I'm asking in an effort to rule out if the trouble I'm experiencing has to do with the FLASK_SECRET_KEY (and only that) or if the other environment variables (or their associated set ups in Google Cloud) might be messed up in some way as well.

e13h commented 2 years ago

Aha! I figured it out.

I decided to create a new Google Cloud project and follow my own tutorial. I got the same output as you, and then after spending some time comparing the working project with this new one that wasn't working, I realized the difference:

You need to create a web application OAuth Client ID instead of a desktop app one. Then you need to add https://localhost and https://localhost:8080 as authorized JavaScript origins, and add https://localhost:8080/google-oauth-callback as an authorized redirect URI.

I created a new (and hopefully improved) TUTORIAL.md file in the repo with my walkthrough for getting the web server up and running.

e13h commented 2 years ago

After you create the new OAuth client ID, don't forget to update GOOGLE_CLOUD_CLIENT_ID and GOOGLE_CLOUD_CLIENT_CONFIG to their latest values

aeble12 commented 2 years ago

Well done! I read through the new TUTORIAL.md and made the necessary changes on my end per instructions in TUTORIAL.md. I'm pleased to report that this is now working on my end as well. Thank you! I successfully linked 1 financial institution and then pulled raw data to Google Sheets via the "Synch transactions" button. Very cool. I then went back to the area of the web browser where one manages the Plaid access tokens for the purpose of linking four more financial institutions (the max amount permitted by Plaid without user having to make a special request via the Plaid Dashboard to be given 100 access tokens). I don't think I will be able to test the pull of data from the other four financial institutions I added until 12 hours from now (it tells me I can only synch transactions once within a 12 hour time frame). So, I don't yet know if the other four are working (or how they get expressed in the Google Sheet e.g. on same sheet within the file or as separate sheets within the file or whatever). I'll plan to check on this and other things some time tomorrow. And perhaps by then, Plaid will have gotten back to me about my request to increase my Plaid access tokens from 5 to 100. I'm also hoping that certain financial institutions not currently supported by Plaid will be added in the near future. We'll see how that goes. Thanks again and great job on this!

aeble12 commented 2 years ago

Forgot to mention....one observation I had while going through the new TUTORIAL.md is that the enabling of Cloud Run API does not appear to be necessary. An attempt to enable Cloud Run API will raise a message about having to pay (if user is not already a paying user). And since I had success without enabling Cloud Run API, I think it may be safe to say it is not necessary to enable Cloud Run API.

aeble12 commented 2 years ago

Uh oh. It stopped working. I'm getting an "Internal Server Error" when I press the "Synch transactions" button. I tried removing all linked accounts other than the first one (which was the one I confirmed yesterday was working) and then tried again. Same message. I also tried selecting different values for the Sync transactions. For instance, I tried 30, 60, 90 and 180. Each case resulted in the same "Internal Server Error" message after pressing the "Synch transactions" button. I was looking for a way to wipe out all the settings and start with a clean slate, but wasn't able to figure out how to do this or if it is currently supported. For instance, in clicking the "Manage" link associated with Item = "Spreadsheet", there doesn't appear to be a way to rename or remove the name of the spreadsheet that I initially entered on my first go around. Is there a way to clear out everything and sort of start over?

e13h commented 2 years ago

I don't think I will be able to test the pull of data from the other four financial institutions I added until 12 hours from now (it tells me I can only synch transactions once within a 12 hour time frame)

The 12 hour time limit is an arbitrary constraint I added since I was doing some testing with the web server deployed on the internet, and I didn't want to exceed free tier usage limits. I'm planning on adding a toggle to turn this off so that when running locally you don't have to deal with it. You can track the progress of this in #15.

I think it may be safe to say it is not necessary to enable Cloud Run API.

Thanks, I'll update the tutorial 👍🏼

I'm getting an "Internal Server Error" when I press the "Synch transactions" button.

Does the terminal print out a stack trace when you see the "Internal Server Error" message?

Is there a way to clear out everything and sort of start over?

Since the data is stored as a browser cookie, if you go to your browsers settings, their should be an option to "view website data" where you can search for "localhost" and clear the data associated with localhost. Or, you could clear all cookies/data in the last 24/48 hours (or whatever covers when you first successfully signed in).

Adding a button to delete your data and start fresh would be a good idea though. I'll add it to my backlog of features.

As a side note, you may have noticed I released v0.4.2 yesterday with some web server UI and database fixes. I'm not sure if that will fix the problem, but if you are going to clear the cookie and start fresh, it may be a nice time to try this latest version too.

aeble12 commented 2 years ago

Thanks! Well noted on the 12 hour time limit being an arbitrary constraint...

Here is one instance of the terminal print out for the stack trace that is written when I see the "Internal Server Error" message:

ValueError: 30 columns passed, passed data had 22 columns 127.0.0.1 - - [17/Sep/2022 09:11:11] "GET /sync?days=180 HTTP/1.1" 500 - 127.0.0.1 - - [17/Sep/2022 09:11:24] "GET /index HTTP/1.1" 200 - [2022-09-17 09:11:33,195] ERROR in app: Exception on /sync [GET] Traceback (most recent call last): File "/Users/Mac/Documents/PythonVENV01/lib/python3.10/site-packages/pandas/core/internals/construction.py", line 982, in _finalize_columns_and_data columns = _validate_or_indexify_columns(contents, columns) File "/Users/Mac/Documents/PythonVENV01/lib/python3.10/site-packages/pandas/core/internals/construction.py", line 1030, in _validate_or_indexify_columns raise AssertionError( AssertionError: 30 columns passed, passed data had 22 columns

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "/Users/Mac/Documents/PythonVENV01/lib/python3.10/site-packages/flask/app.py", line 2525, in wsgi_app response = self.full_dispatch_request() File "/Users/Mac/Documents/PythonVENV01/lib/python3.10/site-packages/flask/app.py", line 1822, in full_dispatch_request rv = self.handle_user_exception(e) File "/Users/Mac/Documents/PythonVENV01/lib/python3.10/site-packages/flask/app.py", line 1820, in full_dispatch_request rv = self.dispatch_request() File "/Users/Mac/Documents/PythonVENV01/lib/python3.10/site-packages/flask/app.py", line 1796, in dispatch_request return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args) File "/Users/Mac/Documents/PythonVENV01/lib/python3.10/site-packages/gsheets_plaid/web_server/main.py", line 277, in sync sync_transactions(gsheets_service, plaid_client, plaid_access_tokens, spreadsheet_id, num_days) File "/Users/Mac/Documents/PythonVENV01/lib/python3.10/site-packages/gsheets_plaid/sync.py", line 279, in sync_transactions transactions = get_transactions_from_gsheet(gsheets_service, spreadsheet_id) File "/Users/Mac/Documents/PythonVENV01/lib/python3.10/site-packages/gsheets_plaid/sync.py", line 135, in get_transactions_from_gsheet transactions = pd.DataFrame(rows[1:], columns=rows[0]) File "/Users/Mac/Documents/PythonVENV01/lib/python3.10/site-packages/pandas/core/frame.py", line 721, in init arrays, columns, index = nested_data_to_arrays( File "/Users/Mac/Documents/PythonVENV01/lib/python3.10/site-packages/pandas/core/internals/construction.py", line 519, in nested_data_to_arrays arrays, columns = to_arrays(data, columns, dtype=dtype) File "/Users/Mac/Documents/PythonVENV01/lib/python3.10/site-packages/pandas/core/internals/construction.py", line 883, in to_arrays content, columns = _finalize_columns_and_data(arr, columns, dtype) File "/Users/Mac/Documents/PythonVENV01/lib/python3.10/site-packages/pandas/core/internals/construction.py", line 985, in _finalize_columns_and_data raise ValueError(err) from err

I started over...downloaded and installed v0.4.2. I cleared all cookies within the last 24 (this timeframe sufficiently captures when I last successfully had everything working with one financial institution linked). I was able to work through a clean slate on the webserver, but when I got to the Link Account part of the webserver, I keep getting a "Something went wrong Internal error occurred" message returned from the Plaid popup thing. I tried linking with multiple financial institutions (including the one that was confirmed working yesterday), but each one results in the same message being raised in the Plaid popup thing. Maybe there's something going on with Plaid that I need to resolve (?)

e13h commented 2 years ago

This is an interesting error. I haven't run into this before. It looks like it is coming from pandas, a data analysis library I use to do some transformations on the data before sending it to Google Sheets.

A couple of questions come to mind that would help for debugging:

aeble12 commented 2 years ago

I haven't manually edited the Google Sheet that the data is stored in.

I see the same "Something went wrong Internal error occurred" message in the Plaid pop up thing in both cases i.e. when in Development mode and in Sandbox mode. But I don't think this fact helps much with the debugging effort since I'm now operating in a pre-Synch button context i.e. I'm not able to get to the point where the "Panda" traceback would be written because I'm stuck at a prior step: the managing of the Plaid access tokens which, if successful, would result in a linking of an account. At this pre-Synch button step, I merely get stuff like the the following in the terminal. Maybe Plaid is just goofed up or something.

127.0.0.1 - - [18/Sep/2022 20:04:42] "GET /index HTTP/1.1" 200 - 127.0.0.1 - - [18/Sep/2022 20:04:47] "GET /manage-plaid-items HTTP/1.1" 200 - 127.0.0.1 - - [18/Sep/2022 20:04:48] "GET /static/plaid_link.js HTTP/1.1" 200 - 127.0.0.1 - - [18/Sep/2022 20:05:24] "GET /index HTTP/1.1" 200 - 127.0.0.1 - - [18/Sep/2022 20:06:07] "GET /manage-spreadsheets HTTP/1.1" 200 - 127.0.0.1 - - [18/Sep/2022 20:06:16] "GET /index HTTP/1.1" 200 - 127.0.0.1 - - [18/Sep/2022 20:06:20] "GET /manage-plaid-items HTTP/1.1" 200 - 127.0.0.1 - - [18/Sep/2022 20:06:20] "GET /static/plaid_link.js HTTP/1.1" 200 -

aeble12 commented 2 years ago

UPDATE: Good news. It's working again. I didn't do anything differently. My theory is that Plaid's honoring of my request to bump up my access tokens from 5 to 100 may have coincidentally interfered with my ability to link accounts i.e. I think the "Something went wrong Internal error occurred" error was purely a Plaid-side glitch that resolved itself once Plaid completed the up-ing of my access tokens (I received confirmation from Plaid this morning that I was bumped up from 5 to 100...). I could be wrong about this; it's just a theory. I was able to successfully link over 5 financial institutions including ones that weren't purely bank accounts. For instance, just to see what would happen, I linked credit cards and retirement accounts. The linking was successful, but the synching would fail if any non-purely bank account accounts were including in the mix of linked institutions presumably because the backend was "looking for" a list of permitted column names (or whatever) such as 'transaction_id' among others and when it didn't "see" these in association with the non-purely bank account accounts, an error was raised such that all linked accounts (including valid purely bank account accounts) failed to synch. The Terminal prints the following in cases where any non purely bank accounts are included in the mix of successfully linked accounts:

KeyError: "None of [Index(['transaction_id', 'pending_transaction_id', 'pending', 'account_id',\n 'date', 'datetime', 'name', 'merchant_name', 'amount',\n 'iso_currency_code', 'unofficial_currency_code', 'payment_channel',\n 'category_id', 'category', 'personal_finance_category', 'location'],\n dtype='object')] are in the [columns]"

This is to be expected (I think) since the program is not designed (I think) to work with credit card and retirement account accounts; rather it's intended to work exclusively with bank account accounts. I just wanted to see what would happen if I tried synching accounts that were other than pure bank account accounts. Now I know.

e13h commented 2 years ago

Alright, glad to hear it's working for you now. You're right that I designed the package to only support regular bank accounts for now, but since Plaid supports all kinds of financial institutions, there's definitely a lot of room to grow. Feel free to fork this project!

Since it seems like the original issue is now resolved, I'll go ahead and close this issue.

By the way, I released a new version yesterday (v0.4.3) with a toggle for restricting sync to every 12 hours, and it is off by default now.

aeble12 commented 2 years ago

Thanks! I've installed v0.4.3. Oddly, I don't see the toggle for restricting synch to every 12 hours. By toggle, you mean a button type thing on the webserver? I see the days drop down box and next to it is the "Synch transactions" button. But I don't see another button for toggling the restricting synch to every 12 hours so that it is either on or off.

Also, I noticed that the TUTORIAL link on the Description page which was added for v0.4.3 seems to be broken. It's returning a webpage that says "We looked everywhere but couldn't find this page"

This is the underlying link: https://pypi.org/project/gsheets-plaid/TUTORIAL.md

e13h commented 2 years ago

I don't see the toggle for restricting synch to every 12 hours.

The toggle is an environment variable that you pass in when spinning up the web server. The script looks for a nonzero value assigned to GSHEETS_PLAID_RESTRICTIONS_ENABLED, so if you wanted to turn on the restrictions you could do something like

GSHEETS_PLAID_RESTRICTIONS_ENABLED=1 \
\ # Other environment variables as described in the tutorial
python -m gsheets_plaid

This is the underlying link: https://pypi.org/project/gsheets-plaid/TUTORIAL.md

Huh, that's interesting. I was using a relative link to point to the tutorial, and it works when you visit the page on GitHub, but I didn't realize that the readme would also end up on PyPI. I'll update the link to be an absolute reference.

aeble12 commented 2 years ago

Thanks, Evan! Ah yes. I see what you mean about the toggle being an environment variable that is passed when spinning up the web server (i.e. not a button on the webserver). I tested the GSHEETS_PLAID_RESTRICTIONS_ENABLED environment variable with both a zero and a one value and in both cases the webserver says "You may not sync more than once every 12 hours." Maybe I'm doing something wrong? It shouldn't make a difference if I just enter and run the code lines from the Script in the Terminal (rather than actually enable the Script to be run from the Terminal via a single line), right? That's the only thing I'm doing differently in comparison to the Tutorial as far as I can tell. I'm just pasting the several lines of code from the Script and running them right from the Terminal rather than enabling the Script so that the Script itself can be run from the Terminal with a single line. As a troubleshooting step, I tried deleting my data via the button on the webserver and then starting over again from scratch. Same result.