Google Sheets Add-on Power Tools

[, Music ] welcome to digital master. Oh my name is Alex Reyes in this lesson, we'll be looking at an add-on called power tools and one of the useful features that we can use within power tools to help us with things like forms. So I have a real simple example here, where I have a list of names here that have been provided through a Google form that I've had my students submit or I've had anyone submit through a Google Form. Now, there's quite a few names here and I've had people submit and we'll just assume that these are the columns would have had responses that were part of the Google form, but I want to keep this simple, so you can see what this actually does here now. These are the responses that were part of the form and on sheet2 here I have the list of all the responses that I was expecting the list of all the names that I expected to provide a response, because I want to see if everyone responded to the Form so this is very useful for teachers that have a list of students and have placed the form out there for the students to fill out, and the teacher wants to find out if a student has filled out that form or has submitted the form. So in sheet, I have a roster of all my students here and in sheet 1. This would be the form that had been submitted by the students here. So what I want to do is I want to compare the names across these here to find out which students have not submitted the form yet so we're gon na use a special tool called power tools and if I click add-ons and then I go to get Add-Ons I'll find power tools in here by simply searching for power tools and I'll see power tools appear here and then I can just add power tools to my add-ons. I'Ve already done that. So I'm gon na go to add-ons select power tools and click start now. Power tools has several very useful tools, but the one we want to look at right here is in the data section, so I'll click data and in data we can do several things, but one of the things that I want to do here is: I want to Compare two sheets so that I can find out by comparing those two sheets which students have not submitted a form. Yet so I'm gon na click on that and once it gets started, it will automatically detect the sheets on my on my sheets here and it will try to determine which is the main sheet now. The main sheet is the one that has the roster of all the names that I'm expecting. So that's not an sheet one, because that's the the form where all the information was submitted through Google Forms. So what I'm going to do is I'm gon na select sheet 2, which has my roster in it, and the powertools will automatically try to detect which column has information, and if it doesn't do that, we can always click this button here to auto select, and it Will go back and select the column that has all the information and if we have multiple columns, we can come in here and manually provide that in here, but in this example I just have one column with the roster of all my students in here. What I'll, then do is click Next and then now what it's gon na do. Is it's gon na look for the sheep that I want to compare the main one against, so that would be sheet 1 and it's having trouble detecting the information that I want here and that would be typical in a form because it probably have several columns. So what I can do is, I can click Auto Select and Google sheets power tools will try its best to find the column that you want to compare now. If it doesn't do a good job, we can still go in here and enter the range that we want the power tools to compare now, once we've selected the main sheet and then the comparing sheet, we can go ahead and click Next and in this option we We can choose what we want. Do we want to find any duplicate values. Probably we're not gon na have duplicate values, but we do want to find unique values, meaning that I want to find if there is a name in my roster. That does not appear in the form, and that would be a unique value because it's in my roster, but not in the form. So that would be a unique value. So I'm gon na go ahead and select that I'm gon na click Next and then it's gon na ask me if I have headers in the column that I want to consider in this case I don't have any headers here, so I'm just gon na make sure That these check marks are not checked here and that these are also not checked, because I don't have any blank spaces in any of my sheets, so I don't have to place any check marks here. Once I've made these selections here I'll go ahead and click Next and then now I need to do decide. What do Check This Out want powertools to do with the new unique values that it finds? Well, it automatically has an option to fill the unique values with a color and we can choose the color by clicking on this here and selecting one of the colors. I'M gon na go ahead and choose yellow to highlight the colors for the names that are unique. So once I've done that I'll click finish and powertools will go and compare both sheets and find the unique values and in a short set of information like that's on this sheet here, I can see that there's only four unique values, I'll click close and then what I'Ll do is in my main sheet, which is the one that has the main roster information I'll go in here and I'll see if there are any unique names that are in my roster, but not in the form. So yes, here they are. There are four names here that do not appear in the form, which is telling me that these four students here have not gone in and filled in the form. So this is a nice tool that we can be used to compare two sheets in a variety of ways, and in this way we are able to compare a form that was submitted to us with a list of names to a roster that we have and to Determine if everyone has submitted the form, as was expected, [ Music, ]