00:00 We're gonna create a proof-of-life spreadsheet. What this will do is we will have a DoGet web app URL inside of our sheet using Google Sheet Servers that will, essentially if we click that with the correct pin, uh, it will set our status to alive and it'll set a date in C3. And we'll do this every month.
00:19 We will essentially check first. If this set to alive, then if it's not alive, we'll destroy this sheet.
00:32 If we are alive, it will set a new pin and then set D2 to unalive and then send the email with a URL and the pin, thus having a loop which essentially says send the email if clicked Within 30 days, all is well.
00:50 If not clicked within 30 days, we're going to destroy this sheet. So let's start at the top. Let's create a do get.
00:57 This is going to be extensions app script. And here we'll actually write a function do get. We will write a URL here.
01:12 We need parameter from that URL, which is the pin. So the URL dot parameter dot pin will create this URL later on in the app script.
01:27 But for now, that's what we need to get. We also need our original pin.
01:41 That's going to be SpreadsheetApp.getActive. Spreadsheet.getRange. Actually, getSheetByName.
01:52 We'll go back here. We'll call it proof. getRange. it will be e2. getValue.
02:06 So we will have a pin there. And if pin is equal to original pin, meaning So we will take that.
02:30 SpreadsheetApp.GetActive. Spreadsheet.GetSheetByName.Proof. GetRange d2. And setValue to alive. more thing here, which is set the date.
02:53 This is a little bit unnecessary, but we want to keep track of when is the last confirmation to C2. So we'll do C2 and setValue as newDate with parentheses. Save all of that.
03:05 Deploy new deployment. We'll select the type as a web app. We will execute as me who has access anyone. Click deploy.
03:20 So anyone with that link can use it. And they're not accessing the sheet. They're just clicking link this web app URL.
03:27 So this web app URL we can use later, but we can also get it. So you can say URL equals this.
03:37 We can also get it another way. Function get script URL. Variable URL equals script app dot get service get URL.
03:55 So let's see if this script app dot get service gets this URL. So we don't have to copy. We might have to copy and paste this.
04:03 So let's function script URL, run. And it's getting macros S A K F Y C B, looks correct. V M, nope.
04:18 It's not the same URL at all. Thank you. So we won't use the script app, ducket service deck URL. We'll just use this URL up here.
04:30 So now what we need to do is create the monthly trigger function.
05:00 Send proof of life. Let's go back here.
05:18 We've created this. Check pin, set D2, set C3. And now we're creating a monthly trigger, if not alive, destroy sheet.
05:26 So the first thing this needs to do is check. D2. And so we'll say variable static.
05:37 Status equals, and we're not setting the value, we're getting the value of D2. And if status, status is equal to alive, we'll do something.
05:55 And else, we'll do something else. So every month, we're going to be seeing more sending, doing the send proof of life.
06:04 So if we're alive, we want to execute, again, those three things. We want to set a new pin, set D2 to not alive, send the email, but if not alive, we want to destroy the sheet.
06:15 How do we destroy the sheet? Two lines of code. Variable ID equals spreadsheet app.get active spreadsheet.get get ID, and then drive app dot get file by ID ID dot set trashed.
06:36 And we're going to set this to true. That's it. That's how you destroy this exact file that we're in right now.
06:42 If we are not alive, but if we're alive, great. We want to check again. Because we've been alive this whole month, we want to check one more time.
06:53 So we're going to do, set a new pin, set D2, not alive, and then send the email. Let's set a variable pin, math.floor, we can do something like 10,000 plus math.random times 9,000, 90,000 and this is going to generate a five-digit pin.
07:46 Again, we can create a little function, setPin, logR.log, this, and then we can see what that does.
08:01 SetPin, run it, and see we have 2, 4, 9, 10. We'll run it again, and we're just setting a random number here.
08:20 Cool, so that's going to be setting the pin. We need to actually run actually put that pin somewhere, which is pin up here, which was E2. E2, setValue, pin.
08:40 At the same time, we will set to not live, D2, and we need to create a confirmation link, so variable confirmation link equals, we're going to use this web app URL.
09:13 We're going to concatenate, slash, question mark, PIN equals, and then add a plus at the end, we need actually only one question mark, the new PIN we created.
09:29 So now we will create this URL here, set the PIN, and then we will check here if that PIN is correct.
09:36 So if it's an old pin, then we won't set a live. Someone else has access to our email or an old URL.
09:48 We need someone to send it to, so we'll say, to is, spreadsheet app, get, active spreadsheet, get, owner, get email, we'll send that to gmailapp.send, we'll send it to the email, we'll have a subject, a body, and we will also create an HTML body.
10:22 So, we have our two already, we need a subject, please confirm you are alive, variable body equals, click the link please, plus confirmation link, variable HTML body plus is a little bit more, click to confirm.
10:55 We are, for a break, ahref equals, we need here, probably single quotes, so that we can, plus, confirmation link, plus, single quote, target equals, So we've deployed our doGet, we now need to to send proof of life.
12:27 We just want to run this to see if it actually works at first, unless we have some errors. So right at this moment, we are alive.
12:37 We don't have a pin yet, but we will create that pin, set a new pin, set not alive, send the email.
12:44 So let's make sure all that runs. We got an email, and it says click to confirm, confirm you're alive.
13:04 We have a pin here, 46557, and we can see in our URL that at the end, it says 46557. We do actually need to open it in an incognito window, that's one weird thing.
13:21 About scripts, we are alive, last confirmed here. Confirmation's sake, let's add a return 200. That means all is okay.
13:49 And then else, return 400. So let's save that and redeploy this.
14:00 Manage deployments. We'll edit and set new version, new 200, return code, click deploy.
14:13 This will use the same exact URL. There it is. Done. And, we are alive, we have a pin, we have last confirmed.
14:24 Let's run it again, just to make sure. Like, a month later, it's run, we are status not alive, we have a new pin, check our email, we have confirmed you're alive, we need to open it in the incoming data window.
14:43 And we are alive. Last confirmed, just now.
14:56 This actually has a time in here as well. We do HTML service, create HTML output, 200. And this will be 400. Let's update that again.
15:32 Let's run it one more time, send proof of life, before we create the monthly trigger.
15:55 There we go, we now have a 200 code, all is okay, if we use an incognito window.
16:09 But now, we need to send this monthly, send proof of life every single month. So over on the left side, click on triggers.
16:18 Over on the bottom, bottom right, click add trigger. Which function to run, send proof of life. Scroll down, we're going to use a time driven event source, and we're going to choose a month.
16:27 Monthly timer, we're going to send it on the first day of the month, and we're going to send it, let's say, 5 to 6 a.m.
16:32 Click save. And now we'll see our trigger here, and every single month, it'll go into the sheet, as if we were clicking run, and run it, we'll get a new email.
16:48 If you want to delete this trigger or edit it, come talk to these three dots, click delete trigger, or edit it by clicking this pencil icon, and edit it.
16:55 Maybe you want to send it once a year, or every week, whatever you want to do, or change the day of the month. There you go.
17:01 This is a proof of life Google sheet, and it'll be trashed if at any moment past 30 days we're running this.
17:13 If we're not alive, we will. This, maybe we have some secrets here or some interesting stuff. So we've created trigger.
17:23 We've said, if not alive, destroy, if alive, set a new pin and send us an email that we can check that we are alive.
17:31 Send a new pin, set the new pin set, not alive. And also have the last confirmed date and time here.