Automatically Check Past Stages
In this video, learn how to automate the checking off of previous stages in a project tracking sheet using Google Sheets and Apps Script, ensuring that once you complete a stage, all prior stages are automatically marked as complete.
Say we're tracking the progress of some projects, and we have these stages in which we will do them in order 6. But when we're tracking them, sometimes we don't come back and track it until we're done with, say, stage 4 or 5. And we have a status progress bar 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 one, two, three, and four. If we know we're already past stage four. So this automation does that. Check this out. If we click stage four here, then the other stages before that are checked off over here in the status column, we are using a sparkline . We're 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 six here. Automatically Check Past Stages Automate Google Sheets without Apps Script, without Zapier Simple Task Management Automations
Let's go to the automation and I want to show you how this works. Cause we may want to use this and you may want to edit this yourself. So over in Apps 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. We are 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 , the value that it checks to will be the value. So if you're checking. If on, turning the check on, it'll be true. 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. And then we want to know what sheet should we be on. So that's what this sheet 1 is. And we're just getting it by its name. Then we're saying if the row is bigger than 1, so that means over Spreadsheet Automation 101 Lesson 2: onEdit() Trigger What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users Show Sheet Tabs Based on Edit One Million Checkboxes
here we're below the header row , then column is above 1 and below 8. 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 occur. We also want to make sure that the value is true, meaning that you are checking it on. If we're unchecking something, we don't want this automation to run. 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 one? 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 Automatically Check Past Stages Getting Started Coding in Apps Script Automate Two Sheets
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. Automatically Check Past Stages One Million Checkboxes