Create an automation that runs whenver we check off a checklist item the past stages also check.
Academy ↓
Hey there stranger!
Automatically Check Past Stages
About this Tutorial
Sheet Resources
Video Transcript
0:00 see we're tracking progress of some projects and we have these stages in which we will do them in order 1 2 3 4 5 6 but when we're tracking them sometimes we don't come back and track it until we're with say stage four or five.
0:15 And we have a status progress bar. Are here that's going to be filled out based on this but what we're looking for in this video is that we want to check off stage 4 let's say and have 1 2 3 automatically check off we don't want to have to go to 1 2 3 and 4 if we know we're already past stage 4.
0:32 All right. So this automation does that check this out if we click stage 4 here then the other stages before that are checked off.
0:40 Over here in the status column we are using a sparkline where it just counting how many of the check boxes are true and then we're loading the then we're loading the progress bar based on maximum 6 here.
0:53 Let's go to the automation and I want to show you how this works because we may want to use this and you may want to edit this yourself.
1:00 So over in app script we're using function on edit and e is the event that's happening. So every single edit that is occurring in your sheet this is triggering.
1:11 We're first getting the row we want to know what row and what column as well are this edit on. We're also getting the value of the edit in the case of a checkbox.
1:20 The value that it checks to will be the value. So if you're checking it on turning the check on it'll be true.
1:28 If you're turning the check off it'll be false. Variable sheet we just want to know what's the name of the sheet we're on so we make sure that we're on the correct sheet.
1:35 And then we want to know what sheet do we should we be on. So that's what this sheet one is and we're just getting it by there and its name.
1:42 Then we're saying if the row is bigger than one so that means over here we're below the a row. And column is above one and below eight.
1:55 In this case it's over to the right of the a column and to the left of the h column. And we're using double ampersands here so that we know that all of these are and so all of these have to be true in order for the if function to.
2:08 A curve. We also want to make sure that the value is true meaning to or checking it on. If we're unchecking something we don't want this automation to run.
2:16 And we're also making sure that the sheet is named sheet one so we're going back up to this sheet get active sheet get name and we're checking is it sheet.
2:24 If all of these are true then this line of code will run which is getting the sheet one getting the range the row that we're on and then checking from column two until column minus one wherever column we're on minus one and we're checking.
2:40 Each of those everything in between and that's how we get this automation that runs every time we check off a box all of the previous checks are checking.
2:50 Thank you.
0:15 And we have a status progress bar. Are here that's going to be filled out based on this but what we're looking for in this video is that we want to check off stage 4 let's say and have 1 2 3 automatically check off we don't want to have to go to 1 2 3 and 4 if we know we're already past stage 4.
0:32 All right. So this automation does that check this out if we click stage 4 here then the other stages before that are checked off.
0:40 Over here in the status column we are using a sparkline where it just counting how many of the check boxes are true and then we're loading the then we're loading the progress bar based on maximum 6 here.
0:53 Let's go to the automation and I want to show you how this works because we may want to use this and you may want to edit this yourself.
1:00 So over in app script we're using function on edit and e is the event that's happening. So every single edit that is occurring in your sheet this is triggering.
1:11 We're first getting the row we want to know what row and what column as well are this edit on. We're also getting the value of the edit in the case of a checkbox.
1:20 The value that it checks to will be the value. So if you're checking it on turning the check on it'll be true.
1:28 If you're turning the check off it'll be false. Variable sheet we just want to know what's the name of the sheet we're on so we make sure that we're on the correct sheet.
1:35 And then we want to know what sheet do we should we be on. So that's what this sheet one is and we're just getting it by there and its name.
1:42 Then we're saying if the row is bigger than one so that means over here we're below the a row. And column is above one and below eight.
1:55 In this case it's over to the right of the a column and to the left of the h column. And we're using double ampersands here so that we know that all of these are and so all of these have to be true in order for the if function to.
2:08 A curve. We also want to make sure that the value is true meaning to or checking it on. If we're unchecking something we don't want this automation to run.
2:16 And we're also making sure that the sheet is named sheet one so we're going back up to this sheet get active sheet get name and we're checking is it sheet.
2:24 If all of these are true then this line of code will run which is getting the sheet one getting the range the row that we're on and then checking from column two until column minus one wherever column we're on minus one and we're checking.
2:40 Each of those everything in between and that's how we get this automation that runs every time we check off a box all of the previous checks are checking.
2:50 Thank you.