my cheat sheet on google apis

Home

1 Python Environment Prerequisites:

From google, check out https://cloud.google.com/python/docs/setup

It tells you all you need to get setup for your python venv, including the google modules you need to pip install and a link to the Python google libraries on git hub: https://github.com/googleapis/google-cloud-python]]

2 Google Drive API

quickstart

2.1 Google Scopes (most common)

Scope Meaning
https://www.googleapis.com/auth/drive.file Per-file access to files
  created or opened by the app.
https://www.googleapis.com/auth/drive.apps.readonly Read-only access to
  installed apps
https://www.googleapis.com/auth/drive Full permission to all user's
  files, excluding app data fld
https://www.googleapis.com/auth/drive.readonly Same data, but read-only
https://www.googleapis.com/auth/spreadsheets  

Used in code: 

SCOPES = ['https://www.googleapis.com/auth/drive', 'https://www.googleapis.com/auth/spreadsheets']
SCOPES = 'https://www.googleapis.com/auth/drive.readonly.metadata'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
SCOPES = ['https://www.googleapis.com/auth/drive.metadata.readonly']
SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']

3 Google Sheets API

4 Google Drive API authentication

About auth

When you enable the API, you will be able to download client info as .json file. Move this file to where you run your google python scripts, and rename it to something your scripts use. I use creds.json

Because I have credentials for Google Drive API, as well as Google Sheets API, I have two version of these credentials, one is creds-drive.json the other is creds-sheets.json Then I cp the one I want to creds.json. I may change that so that my scripts call the appropriate one, as is.

4.0.1 Creds with service accounts

The preferred way for python scripts to access google api resources is to use a service account. I have two set up, one for zintis@gmail.com, the other for perkonsrusins@gmail.com Before running my python scripts copy the correct one to creds.json. The two originals are named:

  • creds-rusins-serv-acct.json
  • creds-zintis-serv-acct.json

So I would run cp creds-rusins-serv-acct.json creds.json if I want to run as rusins and the other if I want to run as zintis.

5 oAuth2.0 for Service Accounts

Read the google docs on setting up google service accounts for python scripts that need read/write access to google docs and google spreadsheets. You use these service accounts along with a key for that service account, and then assign share priviledges to this service account email to the folder or specific google sheet that you need. Then your python script prepares to make authorized API calls by using the service account's credentials to request an access token from the OAuth 2.0 auth server. Finally the script will use the returned access token for all Google API calls.

Google recommends that you only use the Google API client libraries to access google api services. That is because the Google API client libraries take care of the complexities of cyptography needed to make the handshakes secure. The handshakes involve creating and cyrptographically signing JWTs, or JSON Web Tokens.

To make python calls using this service account look the google docs from this link: developers.google.com/docs

5.1 Step 1 create the Credentials object:

Change the scope as needed, but the credentials object code works for all of the google services. (I think. Certainly for sheets and drive it does). 

from google.oauth2 import service_account

SCOPES = ['https://www.googleapis.com/auth/sqlservice.admin']
SERVICE_ACCOUNT_FILE = '/path/to/service.json'

creds = service_account.Credentials.from_service_account_file(
	SERVICE_ACCOUNT_FILE, scopes=SCOPES)

5.2 Step 2 use Credentials Object to call Google APIs

Use the authorized Credentials object to call Google APIs by completing the following steps:

  1. Build a service object for the API that you want to call. You build a a service object by calling the build function with the name and version of the API and the authorized Credentials object. For example, to call version 1.beta.3 of the Cloud SQL Administration API: 
    import googleapiclient.discovery  # the whole thing, or just the build method

sqladmin = googleapiclient.discovery.build('sqladmin', 'v1beta3', credentials=creds)

    One can streamline if all you need is the build function, just import that: 

    from googleapiclient.discovery import build

sqladmin = build('sqladmin', 'v1beta3', credentials=creds)

    Some more build examples:

    • sqladmin = build('sqladmin', 'v1beta3', credentials=creds)
    • serv_sheet = build('sheet, 'v4', credentials=creds)
    • serv_drive = build('drive', 'v3', credentials=creds)
    • serv_drive = build('drive', 'v3', http=creds.authorize(Http()))
  1. Make requests to the API service using the interface provided by the service object. For example, to list the instances of Cloud SQL databases in the exciting-example-123 project: 
    response = sqladmin.instances().list(project='exciting-example-123').execute()

See the docs at developers.google.com/identity

6 Google Documentation links:

documentation link
Sheets ref. https://developers.google.com/sheets/api/reference/rest?apix=true
   

7 Sheets python commands v3? i.e. obsoleted?

  • sheet.get_all_records()) # will return a list of lists (a list for each row)
  • sheet.row_values(2) # returns a list of values from row 2 (counting starts at 1)
  • sheet.cell(2,1).value # returns the value of in the cell in row 2 column 1
  • sheet.col_values(3) # returns a list of values from column 3
  • sheet.insert_row(["Monty", "Python", "Flying", "Circus"], 27) # moves all rows at 27 down
  • sheet.insert_row(array_of_values, inserted_row_number) # moves all rows down.
  • sheet.delete_row(7) # deletes row 7 and moves up all rows below 7
  • sheet.update_cell(89,1) # row 89, column 1 gets a new value
  • cell_of_interest= sheet.find("Cleese") # finds a cell that has "Cleese" in it. print(cell_of_interest.value) print(cell_of_interest.row) print(cell_of_interest.col)

8 Sheets python commands v4 (non-exhaustive)

See https://developers.google.com/sheets/api/reference/rest for details. Til now I have used these:

  • sheet.values().get(spreadsheetId=LAMABLE_SHEET_ID, range=LAMABLE_RANGE).execute() 
"""
BEFORE RUNNING:
---------------
1. If not already done, enable the Google Sheets API
   and check the quota for your project at
   https://console.developers.google.com/apis/api/sheets
2. Install the Python client library for Google APIs by running
   `pip install --upgrade google-api-python-client`
"""
from pprint import pprint

from googleapiclient import discovery

# TODO: Change placeholder below to generate authentication credentials. See
# https://developers.google.com/sheets/quickstart/python#step_3_set_up_the_sample
#
# Authorize using one of the following scopes:
#     'https://www.googleapis.com/auth/drive'
#     'https://www.googleapis.com/auth/drive.file'
#     'https://www.googleapis.com/auth/spreadsheets'
credentials = None

service = discovery.build('sheets', 'v4', credentials=credentials)

# The ID of the spreadsheet to update.
spreadsheet_id = 'my-spreadsheet-id'  # TODO: Update placeholder value.

# The A1 notation of the values to update.
range_ = 'my-range'  # TODO: Update placeholder value.

# How the input data should be interpreted.
value_input_option = ''  # TODO: Update placeholder value.

value_range_body = {
    # TODO: Add desired entries to the request body. All existing entries
    # will be replaced.
}

request = service.spreadsheets().values().update(spreadsheetId=spreadsheet_id, range=range_, valueInputOption=value_input_option, body=value_range_body)
response = request.execute()

# TODO: Change code below to process the `response` dict:
pprint(response)
  • request = service.spreadsheets().values().update(spreadsheetId=spreadsheet_id, range=range_, valueInputOption=value_input_option, body=value_range_body)
  • response = request.execute()
  • sheet.values().(spreadsheetId=LAMABLE_SHEET_ID,
  • sheet.row_values(2) # returns a list of values from row 2 (counting starts at 1)
  • sheet.cell(2,1).value # returns the value of in the cell in row 2 column 1
  • sheet.col_values(3) # returns a list of values from column 3
  • sheet.insert_row(["Monty", "Python", "Flying", "Circus"], 27) # moves all rows at 27 down
  • sheet.insert_row(array_of_values, inserted_row_number) # moves all rows down.
  • sheet.delete_row(7) # deletes row 7 and moves up all rows below 7
  • sheet.update_cell(89,1) # row 89, column 1 gets a new value
  • cell_of_interest= sheet.find("Cleese") # finds a cell that has "Cleese" in it. print(cell_of_interest.value) print(cell_of_interest.row) print(cell_of_interest.col) 
cell_list= sheet.findall("Cleese")~  # returns all cells containing "Cleese" as a list
for cell in cell_list:
   print(cell_of_interest.value)
   print(cell_of_interest.row)
   print(cell_of_interest.col)

8.1 Python code for sheets api

# 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]))

9 Sheets python commands using API v4

Seems the v3 is different enough that I made this section. For instance, to write values into a spreadsheet, you cannot do it with sheet.insert_row Instead check out blank in the spreadsheets API reference In particular it seems that you need spreadsheet.values.update and send it a json representation as a body to the request, in this form: 

{
"range": string,
"majorDimension": enum (Dimension),
"values": [
  array
  ]
}

For instance for a LAmable Spreadsheet row to insert as row #2 : 

{
  "range": "2021_Usage!A1:D5",
  "majorDimension": "ROWS",
  "values": [
    ["Item", "Cost", "Stocked", "Ship Date"],
    ["Wheel", "$20.50", "4", "3/1/2016"],
    ["Door", "$15", "2", "3/15/2016"],
    ["Engine", "$100", "1", "3/20/2016"],
    ["Totals", "=SUM(B2:B4)", "=SUM(C2:C4)", "=MAX(D2:D4)"]
  ],
}

9.1 Update Method for Spreasheet.values

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update

Excellent pythyon examples were found here: sheets api reference 

"""
BEFORE RUNNING:
---------------
1. If not already done, enable the Google Sheets API
   and check the quota for your project at
   https://console.developers.google.com/apis/api/sheets
2. Install the Python client library for Google APIs by running
   `pip install --upgrade google-api-python-client`
"""
from pprint import pprint

from googleapiclient import discovery

# TODO: Change placeholder below to generate authentication credentials. See
# https://developers.google.com/sheets/quickstart/python#step_3_set_up_the_sample
#
# Authorize using one of the following scopes:
#     'https://www.googleapis.com/auth/drive'
#     'https://www.googleapis.com/auth/drive.file'
#     'https://www.googleapis.com/auth/spreadsheets'
credentials = None

service = discovery.build('sheets', 'v4', credentials=credentials)

# The ID of the spreadsheet to update.
spreadsheet_id = 'my-spreadsheet-id'  # TODO: Update placeholder value.

# The A1 notation of the values to update.
range_ = 'my-range'  # TODO: Update placeholder value.

# How the input data should be interpreted.
value_input_option = ''  # TODO: Update placeholder value.

value_range_body = {
    # TODO: Add desired entries to the request body. All existing entries
    # will be replaced.
}

request = service.spreadsheets().values().update(spreadsheetId=spreadsheet_id, range=range_, valueInputOption=value_input_option, body=value_range_body)
response = request.execute()

# TODO: Change code below to process the `response` dict:
pprint(response)

The input range is used to search for existing data and find a "table" within that range. Values will be appended to the next row of the table, starting with the first column of the table. See the guide and sample code for specific details of how tables are detected and data is appended.

The caller must specify the spreadsheet ID, range, and a valueInputOption. The valueInputOption only controls how the input data will be added to the sheet (column-wise or row-wise), it does not influence what cell the data starts being written to. 


service = discovery.build('sheets', 'v4', credentials=credentials)

# The ID of the spreadsheet to update.
spreadsheet_id = 'my-spreadsheet-id'  # TODO: Update placeholder value.

# The A1 notation of a range to search for a logical table of data.
# Values will be appended after the last row of the table.
range_ = 'my-range'  # TODO: Update placeholder value.

# How the input data should be interpreted.
value_input_option = ''  # TODO: Update placeholder value.

# How the input data should be inserted.
insert_data_option = ''  # TODO: Update placeholder value.

value_range_body = {
    # TODO: Add desired entries to the request body.
}

request = service.spreadsheets().values().append(spreadsheetId=spreadsheet_id, range=range_, valueInputOption=value_input_option, insertDataOption=insert_data_option, body=value_range_body)
response = request.execute()

9.2 Append values:

https://developers.google.com/sheets/api/samples/writing#append_values Tricky part is the append range, but that becomes really easy when you read https://developers.google.com/sheets/api/guides/values and in my case for LAmable reports the range is simply "Usage-2021!A2" although "Usage-2021!A2:H" also works.

10 Important, but not properly organized/sorted:

https://developers.google.com/sheets/api/guides/values#writing 

values = [
    [
	# Cell values ...
    ],
    # Additional rows ...
]
body = {
    'values': values
}
result = service.spreadsheets().values().update(
    spreadsheetId=spreadsheet_id, range=range_name,
    valueInputOption=value_input_option, body=body).execute()
print('{0} cells updated.'.format(result.get('updatedCells')))

shows you python code to update spreadsheet cells. Also available on github

10.1 Common tricks

To NOT overwrite a value in a cell (when that cell is inside a range you are writing to, set the corresponding array element to null.

To delete the contents of a cell, write an empty string to it. (not null) 

{
  "range": "Sheet1!B1",
  "majorDimension": "COLUMNS",
  "values": [
    [null,"$1","$2", ""],
    [],
    [null,"4/1/2016", "4/15/2016", ""]
  ]
}

values = [
    [
	# Cell values ...
    ],
    # Additional rows ...
]
body = {
    'values': values
}
result = service.spreadsheets().values().update(
    spreadsheetId=spreadsheet_id, range=range_name,
    valueInputOption=value_input_option, body=body).execute()
print('{0} cells updated.'.format(result.get('updatedCells')))

newrow = [['Sunday', 'Sunday', 6252, 7232, 321112, 626, 5.1, 72.1], ['Sunday', 'Sunday', 6252, 7232, 321112, 626, 5.1, 72.1], ['Sunday', 'Sunday', 6252, 7232, 321112, 626, 5.1, 72.1], ['Sunday', 'Sunday', 6252, 7232, 321112, 626, 5.1, 72.1]] body = {"values": newrow}

result = sheet.values().update( spreadsheetId=LAMABLESHEETID, range="2021Usage!A2", valueInputOption="USERENTERED", # or RAW body=body).execute()

print(f'{result.get('updatedCells')} cells updated.')

11 Useful links to Sheets documentation.

11.1 Overview

11.2 Values

REST Resource: spreadsheets.values()

Specify the ValueRange like this: 

{
  "range": string,
  "majorDimension": enum (Dimension),
  "values": [
   array
  ]
}

There are these 3 useful methods in spreadsheets.values, plus 7 less common but still useful methods.

  1. append values to end of sheet
  2. get
  3. update

11.3 append values to end of sheet

spreadsheets.values.append from google reference docs 


from googleapiclient import discovery

# TODO: Change placeholder below to generate authentication credentials. See
# https://developers.google.com/sheets/quickstart/python#step_3_set_up_the_sample
#
# Authorize using one of the following scopes:
#     'https://www.googleapis.com/auth/drive'
#     'https://www.googleapis.com/auth/drive.file'
#     'https://www.googleapis.com/auth/spreadsheets'
credentials = None

service = discovery.build('sheets', 'v4', credentials=credentials)

# The ID of the spreadsheet to update.
spreadsheet_id = 'my-spreadsheet-id' 

# The A1 notation of a range to search for a logical table of data.
# Values will be appended after the last row of the table.
range_ = 'my-range'  # TODO: Update placeholder value.

# How the input data should be interpreted.
value_input_option = ''  # TODO: Update placeholder value.

# How the input data should be inserted.
insert_data_option = ''  # TODO: Update placeholder value.

value_range_body = {
    # TODO: Add desired entries to the request body.
}

request = service.spreadsheets().values().append(spreadsheetId=spreadsheet_id, range=range_, valueInputOption=value_input_option, insertDataOption=insert_data_option, body=value_range_body)
response = request.execute()


values = [
    [
	# Cell values ...
    ],
    # Additional rows ...
]
body = {
    'values': values
}
result = service.spreadsheets().values().append(
    spreadsheetId=spreadsheet_id, range=range_name,
    valueInputOption=value_input_option, body=body).execute()
print('{0} cells appended.'.format(result \
				       .get('updates') \
				       .get('updatedCells')))

11.4 get a range of values

spreadsheets.values.get 

from googleapiclient import discovery

# TODO: Change placeholder below to generate authentication credentials. See
# https://developers.google.com/sheets/quickstart/python#step_3_set_up_the_sample
#
# Authorize using one of the following scopes:
#     'https://www.googleapis.com/auth/drive'
#     'https://www.googleapis.com/auth/drive.file'
#     'https://www.googleapis.com/auth/drive.readonly'
#     'https://www.googleapis.com/auth/spreadsheets'
#     'https://www.googleapis.com/auth/spreadsheets.readonly'
credentials = None

service = discovery.build('sheets', 'v4', credentials=credentials)

# The ID of the spreadsheet to retrieve data from.
spreadsheet_id = 'my-spreadsheet-id'  # TODO: Update placeholder value.

# The A1 notation of the values to retrieve.
range_ = 'my-range'  # TODO: Update placeholder value.

# How values should be represented in the output.
# The default render option is ValueRenderOption.FORMATTED_VALUE.
value_render_option = ''  # TODO: Update placeholder value.

# How dates, times, and durations should be represented in the output.
# This is ignored if value_render_option is
# FORMATTED_VALUE.
# The default dateTime render option is [DateTimeRenderOption.SERIAL_NUMBER].
date_time_render_option = ''  # TODO: Update placeholder value.

request = service.spreadsheets().values().get(spreadsheetId=spreadsheet_id, range=range_, valueRenderOption=value_render_option, dateTimeRenderOption=date_time_render_option)
response = request.execute()

11.5 update cells in a range

spreadsheets.values.update 

from googleapiclient import discovery

# TODO: Change placeholder below to generate authentication credentials. See
# https://developers.google.com/sheets/quickstart/python#step_3_set_up_the_sample
#
# Authorize using one of the following scopes:
#     'https://www.googleapis.com/auth/drive'
#     'https://www.googleapis.com/auth/drive.file'
#     'https://www.googleapis.com/auth/spreadsheets'
credentials = None

service = discovery.build('sheets', 'v4', credentials=credentials)

# The ID of the spreadsheet to update.
spreadsheet_id = 'my-spreadsheet-id'  # TODO: Update placeholder value.

# The A1 notation of the values to update.
range_ = 'my-range'  # TODO: Update placeholder value.

# How the input data should be interpreted.
value_input_option = ''  # TODO: Update placeholder value.

value_range_body = {
    # TODO: Add desired entries to the request body. All existing entries
    # will be replaced.
}

request = service.spreadsheets().values().update(spreadsheetId=spreadsheet_id, range=range_, valueInputOption=value_input_option, body=value_range_body)
response = request.execute()

12 google mail Nov 2022

https://stackabuse.com/how-to-send-emails-with-gmail-using-python/

Sending gmail emails can be done using the google offered "app specific password" or the preferred oauth method.

app specific use smtplib and the 16 character long alplhabetic
password password copied from google's website
oauth use gmail API and the oauth process
  described in https://plswiderski.medium.com/

Currently my app is using smtplib.

12.1 Home

13