ChatGPT: How to automate Google Sheets in under 2 minutes (with R)
Written by Matt Dancho
Hey guys, welcome back to my R-tips newsletter. In today’s R-tip, I’m sharing a super common data science task (one that saved me 20 hours per week)… You’re getting the cheat code to automating Google Sheets. Plus, I’m sharing exactly how I made this automation in under 2 minutes. AND how you can do it for ANY company (using ChatGPT and R). Let’s go!
Table of Contents
Today I share how to automate Google Sheets with ChatGPT + R. Here’s what you’re learning today:
- 3 Tips for Better ChatGPT Prompts: The mistake you’ll make and how to get ChatGPT to write your code correctly.
- 2-Minute Case Study: How I connect to Google Sheets, Upload Data, and Create Spreadsheets with
ChatGPT + R.
The Google Sheet you create in this tutorial
SPECIAL ANNOUNCEMENT: ChatGPT for Data Scientists Workshop on June 12th
Inside the workshop I’ll share how I built a Machine Learning Powered Production Shiny App with ChatGPT (extends this data analysis to an insane production app):
What: ChatGPT for Data Scientists
When: Wednesday June 12th, 2pm EST
How It Will Help You: Whether you are new to data science or are an expert, ChatGPT is changing the game. There’s a ton of hype. But how can ChatGPT actually help you become a better data scientist and help you stand out in your career? I’ll show you inside my free chatgpt for data scientists workshop.
Price: Does Free sound good?
How To Join: 👉 Register Here
R-Tips Weekly
This article is part of R-Tips Weekly, a weekly video tutorial that shows you step-by-step how to do common R coding tasks. Pretty cool, right?
Here are the links to get set up. 👇
This Tutorial is Available in Video
I have a companion video tutorial that walks you through how to use ChatGPT + R + googlesheets4 for this data automation. 👇
How to get ChatGPT to write your code correctly
I’ll be honest. ChatGPT is like an intern.
- Like an intern, ChatGPT does what you ask it to do. (Sometimes)
- Sometimes the work is good.
- Other times it “hallucinates”.
- But if you don’t really know what your asking. It’s just going to get you 💩 results fast.
- And if you don’t check your intern’s work, well, good luck.
To help I’ve put together a few tips on ChatGPT Prompting as I was creating this R tip on automating Google Sheets with ChatGPT and R. My hope is this helps you speed through the learning curve with ChatGPT. If you’d like more help, I have a free LIVE workshop: ChatGPT for Data Scientists (Limit 500 seats).
This might seem obvious, but 9 out of 10 times when I find myself wasting time on debugging, it’s self inflicted.
Why? Because I wasn’t specific enough with my ChatGPT prompt.
Being specific does not mean complicated. If you look at this prompt above, it’s very simple. And the result gave me insights on how to create a Google Sheet with R.
- The goal: Make a google sheet
- The tool: R
ChatGPT’s response was pretty good. It correctly picked out the googlesheets4 package. And gave me code to create a new sheet.
One issue that you can run into is that ChatGPT is designed to explain it’s steps. That’s great when you’re learning. But usually you just want to test the code out. So ask it to prepare your code. Here’s how.
This format is a lot easier to test. And like I said, the intern’s much faster than I am at writing code.
But how good is it?
Tip 3: Check Your Intern’s Work
One of the big problems with ChatGPT’s coding is it’s far from error free. In fact, I often find myself spending a lot of time debugging.
Unfortunately, I don’t have a simple solution for debugging. ChatGPT can help some time, but often it’s just trial and error and searching for the solution.
This time all I needed to do was investigate the googlesheets4 package, and I found a function range_write() that solved the issue.
Ok. With an understanding of how to prompt with ChatGPT, let’s check out how to automate Google Sheets with R.
Tutorial: How to Automate Google Sheets in R
This tutorial is awesome. You’ll learn how I made the Google Sheets Automation in under 2 minutes with R + ChatGPT.
Step 1: Setup a Sheet and Write Data
Get the Code.
The first step is to setup a blank google sheet, and to write data to the sheet To do so:
- Load the
googlesheets4 library
- Authenticate to your Google account with
gs4_auth()
- Create a new sheet with
gs4_create()
- Then create a data frame and use
write_sheet() to add a new sheet or overwrite an existing sheet
Step 2: Use an Existing Sheets ID (or URL) and Write Data
The next thing you might want to automate are updates to your Google Sheet. You can do so by:
- Finding the URL of the sheet from your web browser
- Extracting the ID from the sheet URL
- Getting the sheet with
gs4_get()
- Writing the data to a new or existing sheet with
write_sheet()
There you have it, you’ve just automated the creation and update of your first Google Sheet.
Get the Code
Get the Code.
If you want all of the code you saw here, just subscribe to the R-Tips Newsletter. The code shown is in the folder: 068_chatgpt_googlesheets.
Want to Learn ChatGPT for Data Science from me LIVE?
I have good news…
SPECIAL ANNOUNCEMENT: ChatGPT for Data Scientists Workshop on June 12th
Inside the workshop I’ll share how I built a Machine Learning Powered Production Shiny App with ChatGPT (extends this data analysis to an insane production app):
What: ChatGPT for Data Scientists
When: Wednesday June 12th, 2pm EST
How It Will Help You: Whether you are new to data science or are an expert, ChatGPT is changing the game. There’s a ton of hype. But how can ChatGPT actually help you become a better data scientist and help you stand out in your career? I’ll show you inside my free chatgpt for data scientists workshop.
Price: Does Free sound good?
How To Join: 👉 Register Here
Need to advance your business data science skills?
I’ve helped 6,107+ students learn data science for business from an elite business consultant’s perspective.
I’ve worked with Fortune 500 companies like S&P Global, Apple, MRM McCann, and more.
And I built a training program that gets my students life-changing data science careers (don’t believe me? see my testimonials here):
6-Figure Data Science Job at CVS Health ($125K)
Senior VP Of Analytics At JP Morgan ($200K)
50%+ Raises & Promotions ($150K)
Lead Data Scientist at Northwestern Mutual ($175K)
2X-ed Salary (From $60K to $120K)
2 Competing ML Job Offers ($150K)
Promotion to Lead Data Scientist ($175K)
Data Scientist Job at Verizon ($125K+)
Data Scientist Job at CitiBank ($100K + Bonus)
Whenever you are ready, here’s the system they are taking:
Here’s the system that has gotten aspiring data scientists, career transitioners, and life long learners data science jobs and promotions…
Join My 5-Course R-Track Program Now!
(And Become The Data Scientist You Were Meant To Be...)
P.S. - Samantha landed her NEW Data Science R Developer job at CVS Health (Fortune 500). This could be you.
