Email yourself from a Google Sheet | Hacker News Reader

Email yourself from a Google Sheet

blog.bettersheets.co

85 pointskamphey23 comments

Apps Script is really powerful and often overlooked as a way to easily automate workflows that involves Google workspace apps.

I’ve built an app similar to this example but that takes things a bit further. It lets you send notifications to a variety of platforms and have them triggered based on conditions such as a cell containing a certain value or a date being reached etc

If you want to check it out the site is https://checksheet.app, happy to answer any questions about it.

I have a reminders spreadsheet that is set up similarly to a crontab file. Each row has:

- subject

- recipient (usually my own email address)

- day of month

- day of week

- every x days

- starting on date

- trigger today? (calculated)

A daily apps script refreshes the spreadsheet formulae (so that the TRUE/FALSE values in the last column are correct for today's date) and then sends the emails.

It took some initial setup, but it's super-easy to add new regular or one-off reminders, and obviously to remove old ones.

Can u share a dummy script? That would be nice.

Seconded, this sounds really handy!

Sure. Here's a view-only copy of the spreadsheet: https://docs.google.com/spreadsheets/d/1kJVja4xo6GG-SanHvqYj...

Note the formula in column H, which generates TRUE/FALSE. You can obviously replace this logic with whatever you want.

I set a daily 8am trigger to run this script, which sends notifications for the rows that are marked TRUE:

  function sendReminderEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange('B1').setValue("");
  SpreadsheetApp.flush();
  sheet.getRange('B1').setValue("=TODAY()");
  SpreadsheetApp.flush();
  var headerrows = 3;
  var numColumns = 8;
  var subjectColumn = 1; // one-based, i.e. column A is 1, B is 2 etc.
  var addressColumn = 2;
  var conditionColumn = 8;
  var startRow = 1 + headerrows; // First row of data to process
  var numRows = sheet.getLastRow()-headerrows; // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, numColumns);
  var data = dataRange.getValues();
  for (var i in data) {
    var row = data[i];
    if (row[conditionColumn-1]) {
      var emailAddress = row[addressColumn-1];
      var message = 'Reminder: ' + row[subjectColumn-1];
      var subject = message;
      MailApp.sendEmail(emailAddress, subject, message);
    }
  }
}

Thanks!

> var rec="andrew@bettersheets.co"

Looks like you can get the sheet owner's email programmatically rather than hardcoding an email:

  SpreadsheetApp.getActiveSpreadsheet().getOwner().getEmail();

I spent a fair amount of time building things like this until I discovered Airtable and gained many, many valuable hours of my life back.

Airtable is basically Google Sheets with with all of the things a smart developer would say “why can’t they just add” and it has the most well documented API that I’ve ever seen and it’s free or close to free for small scale applications like this.

I have a similar setup to mail me if there is a blackout at home. A script running at home as a cronjob updates a cell every 10 minutes. An apps script checks the cell and if it's more than 30 minutes behind it sends me email.

It gives a bit of peace of mind when I am on vacation, knowing that if the breaker trips I can ask someone to go and check what's up.

Cool. A simpler solution for you might be healthchecks.io which you can run for free for what you need.

One use of this is to send yourself a notification any time you get a response to a google form, since google forms populate a spreadsheet. So you can set up emails to trigger any time a row is added to this spreadsheet. Not sure if there's a better way to achieve this workflow, but that's how I was doing it for years.

I've always used the built-in notifications for this. Tools->Notification Settings.

Google Form -> Google Sheets -> Email on new entry, is a great way to do contact forms on a static site as well. It's quite easy to customize the look of any Google form.

https://github.com/jsdevel/google-form

I had a script email me if my website’s Alexa rank would increase vis-a-vis previous day and log every day rankings with a nice graph in the sheet. Lots of fun stuff can be done with Google sheets/scripts.

I used to have lots of little robots that ran on cron jobs, stored their data in postgresql. I moved all that stuff into Apps Script.

Can you somehow use Gapps functionality to host random files, like ICS or html files?

Yupp: https://developers.google.com/apps-script/guides/web

Google sheet also does live currency conversions, which update with exchange rates.

https://en.wikipedia.org/wiki/Google_Apps_Script

mail...what? what is in the mail?

From the article:

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.

This article is so jarring... I'm guessing it wasn't written by ChatGPT, but this makes me wish more authors WERE using ChatGPT...

   > 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.

I just realized this might be a transcript of a Youtube video... I think it'd really help if he got ChatGPT (or manually) rewrote them from that mess, which does NOT inspire any sort of confidence in his product

Reply on news.ycombinator.com