Google Apps Script: Weekly newsletter of bookmarks

I bookmark my favorite articles using Pinboard. Pinboard provides a feed that I’ve been using to share them on my website at learnstream.org/links.

Even better would be to send out a weekly newsletter with recent articles. I was able to set this up through Google Sheets and Apps Script. A warning: Apps Script has a limit on sending email of 100 recipients per day (1500 for Google Apps for Edu), so this wouldn’t accommodate a large number of users, but it works perfectly well to send to myself and a few friends.

Pinboard links feed

First you need to determine the URL of your Pinboard links feed. You’ll use one of the following, filling in the bold parts:

https://feeds.pinboard.in/rss/u:YOUR USER NAME/
https://feeds.pinboard.in/rss/u:YOUR USER NAME/t:TAG/

If you choose the second option, this will only return bookmarks with that tag. For example, my newsletter will only include my items tagged “education” so that readers won’t get my links about programming or animal GIFs. So mine looks like https://feeds.pinboard.in/rss/u:cicatriz/t:education/. Feel free to use that one if you haven’t set up Pinboard but just want to see a newsletter with some entries.

You can also find these URLs on pinboard.in under the link labeled “RSS”, either on your main page or under a specific tag. However, you’ll want to remove the secret:... part or else people will see your private links.

plmqb

Other bookmarking services like Delicious have a similar RSS feature. You can even use Reddit.

Links to Sheets

The next step is to load the links in Google Sheets. Create a new spreadsheet with a blank sheet called “links”. In the top left cell enter the following formula, with your own feed link:

=importfeed("https://feeds.pinboard.in/rss/u:cicatriz/t:education/", "items", false, 50)

The sheet will quickly load and display your links (up to 50)!

gw7at

Generate Your Email

Open the Script Editor from your sheet. First I’ll demonstrate looping through the data and building up a list of titles.

function mailer() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("links");
  var data = sheet.getRange("A1:E50").getValues();
  var titles = [];

  for (var row = 0; row < 50; row++) {
    var title = data[row][0];
    // Exit loop if title is blank
    if (!title) {
      break;
    }
    titles.push(title);
  }

  Logger.log(titles.join(", "));
}

The first couple lines of the mailer function grab all the data we’ve loaded. We use the for loop to access each of the 50 rows. We push the title, which is the first item of data[row], into the titles array. However, if there are less than 50 links, we want to detect an empty title and break out of the loop. At the end we log all of titles, joined together with a comma and space separating them.

If you go to Run > mailer, then View > Logs, you should see the list of titles.

Nice! However, we haven’t accounted for the fact that this is a weekly newsletter: we only want links from the last week. Working with dates can be a huge pain, so I’ll show the added code without much comment. Like with the empty titles, it uses the date column to break out of the loop.

function mailer() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("links");
  var data = sheet.getRange("A1:E50").getValues();
  var titles = [];
  var now = new Date();

  for (var row = 0; row < 50; row++) {
    var title = data[row][0];
    // Exit loop if title is blank
    if (!title) {
      break;
    }

    var date = getDate(data[row][3]);
    // Exit loop if the link is older than 7 days
    if ((now - date)/(24*60*60*1000) > 7) {
      break;
    }

    titles.push(title);
  }

  Logger.log(titles.join(", "));
}

function getDate(datetimeString) {
  var dateString = datetimeString.split("T")[0];
  var dateParts = dateString.split("-");
  return new Date(parseInt(dateParts[0]),
                  parseInt(dateParts[1]) - 1,
                  parseInt(dateParts[2]));
}

Run this again and your list of titles will only include those added in the last 7 days.

The next change just grabs more of the data and formats it into my newsletter, which starts with a list of titles then shows each of the articles with title, description, and URL. I’ve replaced the Logger call with MailApp, so this will actually send the email when you enter your own address and run it!

function mailer() {
 var HR = "\n\n---\n\n";
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("links");
 var data = sheet.getRange("A1:E50").getValues();
 var now = new Date();
 var titles = [];
 var items = [];
 var body;
 
 for (var row = 0; row < 50; row++) {
    var title = data[row][0];
    // Exit loop if title is blank
    if (!title) {
      break;
    }

   var date = getDate(data[row][3]);
   // Exit loop if the link is older than 7 days
   if ((now - date)/(24*60*60*1000) > 7) {
     break;
   }

   var url = data[row][2];
   var desc = data[row][4];

   titles.push(" * " + title);
   items.push([title, desc, url].join("\n\n"));
  }

  body = titles.length + " items:\n\n"
    + titles.reverse().join("\n")
    + HR
    + items.reverse().join(HR);

  MailApp.sendEmail("myemail@example.com", "Ryan's link newsletter", body);
}

function getDate(datetimeString) {
  var dateString = datetimeString.split("T")[0];
  var dateParts = dateString.split("-");
  return new Date(parseInt(dateParts[0]),
                  parseInt(dateParts[1]) - 1,
                  parseInt(dateParts[2]));
}

One more thing! You don’t want to have to come to the sheet and fire it off every week. Instead, use a trigger to run mailer each week. Go to Resources > Current project's triggers then Add a new trigger. My settings are below. If you choose a different frequency be sure to adjust the for loop.

yt4fi

Using a mailing list

The example only sends to one person. Let’s use a list of email addresses that you enter on another sheet. Note that you could also use Google Forms to collect the emails, but I won’t get into that here.

Call this sheet “emails” and enter them in the A column with no header. We’ll load the email data just like the links data and use another for loop to send each email. Here is the final code.

function mailer() {
 var HR = "\n\n---\n\n";
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("links");
 var data = sheet.getRange("A1:E50").getValues();
 var mailSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("emails");
 var mailData = mailSheet.getRange("A1:A100").getValues();
 var now = new Date();
 var titles = [];
 var items = [];
 var body;
 
 for (var row = 0; row < 50; row++) {
    var title = data[row][0];
    // Exit loop if title is blank
    if (!title) {
      break;
    }

   var date = getDate(data[row][3]);
   // Exit loop if the link is older than 7 days
   if ((now - date)/(24*60*60*1000) > 7) {
     break;
   }

   var url = data[row][2];
   var desc = data[row][4];
   titles.push(" * " + title);
   items.push([title, desc, url].join("\n\n"));
  }

  body = titles.length + " items:\n\n"
    + titles.reverse().join("\n")
    + HR
    + items.reverse().join(HR);
 
  for (var row = 0; row < 100; row++) {
    var recipient = mailData[row][0];
    // Exit loop if the cell is blank
    if (!recipient) {
      break;
    }
    MailApp.sendEmail(recipient, "Weekly links summary", body);
  }
}

function getDate(datetimeString) {
  var dateString = datetimeString.split("T")[0];
  var dateParts = dateString.split("-");
  return new Date(parseInt(dateParts[0]),
                  parseInt(dateParts[1]) - 1,
                  parseInt(dateParts[2]));
}

Remember to add yourself so you can see the result of your work!

  • http://www.chrisarcand.com Chris Arcand

    Cool!