Google Drive API Using Excel VBA
With Minimal Background in Both Google Drive API v3 and Excel 2016 VBA
Coalmont VBA | Edit | Contact us
How can a program in Excel 2016 VBA interact with files in Google Drive using its API? In an attempt to answer this question, this article walks an absolute beginner from zero background to efficient user of both Google Drive API and Excel 2016 VBA. The complexity of the information presented herein will increase drastically as you leave the first half behind.
What’s in This Article?
- A | What’s an API?
- B | Authentication vs Authorization
- C | What is OAuth?
- D | Owner of Personal Data Resource
- E | Real-time Editing
- F | Client-Server Model
- G | OAuth 2.0 for Client-side Web Applications
- H | The Implicit Grant Flow
- K | Using Google OAuth Playground
- M | A Simple .NET Console Application
- N | Google API Client Libraries
- P | Calling .NET Methods from Excel VBA
A | What’s an API?
The graphical user interface (GUI) is a form of user interface that allows users to interact with electronic devices through graphical icons and audio indicator such as primary notation, instead of text-based user interfaces, typed command labels or text navigation. | Tell me more
Application programming interface (API) a set of functions and procedures allowing the creation of applications that access the features or data of an operating system, application, or other service. | Tell me more
Google Drive is a file storage and synchronization service developed by Google. Launched on April 24, 2012, Google Drive allows users to store files on their servers, synchronize files across devices, and share files. | Tell me more
To use Google Drive using a browser, go to https://drive.google.com/ using your Gmail account. While there, you can use its GUI to upload your files from your computer to your Google Drive. Using its GUI, you can also give your friends access to your folder and its files in your Google Drive.
When an Excel 2016 VBA program needs to do the same and, on behalf of its users, interact with their Google Drive, it has to use the drive’s API instead of its GUI. In simple words, GUI is for human users and API is for machine users.
B | Authentication vs Authorization
Access to a system is protected by authentication and authorization, and they are frequently used in conjunction with each other. Authentication is the process of verifying the user’s identity. After the user’s identity is authenticated, authorization kicks in. Security questions, for example, can add an extra layer of certainty to your authentication process. Security questions are an alternative way of identifying your customers when they have forgotten their password, entered the wrong credentials too many times, or tried to log in from an unfamiliar device or location.
Authorization is the process of verifying the user’s rights allowing appropriate access to the protected resources, be they data, functionality, or both. In some cases, there is no authorization; any user may be use a resource or access a file simply by asking for it. Most of the web pages on the Internet require no authentication or authorization.
Encryption involves the process of transforming data so that it is unreadable by anyone who does not have a decryption key. Encryption is used to protect, for example, a person’s credit card and personal information when it is sent over the Internet to the airline. The company encrypts the customer’s data so that it will be safer from interception in transit. Airports need to authenticate that the person is who he or she says she is and has purchased a ticket, before giving him or her a boarding pass. A flight attendant must authorize a person so that person can then see the inside of the plane and use the resources the plane has to fly from one place to the next. | Tell me more
C | What is OAuth?
Pronounced as oh-auth, OAuth (Open Authorization) is an open standard for access delegation, commonly used as a way for Internet users to grant websites or applications access to their information on other websites but without giving them the passwords. Generally, OAuth provides to clients a “secure delegated access” to server resources on behalf of a resource owner. It specifies a process for resource owners to authorize third-party access to their server resources without sharing their credentials. | Tell me more
OAuth is an authorization protocol, rather than an authentication protocol. It is an open standard protocol for authorization of an application for using user information. Using OAuth on its own as an authentication method may be referred to as pseudo-authentication. The communication flow is explained on this page here.
D | Owner of Personal Data Resource
When you create a Gmail account, you become an owner of your personal data resource. The data resource is information created by, used by or gathered by the data owner in order to conduct day to day business. Data resources are usually stored in databases under a database management system.
Attached to your Gmail account is a limited amount of your personal data resource. To store even more personal data, use Google Drive. In order to use Google Drive, you will need a Google account. Creating a Google account will automatically create a Gmail email. Once you’ve set up your Google account, you can access Google Drive by going to http://drive.google.com in your web browser. As you begin to upload and create files you’ll need to know how to view, manage, and organize them in the interface.
Using your Gmail address, you’re the owner of the personal data resource found inside your Google Drive.
E | Real-time Editing
While using the GUI of Google Drive, you can create a new document using Google Docs format.
You can edit a document in Google Docs format by using the GUI of Google Docs, which is the Google Document Editor (GDE).
You can authorize several other Google account holders to have read/write access to your Google document.
All authorized users can edit the same Google document at the same time. This is referred to as real-time editing.
In this case, Google Docs is the client application while Google Drive is the server application.
F | Client-Server Model
Client–server model is a distributed application structure that partitions tasks or workloads between the providers of a resource or service, called servers, and service requesters, called clients. Often clients and servers communicate over a computer network on separate hardware, but both client and server may reside in the same system. A server host runs one or more server programs, which share their resources with clients. A client does not share any of its resources, but it requests content or service from a server. Clients therefore initiate communication sessions with servers, which await incoming requests. Examples of computer applications that use the client–server model are Email, network printing, and the World Wide Web. | Tell me more
In few words, the Internet primarily uses a client-server model. The web browser is the client. The web server is the server. That a web server is hosted elsewhere (in the cloud) does not change the fact that the relationship between the web browser and the web server is a client-server relationship.
G | OAuth 2.0 for Client-side Web Applications
OAuth 2.0 allows users to share specific data with an application while keeping their usernames, passwords, and other information private. For example, an application can use OAuth 2.0 to obtain permission from users to store files in their Google Drives. | Tell me more
This OAuth 2.0 flow is called the implicit grant flow; see section G, below, or see this web sequence diagram here. It is designed for applications that access APIs only while the user is present at the application. These applications are not able to store confidential information.
In this flow, your app opens a Google URL that uses query parameters to identify your app and the type of API access that the app requires. You can open the URL in the current browser window or a popup. The user can authenticate with Google and grant the requested permissions. Google then redirects the user back to your app. The redirect includes an access token, which your app verifies and then uses to make API requests. |Tell me more
H | The Implicit Grant Flow
K | Using Google OAuth Playground
For a demo, do the following:
- Using the test email, chuckday402@gmail.com, open the Google Drive at https://drive.google.com/drive/u/1/my-drive
- Delete all untitled documents in Chuck’s drive.
- Select the gear button to then select the commands Settings, then Manage apps.
- Scroll around to find “ OAuth 2.0 Playground”; if found, click on the Options button then select “Disconnect from Drive” and disconnect.
- In a new Browser tab, go to the playground at https://developers.google.com/oauthplayground/
- Press on the X button near the top to reset or clear the playground.
- In the first step selecting and authorizing API, scroll down and look for the node “Drive API v3”.
- Expand the node then select the item “https://googleapis.com/auth/drive.file”.
- Press the button [Authorize APIs]
You will be brought to a URL similar to the following:
accounts.google.com/signin/oauth/oauthchooseaccount?client_id=407408718192.apps.googleusercontent.com&as=VGhCt9IFXW4Zf--vu4p47Q&nosignup=1&destination=https%3A%2F%2Fdevelopers.google.com&approval_state=!ChROLVFNYk8zcEJwTm0yRmUwbjZ1VhIfWS1XdGFwb0NlaGdTVUU3MWpGWk5XazFUdTRIaF9SWQ%E2%88%99AJDr988AAAAAXi3i7wq4KiVRVA4-QDzrkh8aTJdqYiYO&xsrfsig=ChkAeAh8T09rBL_pDjMRAaB9WNQFQwH0AualEg5hcHByb3ZhbF9zdGF0ZRILZGVzdGluYXRpb24SBXNvYWN1Eg9vYXV0aHJpc2t5c2NvcGU&flowName=GeneralOAuthFlow
To continue, do the following:
- Do as instructed and sign in with Google; Choose an account to continue to Google OAuth 2.0 Playground. Remember to use chuckday402@gmail.com
- Press the button [Allow] to allow Playground to access your Google test account chuckday402@gmail.com.
- Carry out Playground Step 2 and press the button “ Exchange authorization code for tokens”
- Do the following steps to carry out Playground Step 3.
- Press the button “List possible operations”.
- Select the item “Create documents”.
- Press the button “Send the request”.
Switch to the tab “My Drive — Google Drive” then refresh to see a new document was created in Chuck’s drive.
M | A Simple .NET Console Application
This article here provides a complete sequence of the steps to create a simple .NET console application that makes requests to the Drive API. | Tell me more
- Client-ID for Chuck: 1050525399669–7sq71d4eglrfc0dgi1s997u5abj2dbur.apps.googleusercontent.com
- Client Secret: ZkSiuTEJIpbxX2EV4ADAMI3c
N | Google API Client Libraries
Google API client libraries are accessible via https://developers.google.com/api-client-library.
Most Google services have web APIs that .NET developers can use to give their applications access to this information with the user’s authorization. | Tell me more
To get started with the .NET client library, find the API you want to use and click the desired version number as found on this page here.
To download the library, go to Drive API Client Library for .NET at https://developers.google.com/api-client-library/dotnet/apis/drive/v3.
P | Calling .NET Methods from Excel VBA
This page here shows how to call .NET methods from Excel VBA.
X | Pending Information
The following information is necessary for completion of this article.
All applications follow a basic pattern when accessing a Google API using OAuth 2.0. At a high level, you follow four basic steps as spelled out on this page here.
Every request your application sends to the Drive API must include an authorization token. The token also identifies your application to Google. | Tell me more
