google_sheets_api_oauth2_read_spreadsheet_range.py

1import os.path
2
3from google.auth.transport.requests import Request
4from google.oauth2.credentials import Credentials
5from google_auth_oauthlib.flow import InstalledAppFlow
6from googleapiclient.discovery import build
7from googleapiclient.errors import HttpError
8
9# If modifying these scopes, delete the file token.json.
10SCOPES = ["https://www.googleapis.com/auth/spreadsheets.readonly"]
11
12# The ID and range of a sample spreadsheet.
13SAMPLE_SPREADSHEET_ID = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"
14SAMPLE_RANGE_NAME = "Class Data!A2:E"
15
16
17def main():
18  """Shows basic usage of the Sheets API.
19  Prints values from a sample spreadsheet.
20  """
21  creds = None
22  # The file token.json stores the user's access and refresh tokens, and is
23  # created automatically when the authorization flow completes for the first
24  # time.
25  if os.path.exists("token.json"):
26    creds = Credentials.from_authorized_user_file("token.json", SCOPES)
27  # If there are no (valid) credentials available, let the user log in.
28  if not creds or not creds.valid:
29    if creds and creds.expired and creds.refresh_token:
30      creds.refresh(Request())
31    else:
32      flow = InstalledAppFlow.from_client_secrets_file(
33          "credentials.json", SCOPES
34      )
35      creds = flow.run_local_server(port=0)
36    # Save the credentials for the next run
37    with open("token.json", "w") as token:
38      token.write(creds.to_json())
39
40  try:
41    service = build("sheets", "v4", credentials=creds)
42
43    # Call the Sheets API
44    sheet = service.spreadsheets()
45    result = (
46        sheet.values()
47        .get(spreadsheetId=SAMPLE_SPREADSHEET_ID, range=SAMPLE_RANGE_NAME)
48        .execute()
49    )
50    values = result.get("values", [])
51
52    if not values:
53      print("No data found.")
54      return
55
56    print("Name, Major:")
57    for row in values:
58      # Print columns A and E, which correspond to indices 0 and 4.
59      print(f"{row[0]}, {row[4]}")
60  except HttpError as err:
61    print(err)
62
63
64if __name__ == "__main__":
65  main()