Take Attendance in Google Sheets

0:00 So normally when we take attendance in Google Sheets, we're gonna have a name here, Andy, Bart, Carl, and we have a date and we are gonna say, yes, yes, no. Or we have some kind of checkbox , insert checkbox . Check, check, check. Or let's say Dale. It is not here. Right. And every day we're going to do that same thing, right? And we can create an automation here to make it easier to say, Hey, create a new column, insert all the check boxes, and put a date on the top. Right? We could do that each weekday. But I want to do something that takes attendance in a more, let's say, interesting way. We are gonna. Execute on that idea of inserting a column, but we're going to redo the UI here.

0:48 Instead of having to move the cursor down, we're gonna have a little alert that says, is Andy here? Is Bart here? Is Carl here? Is Dale here? Yes. No. Yes, no. And so you can actually take attendance with this Google sheet instead of having to check bot check all of these boxes or not check 'em. So let's get into it. Attendance. And let's create daily add column, and we're going to go to spreadsheet app dot get active spreadsheet , and this is going to be our class. Get sheet by name, class, insert column after. And we need a number last column.

1:35 So let's get that last column, and we're gonna do this pretty quickly. So get last column variable. Last column equals this. So every day we're gonna add a column, but we don't want to just add a column. What we wanna do is make sure today's date is in the top. So after that, we're going to take the same class, get range . We're going to get the last column again, or last column plus one. We're going to get in the first row, one comma, last column, set value, new date. Now let's format this date.

2:21 Utilities format , date, we're gonna get the time zone of whatever, is the time zone of our sheet, get time zone , and we're going to set the format to month, month slash dd slash year, year, year. If you want to do it differently, just do DD then. Mm, then year. I think we need one more there. So now if we run this, let's see what happens. Run daily AG column. We might have to authorize. There you go. And we have a new one here, so we can set this as a trigger . We can say trigger this, but we need also all the check boxes. So let's go to, let's clean this up a little bit.

3:08 I see this class is happening all here. So let's change this to class sheet and create a variable class sheet equals this. So now we can take all of this that looks a little bit better. So class sheet get range . We want to take row two. We want. This last column plus one, and we wanna take number of rows is going to be, we need variable last row class sheet. Do get last row. This would be last row minus one because. It's minus the header , one column set values . Oh no.

3:54 Check, insert check boxes. There we go. So let's run this again. And there we go. We have all the check boxes for all of the names and today's date there. So that happens every day. We can do that, but let's actually get attendance. So we're gonna create a new function . Function Take attendance. We need the ui. So variable UI equals spreadsheet . App dot get ui. We are gonna also need the variable class sheet again. We're gonna need last row again. And last column. Mm-hmm. So now we're gonna write a four loop, four I equals two I is

4:41 less than or equal to last row. I Plus Plus. So we're gonna add a one each time. So we're gonna start on row two, add one every time, and make sure we go until the last row. Variable name equals class sheet dot get range . The row is gonna be row one, column one. Get value. And we're going to need a variable response equals UI alert. What we're gonna need is, and we're gonna say put this in, in these tilts is dollar sign name.

5:27 Present, are they here? Then another comma and then UI button set. Do yes or no? So either they're here or they're not. If response. Is equal to UI button. Yes. Then we're gonna go to the class sheet. Get range I last column set value. True. Actually, we don't even need set value. True. This should be just check. There we go. There we go. Check and let's see if this works. But before we see if it works, we want to use this function as a

6:12 user, not by coming here and going to this task, tend hit and run. What we wanna do is go to better sheets.co. Slash snippets and get an Unopen custom menu . We'll call this class menu and we'll take attendance. Take attendance today. Take attendance. And we're gonna have another one that sets up this daily co column. So set up daily column, add or add item. Just create column today. It is a daily ad column. We can set this up to be a trigger that could be function , create daily trigger ,

7:00 create daily trigger , so we don't have to come and do this every single time. Let's call this automation and here. Here we're just gonna do script, app.new trigger . We're gonna take the name of this trigger , put it here, time-based at our, and we can say it'll do be in the morning like 5:00 AM or between 5:00 AM and 6:00 AM. Every day. One every day is that. Every single day create. Cool. So now we have this on open custom menu . We're gonna save everything. We're gonna close our app script , we're gonna refresh our sheet, and we're gonna see up here at the top next to help class menu. We can create a column today.

7:46 We might have to authorize the first time we run it. Here we go and we can take attendance today. Is Andy present? Yes. Is Bart present? Yes. Is Carl here? Nope. Is Dale here? Yes. And there we have a nifty little attendance. Through alerts, yes and no. We don't have to be going to each of these and selecting them and hitting space bar or checking them. Hopefully, this has been fun for you. If you're watching this on YouTube, I'll actually make this available. This sheet available down below. You can copy it directly to your Google sheet.