How to Create Your Own Web Service with Google Apps Script and Call it from Excel VBA

Ever needed to perform some Web task in service of your Excel VBA application? You can write your own web-service to do whatever you need * Popular use cases: access Google Sheets, Docs, Presentations, email and calendar from your Excel application!

Calling Web-services from Excel VBA

As I thoroughly explain and demonstrate in my online course: Business Automation with VBA: Accessing the Internet, you can send any HTTP request over an Internet connection from VBA using the WinHttp service library. Here’s a code snippet to send a request to some URL:

    Dim winHttpReq As Object

    Dim myURL As String

    Dim result As String

    Dim lngStatus As Long

    Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")

    myURL = "https://some-api-web-service?p1=value1&p2=value2”

        With winHttpReq

            .Open "GET", myURL

            .Send

            .waitForResponse 4000 ‘4 seconds before timeout is raised

            result = .responseText

            lngStatus = .Status

        End With

        If (lngStatus = 200) Then

            ‘Success – we have our returned value in result

        Else

                MsgBox "Web service error", vbOKOnly + vbExclamation, "Process Error"

        End If

Writing your own web-service

The above example calls a published web-service (some-api-web-service) designed to handle two GET parameters: p1 and p2.

Someone implemented a function to handle this request, hosted it on some web-server and published it so that it is listening for HTTP requests over the network (the Internet is the network here, although HTTP protocol is used internally in many organizations’ own closed networks, or LANs).

So, if you want to implement your own web-service, you need to have a web-server hosting your function and you need to implement it in some language that this web-server supports.

Examples of popular technologies for writing such server-side code, include: PHP, ASP and Node.js.

Google Apps Script to the rescue

If you have a Google account, you have a web-server at your disposal for writing and publishing web-services. As simple as that.

The language used for implementing a web-service on Google, is GAS (Google Apps Script). This is Google’s flavor of JavaScript. If you know JavaScript – you can implement any logic you desire already.

As JavaScript is one of the most popular and wanted skills to have, you might as well learn it regardless.

Microsoft Office and Google Office Integration

GAS adds to the standard JavaScript libraries many of Google’s libraries available to access many of the Google objects and services.

For example, you can access your Google Docs, or Sheets or Presentations. You can access your Gmail and Calendar. You can leverage Google security services and many others.

When I say “access”, I mean you can automate almost anything over your emails, documents, meetings, etc. You have access to all of your objects, collections of objects and basically your data.

As you can imagine, the possibilities for integrating your Excel VBA program with Google assets are endless and invaluable!

Moreover, since VBA provides you access to all of the Microsoft Office suite of applications, a Google Web-service can open the door to your Google data, in essence establishing a data tunnel between the two worlds.

Writing your Google Web-service

GAS scripts can be associated with any Google document (just as VBA can be written inside any MS-Office application).

You can start by opening a Google Sheet and calling the GAS editor for this Google Sheet (Tools -> Script editor), or create a standalone script by clicking the Google’s “New” button, expanding the standard Google Docs list with “More >” and selecting Google Apps Script.

A new function stub is offered, but you can delete it. In addition to standard functions you can create here for use with your Google docs, there are two special functions that once published as Web Services, they are listening for HTTP requests to handle.

doGet() – will handle GET type HTTP requests.

doPost() – will handle POST type HTTP requests.

Both receive an argument object that will receive all of the GET/POST parameters you pass with your request.

Google’s ContextService library offers methods for formatting and packaging the returned value with the HTTP response in several popular formats, such as JSON, XML and Text.

Here’s a simple Web Service that receives a single parameter and returns this parameter multiplied by 2:

function doGet(request) {

     result = 2 * request.parameter.mynumber

     return ContentService.createTextOutput(result);

}

When we’re finished writing our function, we need to publish it as a Web-service (Publish -> Deploy as web app). We should assign a new version number each time we publish this function. As this is a Web-service listening for calls over the Internet, set the access to be: Anyone, even anonymous. That way, even our VBA program will have access to call this function.

Before you close this dialog box, be sure to copy the web app URL – this is your HTTP request destination!

In our VBA function above, we can now set our URL for this Web-service like that:

myURL = " https://script.google.com/macros/s/......./exec?mynumber=2”

The result variable should hold the returned value: “4”.

For more information on writing GAS Web-services, including an example of accessing Google Sheet data, read Trevor’s great Blog post here: https://trevorfox.com/2015/03/rest-api-with-google-apps-script/