Iterate Numbers with a Simple Apps Script

Beginner

About this Tutorial

You're joining a company and they have some Google Sheets already created, and there's a sheet that has a cell in A1, this number, and you want to iterate it to 3, and B1 is connected to a lot of other stuff, so you don't want to mess with it. All you want to do is iterate a one, one at a time. I think the answer here is an Apps Script.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>0:00 So here's an interesting problem I just ran into. Say you are joining a Google Sheet, maybe someone shared it with you, or you're joining a company and they have some Google Sheets already created, and there's a sheet that has cell in A1, this number , and you want to iterate it to 3, and B1 is connected 0:17 to a lot of other stuff, so you don't want to mess with it. All you want to do is iterate a one, one at a time. 0:25 And you don't want to have to do this with so many clicks. Right?

I think it's like three or four clicks I'm doing right now. 0:30 Five, hit six, hit Enter, click again, hit seven. So that's like three clicks. Can I do this in a third of the clicks? 0:37 Can I save 66% of my time and iterate through? This number without affecting this B1 and without having to go through the entire sheet dealing. 0:47 Yes, I think the answer here is an Apps Script . And I'm gonna show you how to do that, but first I wanna change the background and everything of this. 0:54 I'm gonna use sheet styles and I'm gonna do this in one click.

You'll see I want to just, make it a little easier to look at. 1:03 There we go. But I'm going to go to Apps Script . Let's see what we have in Apps Script . I'm going to write a little Apps Script very quickly, in real time here. 1:11 And all we need to do is we need to do, I guess, two things. One, we can create a menu here, but then that's going to be the same amount of clicks like. 1:21 Three or four clicks, actually more clicks probably, to iterate this. But we wanna do a button here. So we can insert a drawing . 1:30 And let's do, let's do something like this shape.

Oh, actually. Let's just do this. Let's fill this in. There you go. 1:46 Save and close. There we go. We got our plus sign and we just want. To go into this little cell here. 2:05 Put it next to the cell. Maybe even make the cell bigger. But over here, we're going to be able to, on this three dots, on this drawing , we'll be able to assign a script. 2:15 What is that script going to be? It's going to be this. We're going to write it in real time here. 2:20 Get ready. It's going to be. Just open up app script . It's going to say function my function .

All we need to do is let's say add to a one, let's call it that. 2:32 We're going to get first off, we need variable number , equals spreadsheet app. We can use This IntelliSense here, we can just hit Enter, get active, spreadsheet , get sheet by name. 2:51 The sheet by name is gonna be sheet one, of course, because we haven't changed the sheet name , sheet one. We need also the range . 3:03 GetRange , a1, in quotes, and we need the value, getValue .

So this, all of this, just gets whatever number is in a1 and brings it here and we're going to do a variable new number . 3:18 It's going to be a little verbose. You probably can do it differently. It's going to be a little verbose. But this is going to be simple. 3:23 Variable new number equals number plus one. And what we want to do is we want to take all of this, paste it here, but we're going to change one thing. 3:36 We're going to change get value to set value. And the value we want to set is the new number . That is it. 3:43 Hit command S or hit save project up here.

And we can actually run this without having to assign it just to double check. 3:51 If there's any errors or anything, we might have to authorize it. We'll see in a hot second. Yeah, we do have to authorize here. 4:02 Select our account, allow, it's just editing and creating sheets with this spreadsheet app function . That's all we need. Let's see, we iterated the number . 4:14 Okay, so now take this name, this function added to A1.

The exact name that you named it, not the function , but the actual text. 4:22 Here add to a one going to click on the three dots of this plus sign a sign script and all we have to do is paste that text there click okay and let's see if this is going to work and there we go now we have just one click we are iterating a one and that's all we're iterating. 4:43 Of course, B one is changing because it's connected through many different sources, many different sheets, many different functions and custom stuff. 4:50 But now we have a pretty cool little iterator here, really fun thing.

And if you're looking to iterate or add numbers to anything, this might have been very helpful for you. 5:00 If you're a better sheets member, down below Oh, at bettersheets.co is this exact sheet. You can copy it to your Google Drive. 5:08 And if you're not a member, become a member today. And if you're watching this on YouTube, go ahead and watch another video here. 5:13 There's tons of videos here about Google Sheets and doing cool stuff.

But also, check out Spreadsheet Automation 101, course to take you from zero, knowing nothing. 5:22 To knowing something about automating your Google sheets.</div>