Proof of Life in Google Sheets
In this video, learn how to create a 'Proof of Life' spreadsheet in Google Sheets that sends a confirmation email monthly and deletes itself if not confirmed within 30 days. The tutorial covers using Google Apps Script to manage the process and set up triggers.
We're gonna create a proof of life spreadsheet . What this will do is we will have a do get 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 a live, and it'll set a date in C3. And we'll do this every month. We will essentially check first is this set to alive? Then if it's not. Alive will destroy this sheet. If we are alive. It will set a new pin and then set D two to UN alive 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. If. Not clicked within 30 days, we're gonna destroy this sheet. So let's start at the top. Let's create a do get. This is going to be extensions app script , and here we'll actually write a function due get. We will write a URL here. We need a parameter from that URL, which is the pin. So it'll be URL parameter .pin. We'll create this URL later on in the app script . But for now, that's what we need to get. We also need our original pin. That's gonna be spreadsheet app doc, get active spreadsheet , get range . If you get sheet by name, we'll go back here, we'll call it proof get range. It will be E two, get Value. So we will have a pin there and if PIN is equal to original pin.
Meaning we have the correct pin, we will set this D two two A Life. So we'll take that spreadsheet , app dock, active spreadsheet docket sheet by name, proof get range D two, and set value to alive. We will also do one more thing here, which is set the date. This is a little bit unnecessary, but we want to keep track of when is the last confirmation, C two. So we'll do C two and set value as new date with save all of that. Deploy new deployment. We will select the type as a web app. We will execute as me who has access , anyone, click deploy. So anyone with that link can use it. And they're not accessing the sheet, they're just clicking the link.
This web app, URL. So this web app URL we can use later, but we can also get it. So you can say URL equals. This, we can also get it another way. Function . Get script. URL, logger Log. URL variable. URL equals script. App. Do get service, get URL. So let's see if this script app, docket service gets this URL, so we don't have to copy, we might not have to copy and paste this. So it's function script, URL run, and it's getting macros. S-A-K-F-Y-C-B looks correct. BM no, it's not the same oral at all. So. We won't use the script app, duck service, URL. We'll just use this URL up here. So now what we need to do is create the monthly trigger function .
Send proof of life . Let's go back here. We've created this check pin set, D two, set C3, and now we're creating a monthly trigger , if not alive, destroy sheet. So the first thing this needs to do is check. D two. And so we'll say variable status equals, and we're not setting the value, we're getting the value of D two. And if status status is equal to alive, we'll do something and else. We'll do something else. So every month we're gonna be sending, doing the send proof of life . So if we're alive, we wanna execute again those three things. We want to set a new pin set, D two to not alive, send the email. But if not alive, we wanna destroy the sheet. How do we destroy the sheet?
Two lines of code variable ID equals spreadsheet app. Do get active spreadsheet dot get id. And then drive app, get file by id, id dot set, trashed. And we're gonna set this to true. That's it. That's how you destroy this exact file that we're in right now. If we are not alive. But if we're alive, great. We wanna check again 'cause we've been alive this whole month. We wanna check one more time. So we're gonna do. Set a new pin set D two not alive, and then send the email. Let's set a variable pin math dot 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. Again, we can.
Create a little function set pin logger. Log this and then we can see what that does. Set pin, run it and see. We have 2, 4, 9, 10. We'll run it again. And we're just setting a random number here. Cool. So that's gonna be setting the pin. We need to actually put that pin somewhere, which is pin up here, which was E two, E two, set value p. At the same time, we will set to a not live D two and we need to create a confirmation link. So variable confirmation link equals, we're gonna use this web app, URL. We're gonna 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. So now we will create this URL here, set the pin, and then we will
check here if that pin is correct. So if it's an old pin, then we won't set alive. Someone else has. Accessed our email or an old URL we need someone to send it to. So we'll say two is sheet app, get active, spreadsheet , get owner , get email, we'll send that to Gmail app dot send. We will send it to the email. We'll have a subject, a body, and we will also create an HTML body. 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 is a little bit more.
Click to confirm BR for a break. A F equals. We need here, probably single quotes so that we can, plus confirmation link, plus single quote target equals. Blank confirm you are alive. So we've deployed our do get. We now need to send proof of life . We just wanna run this to see if it actually works at first, unless we have some errors. So right at this moment, we are alive. We don't have a pin yet, but we will create that pin set, a new pin set, not alive. Send the email. So let's make sure all that runs. We got an email and it says, click to confirm. Confirm your live. We have a pin here, 4 6 5 5 7, and we can see in our URL that
at the end it says 4 6, 6, 5, 7. We do actually need to open it in an incognito window. That's one weird thing about scripts. We are live, last confirmed here. We do need to do one more thing just for confirmation's sake. Let's add a return 200. That means all is okay and then else return 400. So let's save that and redeploy this managed deployments. We'll edit and set new version, new 200 return code. Click deploy. This will use the same exact URL. There it is done and we are alive. We have a pin. We have last confirmed. 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 confirm you are alive. We need to open it in the Inc. Needle window and we are alive. Last, confirmed just now. This actually has a time in here as well. We do HTML service. Create H TM L, output 200, and this will be 400. Let's update that again. Let's run it one more time. Set in proof of life before we create the monthly trigger . There we go. We now have a 200 code. All is okay if we use an Incog window, but now we need to send this monthly, send proof of life every single month. So over on the left side, click on triggers. Over on the bottom. Bottom right click add trigger , which function to run. Send proof of life . Scroll down. We're gonna use a time-driven event source and we're gonna choose a monthly timer.
We're gonna send it on the first day of the month, and we're gonna send it, let's say five to 6:00 AM. Click save. And now we'll see our trigger here, and every single month it'll go into the sheet as if we are clicking run and run it. We'll get a new email. If you wanna delete this trigger or edit it, come to these three dots, click delete, trigger , or edit it by clicking this pencil icon and edit it. Maybe you wanna send it once a year or every week, whatever you wanna do, or change the day of the month. There you go. This is a Proof of Life Google Sheet, and it'll be trashed if at any moment. The past 30 days, we're running this. If we're not alive, we will trash this. Maybe we have some secrets here or some interesting stuff. So we've created a trigger .
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. Send a new pin. It'll set the new pin set not alive, and also have the last confirmed date and time here. Proof of Life