Saturday, April 22, 2023

Email Yourself from a Google Sheet

I wanted to share with you one of the simplest apps scripts that has immense power.

Email!

You can email others, but you can also email yourself.

In any Google Sheet you can go to the menu: Extensions

Then click on Apps Script

It'll open Apps Script in another window.

If you haven't ever written any scripts yet it'l say this:

function myFunction() {
}

We can change "myFunction" to say "emailMe"


and then let's pick the A1 cell on tab Sheet1 to send to our inbox every day.

between the two curly brackets { }

write this:

MailApp.sendEmail(rec,sub,body)

This is the simple script that emails a recipient (rec), an email with subject (sub) and a text body (body)

What we need to do now is set those variables above it.


Now go to the front of MailApp and hit the enter button a couple times.


write  var rec =

and then in quotes put your email address.

like this  

var rec = "andrew@BetterSheets.co"

and now that email that is sending will send to that email address. this is also where you can put someone else's email address if you're sending them a daily report.


next under that line add a return and let's write var sub =

this is going to be our subject

I wrote

var sub = "Today's Report"

and then enter again.

and let's add the body

var body =

oh no. what do we do here? it's not going to be just text. It's going to be the cell in the sheet.

Here's how we get that;

SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("A1").getValue()

So the whole line would be

var body = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("A1").getValue()

What that line means is that we're selecting the spreadsheet app, a built in function inside apps script, then we're getting the active spreadsheet file, the entire file, then we're selecting one sheet, "Sheet1" by name, and then we'll select the range. In this case "A1" so if you want to change to a different cell, change "A1" to the cell you want. and then we're not selecting the value yet until we add "getValue()"

and here's the crazy part, we're pretty much done.


We have to set a Trigger to send this email every day. but if we wanted to just send that email now we can hit "run"

but if you can't. if it's grayed out. try saving the script.

COMMAND + S, or hitting "save project" which looks like a floppy disc icon in the Apps Script toolbar.


If you hit run from here. which you could/should do to test it out, then you'll have to authorize the script. just say yes and allow.


And check your inbox, if you changed the email address to your email. Or check your sent folder, if you kept the rec variable to my email address.


If you followed along, and it didn't work, or it did. let me know


If you want to see the script yourself copy this sheet: https://ift.tt/98SDni4


Now i won't be able to share with you the trigger in a sheet. If I create a trigger, then another user can't see it.

If you create a trigger and share me on a sheet, I can't see that trigger. It's one of those weird things about Triggers that you learn in Spreadsheet Automation 101.


But let me walk you through adding a trigger right now.

Go to the "clock" icon on the left side. if you hover over the sidebar on the left, the text will appear. click on "Triggers"


then go to the bottom right corner and click "Add Trigger"

Because there's only 1 script, the script "emailMe" will already be selected.


Scroll down to "Select event source"

and click on an change "From Spreadsheet" to "Time-Driven"

Then under that you'll see "Hour timer" change that to "Day Timer"

then you can select an hour within which you want to send the email.


Select the hour, then click the blue "SAVE" button.

and you'll see a trigger appear.

you can delete that trigger anytime. check back an hour after the hour you selected and see if it worked. if it did it will send you an email every day.


Again.. you can always delete that trigger.

Please try not to send me an automatic email of your test every day. I hope you changed the email to yourself

If you enjoyed this tutorial, you'll probably LOVE Spreadsheet Automation 101

A complete video course you can take anytime is available on Udemy. And occasionally I'll run a live cohort class via Maven: https://maven.com/bettersheets/spreadsheet-automation-101

Better Sheets Members get Spreadsheet Automation 101 included with both their monthly membership and lifetime membership.



from Hacker News https://ift.tt/nR8uZbi

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.