Reading Google Sheets With Python

Google Sheets is an excellent spreadsheet tool.
Sometimes good tools end up used more than they truly aught to be used. With that in mind
it becomes useful at some point to stop dealing with spreadsheets directly and utilize
databases or other dedicated applications to "do the work".

However, during the transition time it can becomes useful to migrate data from a spreadsheet
and use it as a psuedo database during a transition. With this in mind I have a little snippet
below that can be utilized and integrated into your application to pull bits out of a
spreadsheet and turn it into a JSON file.

Google provides a quick introduction into utilizing it's libraries to pull data out of it's sheets. It's a good introduction.

So I start by setting some constants:

scopes = ['https://www.googleapis.com/auth/spreadsheets.readonly']

# The ID and range of a sample spreadsheet.
#   Note that in this instance configuration_dictionary
#   is a dictionary that should represent your settings
#   you can make this dynamic if you wish.
spreadsheet_id = configuration_dictionary.get("spreadsheet_id", "longspreadsheetid")

# Contains the the creds.json items
creds_file = "/tmp/creds.json"

Now that we have our constants. We want to represent the spreadsheet as a pythnic object.
The below should get you there. There's some imports to bring in the google libraries, and
then a couple of steps to build and utilize the credentials and authenticate to a spreadsheet.

from googleapiclient.discovery import build
from google.auth.transport.requests import Request
from google.oauth2 import service_account

...

# Creds File from constants above
creds = service_account.Credentials.from_service_account_file(creds_file)

# Scopes referenced in earlier snippet
creds_w_scopes = creds.with_scopes(scopes)

service = build('sheets', 'v4', credentials=creds_w_scopes, cache_discovery=False)

spreadsheet = service.spreadsheets()

Configure Ranges and so now we can use the spreadsheet to work through ranges and turn it into k/v style jsons. At the end of this we end up with a dictionary filled with lists of data based on what

you.

ranges = {"nameofrange" : {"range" : "pagename!A1:F"},
          "range2" : {"range" : "page2name!A1:F"}}


data = dict()

for key_name, config in ranges.items():

  data[key_name] = list()

  this_result = spreadsheet.values().get(spreadsheetId=spreadsheet_id,
                                         range=config["range"]).execute()

  this_values = this_result.get("values", [])

  headers = values[0]

  for this_row in values[1:]:

    this_dict = {"exposure_type" : exposure_type, "a_time" : export_time}

    for index_num in range(0, len(this_row)):

      name = headers[index_num] if index_num < len(headers) else "index{}".format(index_num)

      this_dict[name] = this_row[index_num]

      data[key_name].append(this_dict)

And with that you can use this data and upload to a database or make it as some jsonic stuff etc.

Hope this helps.