Learn how to use Python to make most of Google products. Let's start with a simple use case: uploading files to GDrive automatically.

Python is extremely useful at work, especially when your organization is subscribed to G Suite apps for Business. Spending hours uploading files one-by-one to about 500 folders is too much, so I just let Python do this task for me.

Steps covered in Part 1:

  • Step 1. Establish connection to GDrive and GSheet APIs: Click here .
  • Step 2. Write the file to GDrive as Google Sheet: Click here

Update: Part 2 is now available: Click here

What you'll need:

  1. Files for upload: For this tutorial, we will use the Excel files we made from the previous tutorial.
  2. Google Spreadsheet for tracking: We'll use the file to list all the folders created and uploaded in GDrive - Tutorial available here!

Image: an Excel file for upload

Step 1: Establish connection to GDrive and GSheet APIs.

Google's documentation is pretty much straightforward. Visit this page and follow the instructions.

First, visit the Google's Developer console home page, and create a new project. In the screenshot below, I've already created this project named Pigeon.

Then create your credentials: Select OAuth client ID, and "Other" as application type.

Download the json file and make sure you don't share it to anyone! :)

Now, copy paste this Python script in your IDE. The script came from here. I modified this a bit to include Google Drive and Google Spreadsheet applications. 

from apiclient.discovery  import build  
from httplib2 import Http  
from oauth2client import file, client, tools  
from oauth2client.contrib import gce  
from apiclient.http import MediaFileUpload

CLIENT_SECRET = "/Users/eyana.mallari/Projects-Local/client_secret.json"

SCOPES='https://www.googleapis.com/auth/drive', 'https://www.googleapis.com/auth/spreadsheets'  
store = file.Storage('token.json')  
creds = store.get()  
if not creds or creds.invalid:  
    flow = client.flow_from_clientsecrets(CLIENT_SECRET, SCOPES)
    creds = tools.run_flow(flow, store)
SERVICE = build('drive', 'v3', http=creds.authorize(Http()))  
SS_SERVICE = build('sheets', 'v4', http=creds.authorize(Http()))

Run your python3 script, and you must be redirected to this page:

Click your profile.

Then allow your Python scripts to access to your Google account.

If you see this message, you have succeeded in this step!

Back to top


Step 2: Write the file to GDrive as Google Sheet.

Now, we proceed to the exciting part of uploading tons of Excel files to Google Drive.

Here's the whole script: uploadtogdrive.py Feel free to clone this git repo and play with the code.

Read further if you want a step by step guide on the code.

By the end of this step, you will be able to upload one (1) Excel file to GDrive programmatically (We'll cover the multiple uploads on part 2!).

Let's start with the createGDriveFolder method to create a GDrive folder for each sales rep: 

# ---------------------------------------
# GDrive API: Create New Folder
# ---------------------------------------
def createGDriveFolder(filename,parent):  
    file_metadata = {'name': filename,'parents': [parent],
    'mimeType': "application/vnd.google-apps.folder"}

    folder = SERVICE.files().create(body=file_metadata,
                                        fields='id').execute()
    print('Upload Success!')
    print('FolderID:', folder.get('id'))
    return folder.get('id')

I also created a boolean method called folderinGDrive to tell me if a Sales Rep has a folder already. 

# ------------------------------------
# GDrive API: Check if Folder exists
# ------------------------------------
def folderInGDrive(filename):  
    results = SERVICE.files().list(q="mimeType='application/vnd.google-apps.folder' and name='"+filename+"' and trashed = false and parents in '"+PARENT_FOLDER+"'",fields="nextPageToken, files(id, name)").execute()
    items = results.get('files', [])
    if items:
        return True
    else:
        return False

And a string method called the getFolderfromGDrive to return the folder ID of the sales rep:

def getFolderfromGDrive(folder_name):  
# Main Folder

    results = SERVICE.files().list(q="mimeType='application/vnd.google-apps.folder' and name='"+folder_name+"' and trashed = false and parents in '"+PARENT_FOLDER+"'",fields="nextPageToken, files(id, name)").execute()

    items = results.get('files', [])

    #print(items)

    if not items:
        return ""
    else:
        print(items[-1]['name'])
        return items[-1]['id']

Next is the the writetoGDrive method. This basically uploads the excel file as Google Spreadsheet to the Google Drive.

The three parameters used are:

  • filename: your assigned filename to your Excel file .
  • source: the absolute directory / file path of the Excel file in your computer
  • folder id: the GDrive ID of the destination folder. You can dynamically get this from the getFolderfromGDrive method
# ---------------------------------------
# GDrive API: Upload files to Google Drive
# ---------------------------------------
def writeToGDrive(filename,source,folder_id):  
    file_metadata = {'name': filename,'parents': [folder_id],
    'mimeType': 'application/vnd.google-apps.spreadsheet'}
    media = MediaFileUpload(source,
                            mimetype='application/vnd.ms-excel')

    if fileInGDrive(filename) is False:
        file = SERVICE.files().create(body=file_metadata,
                                            media_body=media,
                                            fields='id').execute()
        print('Upload Success!')
        print('File ID:', file.get('id'))
        return file.get('id')

    else:
        print('File already exists as', filename)

The methods may be a bit choppy and incoherent at this point. But no worries, in the next steps, we are going to orchestrate these in a loop.

But with these methods, you can already do a basic upload.

Stay tuned for the parts 2 and 3 of this tutorial! - EM

Back to top