Can ChatGPT Automate Spreadsheets?

We're going to see if ChatGPT can actually help us automate Google Sheets. I have used ChatGPT over the last few weeks and I actually used it to create a brand new Google sheet add-on.

Go up to Extensions > Add-ons > Get add-ons: Search for Tiny Sheets.

And if you ever are creating Google Sheets and you want to create a new sheet, but you don't want 26 columns and 1000 rows, you want maybe one cell, then you want Tiny Sheets.

If you're an admin, you can install it to everyone. What it does is it allows you to literally create in one button. Create a one by one cell.

What it also allows you to do is delete all of the rows and columns that don't have data in them, that are beyond the data range.

How did I get the idea for Tiny Sheets?

I've been bothered by deleting the extra rows and columns in every sheet I use. One of my small sheet peeves is sheets with unnecessary rows and columns. I constantly find myself deleting un used rows manually. This Google Sheets Add-on does that in one-click.

How did ChatGPT come into this process?

I'm going to come clean. I'll tell you the truth. I have had this idea of an Apps Script that I wanted to create. I thought, "Okay, I know how to get the last row. I know how to get the last column and max rows and max columns, and then just delete from there. I knew how to write that script."

When I went to ChatGPT, I thought, “I wonder what it will do.” and I said, “Write a Google Apps Script that will delete all rows and columns without data.”

ChatGPT and the code

I want to show you how to use ChatGPT and the code in here, how to accurately read it and use it in your sheet. So if you have a business process, if you need something automated there are a couple more things than just the code.

I actually cover that in a course on Udemy called Spreadsheet Automation 101. It's $99 right now for three hours of lessons, in 29 modules. Everything you need to know about reading and writing, Apps Script, and then also Triggers, which is how you automate Google Sheets.

Going back to our topic...

All right, so this is telling us how to use the script, but let's go and read it and see if we can decode it.

The first thing it says is this:

function deleteEmptyRowsAndColumns()

Why this is all one word is because a function needs one-word function, but what you can do is use camel case. The camel case has every word is capitalized, but there are no spaces.

The key point out of this that I got was this data range. Now in Google Sheets there are about 501 formulas. You can go into a sheet and type in “=” and there's 501 of those. But functions in Google Apps Script, there are like thousands because it's really like a flavor of JavaScript.

So it was this get data range that I found out from ChatGPT. What I see here is:

• It’s a function and that’s correct.
• We need the variable, the sheet that we’re on is the active sheet. That’s correct.
• The range that we want this, getDataRange().
What it's gonna do is it gets the last row that has data and the last column that has data, and it creates a range.
• getValues()

I don't think it needs this getValues() to delete them. I wonder what this does. Let's see.

It's hard to see what's going on here because in ChatGPT, it doesn't highlight the syntax correctly. So we're going to copy this code then we’re going to Apps Script.

What you want to do is you'll want to take everything that's in the code editor, which is just going to be function by function. You can delete that and then copy- paste. Now we have we have the syntax and the coloring.

I'm going to save this save project. The first time that it saves, it takes a bit of a moment or two. After that, it won't take as long.

It says “Delete empty rows” and it has a four loop, which is in Spreadsheet Automation 101. I talk about how to read this four loop there.

You don't really need to do all of this code, but in Automation 101, I go through how to write and read a four loop. We use four loops there, and it has a four loop inside one, which says “j” here.

This one is more particular, but I don't know why this var is here. Usually you just have to do i=0; i < values, whatever is linked. It has the [i] in here, in row 10. And if row is empty equals false. And then deleteRow.

Okay. So this is probably going to work because it just goes through each row. Good.

This sort of will do what it says it'll do. It'll delete empty columns and empty rows, but sometimes you don't want to do it. You don't want to delete all the empty stuff. You want to delete all the unnecessary ones outside of the data range.

That's what Tiny Sheets does. Let's say we have this data and row 8 is empty. We don't actually want to delete this row, we just want to delete row 15 and down. And we want to delete column D across to the right.

So if we go up to Extensions --> Tiny Sheets --> Delete Rows/Columns outside of Data, it just deletes the rows and columns that are outside your data range.

This code from ChatGPT did exactly what I said in the prompt:

Let's write this again. Let's try to say more specifically what I want to do outside: “write a google apps script that will delete all rows and columns outside of the range of data.”

Let's see if it does it here:

This is doing it perfectly. We'll copy this code and again, we'll go to our Apps Script and save it.

So now in reading this, here's a couple of interesting things about this code.

Again, I go through the code. What are variables? What are functions? I go through that in Spreadsheet Automation 101 on Udemy. Go check it out.

Now, this NumRows here and this NumColumns there, if you notice, they're a little lighter, they're not as black. That's because they're not used. So this code exists here, but never again.

That's usually a bad thing about code. If you create variables and then don't use them, it's unnecessary lines of code.

So this is very interesting and what I want to do is I would like to impart to you that I don't think ChatGPT is taking any of our jobs. I think it's going to allow you to do more in spreadsheets.

How do I automate this script daily?

That's actually wrong. I think it’s using the old instructions from the old UI.

Google Sheets has changed their UI. That's something that ChatGPT will probably fix in the coming years, the information that's within them.

But I'll show you how to do a quick trigger.

If we run this code here, deleteExtraRowsAndColumns, and then we want to do that say every month or every day. Let start by going to Apps Scripts. Over here on the left are five options. Click on “Triggers.”

On the bottom right, click on the blue button, “Add Trigger.”

We've chosen the function to run, deleteExtraRowsAndColumns. The head is the same. You just have to change the “Select event source.” Change that to time-driven.

Now here's a little bit of weirdness. You have to choose “Day timer.” Now you're going to get a specific time, but you're going to get an hour to choose from.

Out of midnight to midnight, you get to choose one hour.

Now, how this will run is once you select it, the next time that hour comes around, it will do it at a random time. It’s Google servers. They're going to run it at a random time between that hour, but then after that it should run every 24 hours.

So that’s a little bit of a weirdness. You can't select the exact time to run it, but it gives you an hour, which is for almost everything we do in Google Sheets. I think that's totally fine.

Just hit “Save” and then it will ask you for authorization. That's fine if we have some spreadsheet app or something in there that we need to authorize. If you've already run it, you won't get that authorization screen.

Now you have a time-based trigger. This will always run. It will give you an error. It will also give you error rates if they're errors, and you can also delete it. You can also delete the trigger and edit it if you want to change the time.

If you have any questions, put them in below because I'm just sharing with you what I saw in ChatGPT: That it was working, it was possible to create Apps Scripts and Google Apps Scripts from ChatGPT.

Use them in our sheet directly. Copy-paste them, try it out. That's pretty cool and I'm very excited that this is going to expand the possibilities of you using Apps Script that you can write the Apps Script by just asking ChatGPT.

Again, if you want to understand, read, and figure out what's going on in Apps Script and how to automate your Google Sheets, look at more triggers, look at more options. I go through some really cool little projects.

Go check out Spreadsheet Automation 101. Three hours to watch all of the videos. That’s very little time to get through it. It's a great introduction to coding and working through problems and automating your business practices.

Also, if you want to check out more mindset stuff about how to think about script or you’re struggling through learning to code or you’re copy-pasting a lot of code, check out the video tutorials below. I think it's going to be very good for you.

Watch the video for this tutorial:

Check out other cool things you can do with Google Sheets:

Get more from Better Sheets

I hope you enjoyed this tutorial! If you want to do more with your Google Sheets, I have other tutorials, like how to create a timer with Apps Script and learning to code with Google Sheets. Beginner? Intermediate? There’s a lot of tutorials for everybody! Check them out at Bettersheets.co.

$19/month for instant access to a growing library of Google Sheets Tutorials.the

Don’t make any sheets. Make Better Sheets.