david-pettifor-nd · October 24, 2018 22:09
diff --git a/Append CSV to Sheet.py b/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()
diff --git a/Authentication.py b/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)
diff --git a/Clear Sheet.py b/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()
diff --git a/Export Specific Sheet as CSV.py b/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()
diff --git a/Get Sheet Data.py b/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
diff --git a/Imports.py b/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
diff --git a/requirements.txt b/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
diff --git a/Setup Instructions b/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"
diff --git a/Upload CSV to Sheet Tab.py b/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()
	# 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()
	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)
	# 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 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()
	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
	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
	# 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
	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"
	# 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()