It records a series of steps to save log data such as user activity that accumulates daily or user registration data (CSV) on Google Drive in the form of Google spreadsheets. By using this memo, you can do the following.
--You can learn how to use the Google API, which has only English literature. ――You can learn the difference in specifications between Ruby and Python. ――You can save time by automating the work you do manually every morning. ――You can get a way to efficiently manage data that was always managed locally in the cloud.
And so on, there is only honest merit. The target of this memo is "people who want to know how to automate work in the shortest time" and "people who have tried using Google API but are frustrated by only unfriendly English literature". After reading, we aim for a state where such people ** "somehow understand how to use the Google API" **.
_ ** * Below, the discussion will proceed on the assumption that an environment that can use Ruby or Python has been built. ** _
OS
First of all, you need to register to use the Google API. Please execute according to the following flow.
client_secret.json under~ / .credential /.gitignore and be careful not to include the API keyRuby
$ gem install google-api-client
Python
$ pip install --upgrade google-api-python-client
Create the following script in any directory. Also, please note that the tasks performed by the Ruby version and the Python version below are not 100% the same.
Ruby: uploader.rb
require 'google/apis/drive_v3'
require 'googleauth'
require 'googleauth/stores/file_token_store'
require 'fileutils'
credentials_dir = File.join(Dir.home, '.credentials')
CLIENT_SECRETS_PATH = File.join(credentials_dir, 'client_secret.json')
CREDENTIALS_PATH = File.join(credentials_dir, 'sampleApp.yaml')
OOB_URI = 'urn:ietf:wg:oauth:2.0:oob'
APPLICATION_NAME = 'sampleApp'
SCOPE = Google::Apis::DriveV3::AUTH_DRIVE
class DriveUploader
#Define the path to the folder that contains the CSV file you want to upload
FILE_DIR = 'out/'
#Define the ID of the folder on Google Drive where you want to store the file
#Details are summarized below
FOLDER_ID = '<Your Folder ID>'
MASTER_FILE_ID = '<Your Master FIle ID>'
MASTER_FOLDER_ID = '<Your Master Folder ID>'
def initialize
#Generate API instance
@@drive = Google::Apis::DriveV3
@@service = @@drive::DriveService.new
@@service.client_options.application_name = APPLICATION_NAME
@@service.authorization = authorize
end
def authorize
FileUtils.mkdir_p(File.dirname(CREDENTIALS_PATH))
client_id = Google::Auth::ClientId.from_file(CLIENT_SECRETS_PATH)
token_store = Google::Auth::Stores::FileTokenStore.new(file: CREDENTIALS_PATH)
authorizer = Google::Auth::UserAuthorizer.new(client_id, SCOPE, token_store)
user_id = 'default'
credentials = authorizer.get_credentials(user_id)
if credentials.nil?
url = authorizer.get_authorization_url(base_url: OOB_URI)
puts "Open the following URL in the browser and enter the " +
"resulting code after authorization"
puts url
code = gets
credentials = authorizer.get_and_store_credentials_from_code(user_id: user_id, code: code, base_url: OOB_URI)
end
credentials
end
def upload_csvs
##
#Method to actually upload the file
#In the specified folder"~~.csv"Upload all files named
#Find the file to upload
file_path = File.join(FILE_DIR, '*.csv')
files = Dir.glob(file_path)
abort('No files to upload.') if files.empty?
#Repeat the upload task for all applicable files
counter = 0
files.each.with_index(1) do |a_file, i|
#Name the file"MMDD_FileName"Convert to the format
file_name = Date.today.strftime('%m%d') + a_file.gsub(/out\/\d{4}-\d{2}-\d{2}/,'').gsub('.csv','')
puts "\nUploading[#{i}/#{files.count}]: #{file_name}"
#Define the upload destination
file_metadata = @@drive::File.new(
name: file_name,
mime_type: 'application/vnd.google-apps.spreadsheet',
parents: [FOLDER_ID]
)
#Upload the converted file to the specified folder on Goodle Drive
file = @@service.create_file(file_metadata, upload_source: a_file, content_type: 'text/csv', fields: 'id')
file_id = file.id
puts "Successfully uploaded as:\nhttps://docs.google.com/spreadsheets/d/#{file_id}\n"
counter += 1
end
#Output result
puts "\n\nTotal: #{counter.to_s} Files Uploaded\n"
end
def copy_master
##
#Copy the master file such as daily reports that you want to copy on Google Drive at the same time as uploading
#The master file is already defined as a constant at the beginning
master_file_metadata = @@drive::File.new(
name: "#{Date.today.strftime('%m%d')}_Dashboard",
parents: [MASTER_FOLDER_ID]
)
master_file = @@service.copy_file(MASTER_FILE_ID, master_file_metadata)
puts "\nSuccessfully created as: #{master_file.name}\nhttps://docs.google.com/spreadsheets/d/#{master_file.id}"
end
end
#This script from the terminal[./uploader.rb]Description to execute with the command
if __FILE__ == $0
puts "Start uploading reports to Drive..."
DriveUploader.new.upload_csvs
puts "Copying master file in Drive..."
DriveUploader.new.copy_master
end
Python: uploader.py
import httplib2
import os
import sys
import time
import glob
from apiclient import discovery
import oauth2client
from oauth2client import client
from oauth2client import tools
from apiclient.http import MediaFileUpload
CREDENTIAL_DIR = os.path.join(os.path.expanduser('~'), '.credentials')
CLIENT_SECRET_FILE = os.path.join(CREDENTIAL_DIR, 'client_secret.json')
CREDENTIAL_PATH = os.path.join(CREDENTIAL_DIR, 'piwikReport.json')
APPLICATION_NAME = 'piwikReport'
SCOPES = 'https://www.googleapis.com/auth/drive'
#Define the path to the folder that stores the CSV file you want to upload
FILE_DIR = 'out/'
#Define the ID of the folder on Google Drive where you want to store the file
#Details are summarized below
FOLDER_ID = '<Your Folder ID>'
MASTER_FILE_ID = '<Your Master FIle ID>'
MASTER_FOLDER_ID = '<Your Master Folder ID>'
class DriveUploader(object):
def __init__(self):
"""
Authenticate using credentials that you have already downloaded and saved
"""
self.credentials = self.get_credentials()
self.http = self.credentials.authorize(httplib2.Http())
self.service = discovery.build('drive', 'v3', http=self.http)
def get_credentials(self):
"""
Check if API authentication has already been completed
"""
if not os.path.exists(CREDENTIAL_DIR):
os.makedirs(CREDENTIAL_DIR)
store = oauth2client.file.Storage(CREDENTIAL_PATH)
credentials = store.get()
if not credentials or credentials.invalid:
flags = tools.argparser.parse_args(args=[])
flow = client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
flow.user_agent = APPLICATION_NAME
if flags:
credentials = tools.run_flow(flow, store, flags)
else:
# Python2.Description for users using 6
credentials = tools.run(flow, store)
print('Storing credentials to' + CREDENTIAL_PATH)
return credentials
def upload_csvs(self):
"""
Method to actually upload the file
In the specified folder"~~.csv"Upload all files named
"""
#Search for files to upload
file_path = os.path.join(FILE_DIR, '*.csv')
files = glob.glob(file_path)
if not files:
print('No files to upload.')
sys.exit()
#Repeat the task of renaming and uploading all the files
counter = 1
for a_file in files:
#Name the file"MMDD_FileName"Convert to the format
file_name = time.strftime('%m%d') + a_file.replace('out/','_').replace('.csv','')
print('>>>\nUploading[' + str(counter) + '/' + str(len(files)) + ']: ' + file_name)
#Confirm the upload destination
file_metadata = {
'name' : file_name,
'mimeType' : 'application/vnd.google-apps.spreadsheet',
'parents' : [FOLDER_ID]
}
#Upload CSV data converted to spreadsheet format to the already defined Google Drive folder
media = MediaFileUpload(a_file, mimetype='text/csv', resumable=True)
file = self.service.files().create(body=file_metadata, media_body=media, fields='id').execute()
file_id = file.get('id')
print('Successfully uploaded as:\nhttps://docs.google.com/spreadsheets/d/' + file_id)
counter += 1
#Output result
print('--------------\nTotal: '+ str(counter - 1) + ' Files Uploaded')
def copy_master(self):
"""
Copy the master file such as daily reports that you want to copy on Google Drive at the same time as uploading
The master file is already defined as a constant at the beginning
"""
master_file_metadata = {
'name' : (time.strftime('%m%d') + '_PiwikReport'),
'parents' : [MASTER_FOLDER_ID]
}
master_file = self.service.files().copy(fileId=MASTER_FILE_ID, body=master_file_metadata, fields='id, name').execute()
print('Successfully created as: ' + master_file.get('name') + '\nhttps://docs.google.com/spreadsheets/d/' + master_file.get('id'))
#This script from the terminal[python3 uploader.py]Description to execute with the command
if __name__ == "__main__":
DriveUploader().copy_master()
Next, I will explain the constants in the code.
CREDENTIAL_DIR
--Path to the local folder where the downloaded credentials are storedCLIENT_SECRETS_PATH
--Path to downloaded credentials fileCREDENTIALS_PATH
--The path of the file that records the authentication information once the API authentication is successfulOOB_URI
--Information required for API authenticationAPPLICATION_NAME
--The name of the file or applicationSCOPE
--Setting the range that the API can access the accountFILE_DIR
--The path to the local folder where the CSV file you want to upload is savedFOLDER_ID
--ID of the folder where you want to upload CSV data in spreadsheet format
--ID is a list of alphanumeric characters at the end of the URL of the folder.
--If the URL of the folder is https://drive.google.com/drive/u/0/folders/7hu9HyWxhuga563WV2xxxxGd, the last part 7hu9HyWxhuga563WV2xxxxGdMASTER_FILE_ID
--Master file ID such as daily report that you want to copy on Google Drive at the same time you upload CSV data
--ID is a list of alphanumeric characters at the end of the URL of the file.
--If the URL of the file is https://docs.google.com/spreadsheets/d/1vm5eyjJpuXXXhogefjduaJGeoSQSjJfs2FBpiyoHige/edit, the part of 1vm5eyjJpuXXXhogefjduaJGeoSQSjJfs2FBpiyoHige just before the endMASTER_FOLDER_ID
--ID of the folder you want to keep after copying the master file such as daily report
--ID is a list of alphanumeric characters at the end of the URL of the folder.
--If the URL of the folder is https://drive.google.com/drive/u/0/folders/7hu9HyWxhuga563WV2xxxxGd, the last part 7hu9HyWxhuga563WV2xxxxGdMake sure that you are in the same directory as the above script, and then execute the following command.
Ruby
$ ruby uploader.rb or $ ./uploader.rb
Python
$ python3 uploader.py
At the first time, a screen such as "Do you want to authenticate?" May appear, but you can allow all of them. If you really care about it, you can always change it later in the settings. Also, delete the local CSV file as soon as the upload is complete.
that's all. This memo was the fastest strategy article to send to those who advocate work automation and cloud computing using Google APIs. In the first place, when I was a user data analyst overseas, I was forced to do it, and I had the experience of doing it while becoming a hero in front of English and unfriendly documents. If you are fluent in English, you may want to visit the reference sites listed below. I hope that many of the people who are forced to do inefficient and unproductive work in the world will be happy through this article.
Ruby
Python
Recommended Posts