2012-10-26

Using Google Apps Scripts to create an automatic email subsscription service


Maintaining an email list for email subscription service is a headache.  What I mean is a sincere service and not for spam email dissemination.

The steps will be as follows:

  1. to collect email address
  2. to confirm the email address is capable of receiving emails and the email address registration is intended (by sending a confirmation email and request acknowledgement)
  3. to process the acknowledgement
  4. to send email periodically based on the maintained email list
I encountered Google Apps Scripts last month and starts to think how I can make use of this to automate the above process.  There was already tutorial on how to handle step 4.  This blog will focus on steps (1) to (3).

I shall use Google Spreadsheet to maintain the email list.

For step 2, there can be two alternatives (i) requiring user to send email to a designated address; (ii) allowing user to input an email address on a web page.  Since I think some users may use receive-only email address, I decide to choose alternative (ii).

So, for step 1, the most straight forward method is to use Google form, as shown below:


Then I shall use Google Apps scripts to process the registration request.  Google supports many methods to invoke Apps scripts (link), one of which is the form-submit event handler.  Here I setup a method "send_confirmation" as follows:


I call this method send_confirmation because it will send an email to the inputted email address for confirmation purpose.  The source is as follows:


Before sending the email, I will assign a random number for a unique key (which will be included in the email subject).  This number will be recorded in the spreadsheet.  However, I find the range passed into the method (e.range) has only the fields populated by Google form.  Therefore I need to use the Spreadsheet object to do the update.  Moreover, there is a tutorial to use sheet.getLastRow to return the row number for update.  But I wonder whether there is any locking mechanism to prevent concurrent update.  Therefore I use e.range.getRowIndex instead.

I would expect the user to reply the email to confirm the email address is for an intended registration.

To process the email reply (step 4), I use another trigger "checkGmail".  I originally think whether there is any asynchronous event handler at Gmail for any event like emailReceived.  But I fail.  Therefore I use a periodic event (Time-driven) to periodically poll my Gmail inbox for an reply.

The source is as follows:


My email search criteria is 'in:inbox is:unread subject:"xxx"' to ensure that only new emails are processed.  Again I use the Spreadsheet object to search for the email record (using the unique random number).  If found, I will update the spreadsheet with a time-stamp to confirm the email address is geniune.

Finally I have included a method formatDate because I find there is not a dateFormatter in Javascript.  But it is easily copied from internet.

2012-10-02

@Ted: What's invisible? More than you think - John Lloyd

Scripts from "What's invisible? More than you think - John Lloyd"


So the question is, what is invisible? There is more of it than you think, actually. Everything, I would say. Everything that matters except every thing and except matter.

We can see matter. But we can't see what's the matter. As in this cryptic sentence I found in The Guardian recently: "The marriage suffered a setback in 1965, when the husband was killed by the wife." There's a world of invisibility there, isn't there?

So, we can see the stars and the planets, but we can't see what holds them apart or what draws them together. With matter, as with people, we see only the skin of things. We can't see into the engine room. We can't see what makes people tick, at least not without difficulty. And the closer we look at anything, the more it disappears. In fact, if you look really closely at stuff, if you look at the basic substructure of matter, there isn't anything there. Electrons disappear in a kind of fuzz, and there is only energy. And you can't see energy.

So everything that matters, that's important, is invisible. One slightly silly thing that's invisible is this story, which is invisible to you. And I'm now going to make it visible to you in your minds. It's about an M.P. called Geoffrey Dickens.

The late Geoffrey Dickens, M.P. was attending a fete in his constituency. Wherever he went, at every stall he stopped he was closely followed by a devoted smiling woman of indescribable ugliness. Try as he might, he couldn't get away from her. A few days later he received a letter from a constituent saying how much she admired him, had met him at a fete and asking for a signed photograph. After her name, written in brackets was the apt description, horse face.

"I've misjudged this women," thought Mr. Dickens. "Not only is she aware of her physical repulsiveness, she turns it to her advantage. A photo is not enough." So he went out and bought a plastic frame to put the photograph in. And on the photograph, he wrote with a flourish, "To Horse Face, with love from Geoffrey Dickens, M.P." After it had been sent off, his secretary said to him, "Did you get that letter from the woman at the fete? I wrote Horse Face on her, so you'd remember who she was."

I bet he thought he wished he was invisible, don't you?

So, one of the interesting things about invisibility is that things that we can't see we also can't understand. Gravity is one thing that we can't see and which we don't understand. It's the least understood of all the four fundamental forces, and the weakest. And nobody really knows what it is or why it's there.

For what it's worth, Sir Isaac Newton, the greatest scientist who ever lived, he thought Jesus came to Earth specifically to operate the levers of gravity. That's what he thought he was there for. So, bright guy, could be wrong on that one, I don't know.

Consciousness. I see all your faces. I have no idea what any of you are thinking. Isn't that amazing? Isn't that incredible that we can't read each other's minds? But we can touch each other, taste each other perhaps, if we get close enough. But we can't read each other's minds. I find that quite astonishing.

In the Sufi faith, this great Middle Eastern religion, which some claim is the route of all religions, Sufi masters are all telepaths, so they say. But their main exercise of telepathy is to send out powerful signals to the rest of us that it doesn't exist. So that's why we don't think it exists, the Sufi masters working on us.

In the question of consciousness and artificial intelligence, artificial intelligence has really, like the study of consciousness, gotten nowhere. We have no idea how consciousness works. With artificial intelligence, not only have they not created artificial intelligence, they haven't yet created artificial stupidity.

The laws of physics: invisible, eternal, omnipresent, all-powerful. Remind you of anyone? Interesting. I'm, as you can guess, not a materialist, I'm an immaterialist. And I've found a very useful new word, ignostic. Okay? I'm an ignostic. I refuse to be drawn on the question of whether God exists, until somebody properly defines the terms.

Another thing we can't see is the human genome. And this is increasingly peculiar, because about 20 years ago, when they started delving into the genome, they thought it would probably contain around 100,000 genes. Geneticists will know this, but every year since, it's been revised downwards. We now think there are likely to be only just over 20,000 genes in the human genome.

This is extraordinary. Because rice -- get this -- rice is known to have 38 thousand genes. Potatoes, potatoes have 48 chromosomes. Do you know that? Two more than people, and the same as a gorilla. You can't see these things, but they are very strange.

The stars by day. I always think that's fascinating. The universe disappears. The more light there is, the less you can see.

Time, nobody can see time. I don't know if you know this. Modern physics, there is a big movement in modern physics to decide that time doesn't really exist, because it's too inconvenient for the figures. It's much easier if it's not really there. You can't see the future, obviously. And you can't see the past, except in your memory.

One of the interesting things about the past is you particularly can't see. My son asked me this the other day, he said, "Dad, can you remember what I was like when I was two?" And I said, "Yes." And he said, "Why can't I?"

Isn't that extraordinary? You cannot remember what happened to you earlier than the age of two or three, which is great news for psychoanalysts, because otherwise they'd be out of a job. Because that's where all the stuff happens that makes you who you are.

Another thing you can't see is the grid on which we hang. This is fascinating. You probably know, some of you, that cells are continually renewed. You can see it in skin and this kind of stuff. Skin flakes off, hairs grow, nails, that kind of stuff. But every cell in your body is replaced at some point. Taste buds, every 10 days or so. Livers and internal organs sort of take a bit longer. A spine takes several years. But at the end of seven years, not one cell in your body remains from what was there seven years ago. The question is, who, then, are we? What are we? What is this thing that we hang on, that is actually us?

Okay. Atoms, you can't see them. Nobody ever will. They're smaller than the wavelength of light. Gas, you can't see that. Interesting. Somebody mentioned 1600 recently. Gas was invented in 1600 by a Dutch chemist called Van Helmont. It's said to be the most successful ever invention of a word by a known individual. Quite good. He also invented a word called "blas," meaning astral radiation. Didn't catch on, unfortunately. But well done, him.

There is so many things that -- Light. You can't see light. When it's dark, in a vacuum, if a person shines a beam of light straight across your eyes, you won't see it. Slightly technical, some physicists will disagree with this. But it's odd that you can't see the beam of light, you can only see what it hits. I find that extraordinary, not to be able to see light, not to be able to see darkness.

Electricity, you can't see that. Don't let anyone tell you they understand electricity. They don't. Nobody knows what it is. You probably think the electrons in an electric wire move instantaneously down a wire, don't you, at the speed of light when you turn the light on. They don't. Electrons bumble down the wire, about the speed of spreading honey, they say.

Galaxies, 100 billion of them estimated in the universe. 100 billion. How many can we see? Five. Five out of the 100 billion galaxies, with the naked eye, and one of them is quite difficult to see unless you've got very good eyesight.

Radio waves. There's another thing. Heinrich Hertz, when he discovered radio waves in 1887, he called them radio waves because they radiated. And somebody said to him, "Well what's the point of these, Heinrich? What's the point of these radio waves that you've found?" And he said, "Well, I've no idea. But I guess somebody will find a use for them someday." And that's what they do, radio. That's what they discovered.

Anyway, so the biggest thing that's invisible to us is what we don't know. It is incredible how little we know. Thomas Edison once said, "We don't know one percent of one millionth about anything."

And I've come to the conclusion -- because you've asked this other question, "What's another thing you can't see?" The point, most of us. What's the point? You can't see a point. It's by definition dimensionless, like an electron, oddly enough.

But the point, what I've got it down to, is there are only two questions really worth asking. "Why are we here?" and "What should we do about it while we are? And to help you, I've got two things to leave you with, from two great philosophers, perhaps two of the greatest philosopher thinkers of the 20th century, one a mathematician and an engineer, and the other a poet.

The first is Ludwig Wittgenstein who said, "I don't know why we are here. But I'm pretty sure it's not in order to enjoy ourselves."  He was a cheerful bastard wasn't he?

And secondly and lastly, W.H. Auden, one of my favorite poets, who said, "We are here on earth to help others. What the others are here for, I've no idea."