How to Replace Your MS Excel Macros With Cloud Functions in Python on Google Sheets

Macros embedded within Excel can often be confusing to work with. To solve this issue, you can instead replace your MS Excel macros with cloud functions.

In four steps, we have created a cloud function in Python that talks back and forth through the cloud using Google Sheets. First, for clarity, let’s quickly define some terms:

• Python: A programming language that is known for being a clean, easy to program syntax
• Cloud Function: A way to execute code without all of the infrastructure requirements, similar to how you would on your laptop, but in the cloud (in this case, Google’s servers)
• Google Sheets: A cloud-hosted spreadsheet within G Suite that you can access through your browser.

Prerequisites

You will need 3 things:

• a web browser with an internet connection
• a Gmail account
• a Google Cloud Platform account

In my opinion, it’s not necessary to learn Python if you have already learned Excel Macros. For this purpose, you should be able to easily learn what you need to know in Python on your own.

4 Easy Steps

As an overview, we will do the following:

• Create a Google Sheet
• Create a Script to call our Cloud Function
• Create a Cloud Function in Python
• Finish the Script to write back to the Sheet

1) Create a Google Sheet

1. Open Google Sheets
2. Hit the “+”
3. Populate the Cells A1 with “x”, A2 with 1, B1 with “y”, B2 with “2” and C1 with “z”
4. Name your sheet

2. Create a Script to call our Cloud Function

Go to “Tools” then “<> Script Editor” from the menu.

Rename the project “tutorial” or whatever you wish.
Add the following code:

Then in the Spreadsheet add the function:

If all goes well, C2 should say 3 (or whatever you put in A2 and B2).

3. Create a Cloud Function in Python

Go to the Cloud function listing page. (You may need to first create a project, set up billing, etc. If you need help, go here)

Add the following code to replace the Hello world function:

Set the code to execute to “add_x_y”. Then, hit and the function will be created. You can test the function if desired by going to the “testing” tab: {“x”:1, “y”:1}

Then you will need to grab the URL (endpoint) of the function:

Click the name, then select the Trigger.

4. Finish the Script to write back to the Sheet

Going back to your Google Sheet, you will need to grab that URL.
Open the Script Editor again with “Tools” -> “<> Script Editor”. Replace the code with:

If all goes well, you are done!

Testing

To test the function, first clear the cell, hit <enter|return>, then re-add the function “=add_x_y()” or put it in a new cell. You can improve the script in several ways, such as setting a button to run the scripts.

Bonus Material: integrate sentiment analysis

The Python

Also, add to the requirements.txt tab:

Test it with:

You may need to enable the API here.

Be aware this does have a cost attached to it, but it can be useful for many reasons.

The Script

The Sheet

If you run into any issues or have questions as you are replacing your Excel macros with cloud functions in Python, please reach out to me.

Maven Wave has helped some of the world’s largest enterprises transform their business with G Suite. With the Google Cloud Partner Specialization in Enterprise Collaboration under our belt, we’ve proven our expertise and success in building customer solutions with G Suite. Are you interested in learning how G Suite can transform your business? Contact us now.