2

I'm pretty new to python — and programming in general — and I thought it would be nice to work on my new python skills through programming my google sheet. I followed google's python sheets api quickstart using repl.it and then using JupyterLab (see the bottom of the post for the code). It all worked well until the authorization. When I clicked on the link it send me and checked "allow," chrome gave me the error: "This site can’t be reached. localhost refused to connect" on site: http://localhost:49471/.

(The first thing that confused me about that was that I thought repl.it was not interacting with anything local to my computer and was doing everything in the cloud. Has something possibly gone wrong with repl.it or am I not understanding what localhost means?)

I'm on a mac and this happens to me no matter what browser I've tried: chrome, safari, firefox).

This may look like a repeat of another stackoverflow question, but I've tried the recommendations I've found on every other question that looked related and none of them worked for me (google doc api - localhost refusing to connect, Access Google Drive from Sheets: authorization, Http Err 500 when executing Google Sheets API php Quickstart authorization, Accessing Google Sheets API from Google Compute Engine (python)). The things I've tried include:

  1. In google cloud, I created an API key that takes HTTP referrers:

localhost
localhost:*
*localhost*
http://localhost:49471
localhost:49471

  1. In google cloud I created an API code that takes IP addresses and uploaded my own IP address.

  2. in google cloud I created an OAuth 2.0 Client ID web application (which could access the google sheet since it is shared with everyone on the internet: https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit#gid=0)

  3. in my mac system preferences, I temporarily let incoming connections in through the firewall for chrome

  4. I checked to make sure the account that was made a test user for the project was the same user who was trying to access it.

  5. in my mac system preferences, I told it to bypass proxy settings for localhost:49471

  6. I tried restarting my mac and restarting the browser

  7. Adding DriveApp.getStorageLimit(); to the code

  8. turning off my firewall and rebooting my computer

  9. using JupyterLab instead of Replit

  10. opening the authorization line in an incognito window

Edit: I'm looking back at this three days after originally posting. I've tried every step I wrote above again and it still isn't working. Please let me know if there's anything I can do to improve this question because I see a lot of people have viewed it, but no one's answered yet. Thank you so much for any help you can give. It will be nice to start this project!

Here is the code that I copy and pasted from the google sheets api python quickstart:

from __future__ import print_function
import os.path
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials

# If modifying these scopes, delete the file token.json.
SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']

# The ID and range of a sample spreadsheet.
SAMPLE_SPREADSHEET_ID = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
SAMPLE_RANGE_NAME = 'Class Data!A2:E'

def main():
    """Shows basic usage of the Sheets API.
    Prints values from a sample spreadsheet.
    """
    creds = None
    # The file token.json stores the user's access and refresh tokens, and is
    # created automatically when the authorization flow completes for the first
    # time.
    if os.path.exists('token.json'):
        creds = Credentials.from_authorized_user_file('token.json', SCOPES)
    # If there are no (valid) credentials available, let the user log in.
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                'credentials.json', SCOPES)
            creds = flow.run_local_server(port=0)
        # Save the credentials for the next run
        with open('token.json', 'w') as token:
            token.write(creds.to_json())

    service = build('sheets', 'v4', credentials=creds)

    # Call the Sheets API
    sheet = service.spreadsheets()
    result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                                range=SAMPLE_RANGE_NAME).execute()
    values = result.get('values', [])

    if not values:
        print('No data found.')
    else:
        print('Name, Major:')
        for row in values:
            # Print columns A and E, which correspond to indices 0 and 4.
            print('%s, %s' % (row[0], row[4]))

if __name__ == '__main__':
    main()
Noa Perlmutter
  • 43
  • 6

1 Answers1

1

As far as I can tell, neither Repl.it nor Jupyter allow this kind of incoming connection. I'm working on my app directly from my computer now (and getting a new kind of error). If anyone sees this and thinks it's wrong, please let me know. Also, let me know if anyone knows of an online place to put code that will allow google connections. Otherwise, I'll mark this as answered as soon as stackoverflow allows me to.

Noa Perlmutter
  • 43
  • 6