Google Drive/Sheets API

Append CSV to Sheet.py

# open the CSV file
csv_file = open(CSV_LOCATION, 'r')
csv_reader = csv.reader(csv_file)

values = []
for row in csv_reader:
    values.append(row)

body = {
    'values': values
}

# NOTES:
#   - 'range': Start with the sheet's title to specify which sheet/tab to write these values to, followed by a '!', and then the starting cell ('A1' for the top-left cell).  Leave as 'A1' for default sheet/tab (first one).
#   - 'valueInputOption': 'RAW' forces it to not interpret the text you insert.  See: https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
#   - 'insertDataOption': 'INSERT_ROWS' forces it to append to the bottom of the table.  You can also 'OVERWRITE' which essentially is just a write command.  
#                           It is safe to leave the `range` set to 'A1' if this is set to 'INSERT_ROWS' verses 'OVERWRITE'.
#                           See: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append#InsertDataOption
response = service.spreadsheets().values().append(spreadsheetId=GOOGLE_FILE_ID, range=sheets[SHEET_TAB_INDEX_0]['title']+'!A1',
                                                 valueInputOption='RAW',
                                                 insertDataOption='INSERT_ROWS', body=body).execute()

Authentication.py

flow = client.flow_from_clientsecrets(
    CREDENTIALS_JSON_FILE,
    scope='https://www.googleapis.com/auth/drive',
    redirect_uri='urn:ietf:wg:oauth:2.0:oob')

auth_uri = flow.step1_get_authorize_url()
webbrowser.get('firefox').open(auth_uri)

auth_code = raw_input('Enter the auth code: ')

credentials = flow.step2_exchange(auth_code)
http = httplib2.Http()
credentials.authorize(http)
service = discovery.build('sheets', version='v4', credentials=credentials)
drive_service = discovery.build('drive', 'v2', http=http)

Clear Sheet.py

# NOTE: Clears only entered values, but preserves cell format.
requests = []
requests.append({
        "updateCells": {
            "range": {
                "sheetId": sheets[SHEET_TAB_INDEX_0]['sheetId']
            },
            'fields': 'userEnteredValue'
        }
})

body = {
    'requests': requests
}
response = service.spreadsheets().batchUpdate(spreadsheetId=GOOGLE_FILE_ID,
                                              body=body).execute()

Export Specific Sheet as CSV.py

# Export the file as a micosoft spreadsheet first - this the only way to get all "sheets" of the workbook
# Other formats supported: https://developers.google.com/drive/v3/web/manage-downloads#downloading_google_documents
request = drive_service.files().export_media(fileId=GOOGLE_FILE_ID,
                                             mimeType='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')

fh = open(DOWNLOAD_LOCATION_XLSX, 'w')
downloader = MediaIoBaseDownload(fh, request)
done = False
while done is False:
    status, done = downloader.next_chunk()
    # print "Download %d%%." % int(status.progress() * 100)
fh.close()

# now that it's downloaded and we know what sheet to look for, let's extract the sheet and and export as a CSV
book = get_data(DOWNLOAD_LOCATION_XLSX)
target_sheet = book[sheets[SHEET_TAB_INDEX_0]['title']]

# now we can dump it as a CSV
csv_file_path = os.path.join(EXPORT_LOCATION, sheets[SHEET_TAB_INDEX_0]['title'] + '.csv')
csv_output = open(csv_file_path, 'w')
csv_writer = csv.writer(csv_output)

for row in target_sheet:
    csv_writer.writerow(row)

csv_output.close()

Get Sheet Data.py

doc = service.spreadsheets().get(spreadsheetId=file_id).execute()
sheets = [s['properties'] for s in doc['sheets']]

sheets[SHEET_TAB_INDEX_0] # JSON Object/Dict of meta data

Imports.py

from pyexcel_xlsx import get_data
from apiclient import discovery
from oauth2client import client
import webbrowser
import httplib2
from googleapiclient.http import MediaIoBaseDownload
import csv

requirements.txt

# pip install these bad boys:
et-xmlfile==1.0.1
google-api-python-client==1.6.7
httplib2==0.11.3
jdcal==1.4
lml==0.0.1
oauth2client==4.1.2
odfpy==1.3.6
openpyxl==2.5.3
pyasn1==0.4.2
pyasn1-modules==0.2.1
pyexcel-io==0.5.7
pyexcel-xlsx==0.5.6
rsa==3.4.2
six==1.11.0
uritemplate==3.0.0

Setup Instructions

1. Go to the Google Drive Console: https://console.developers.google.com/start/api?id=drive
2. Make sure the option "Create a project" is selected and click "Continue"
3. Click "Go to Credentials"
4. Click the "Cancel" button
5. Click the "OAuth consent screen" tab.  Select an email address and give a Product name.
6. Click "Save"
7. On the "Credentials" tab, click "Create Credentials" and select "OAuth client ID"
8. Under "Application Type" select "Other" and give it a name (Example: Drive API Script) and click "Create"
9. On the next window, click "OK" to dismiss the ID/Passphrase.
10. On the row with the credentials you just named, click the Download button (far-right side of the row).  This will
    download the JSON file needed for this script to run.
11. Update the "CREDENTIALS_JSON_FILE" variable below to contain the path of the downloaded JSON file.
12. Enable the Google Sheets API here: https://console.developers.google.com/apis/library/sheets.googleapis.com/ and click "ENABLE"

Upload CSV to Sheet Tab.py

# Recommended to clear the sheet first...(see "Clear Sheet.py")
# open the CSV file
csv_file = open(CSV_FILE, 'r')
csv_reader = csv.reader(csv_file)

# generate the values we'll upload
values = []
for row in csv_reader:
    values.append(row)

body = {
    'values': values
}

# NOTES:
#   - 'range': Start with the sheet's title to specify which sheet/tab to write these values to, followed by a '!', and then the starting cell ('A1' for the top-left cell).  Leave as 'A1' for default sheet/tab (first one).
#   - 'valueInputOption': 'RAW' forces it to not interpret the text you insert.  See: https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
result = service.spreadsheets().values().update(
    spreadsheetId=GOOGLE_FILE_ID, body=body, range=sheets[SHEET_TAB_INDEX_0]['title']+'!A1', valueInputOption='RAW').execute()