Learn Data Science for Business

Become a Data Scientist in our online courses

Earn 6 figures or more in 6 months or less by learning R, Shiny, Machine Learning, Time Series, Web Apps, AWS, Cloud, and more!

5-10 Hours Per Week. 80/20 Skills. End-To-End Business Projects.

Start Your Courses Now! 👉

Join over 100,000+ Data Scientists

DATA SCIENCE CHEAT SHEETS FOR FREE!

Choose your interest:

  • Interested in R
  • Interested in Python
  • Interested in Segmentation
  • Interested in Machine Learning

Click Here to Download!

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

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.

Tip 1: Specify your goal and which tools to use

This might seem obvious, but 9 out of 10 times when I find myself wasting time on debugging, it’s self inflicted.

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.

Tip 2: Ask it to format your script for you

One issue that you can run into is that ChatGPT is designed to explain its 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.

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 sometimes, 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

The first step is to setup a blank google sheet, and to write data to the sheet To do so:

  1. Load the googlesheets4 library
  2. Authenticate to your Google account with gs4_auth()
  3. Create a new sheet with gs4_create()
  4. 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:

  1. Finding the URL of the sheet from your web browser
  2. Extracting the ID from the sheet URL
  3. Getting the sheet with gs4_get()
  4. 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.

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!
P.S. - Samantha landed her NEW Data Science R Developer job at CVS Health (Fortune 500). This could be you.

Become a 6-Figure Data Scientist

We've helped thousands of students become 6-figure data scientists.

Make 6-figures. Launch your career. And become a Data Scientist!
Start Your Classes Now! 👉