Wednesday, April 23, 2014

Newsletter service on Google Apps Script

This blog post was inspired by great ebook Google Apps Script for Beginners.

Google Apps Script (GAS) is tool for manipulating Google Documents, SpreadSheets, Calendar, Gmail, Forms, Sites, …  and automating your workflow. GAS offers nice and powerful API, detailed documentation. In this post I am going to describe how to create Newsletter service on GAS.

Newsletter service - as I called it - uses Spreadsheet as storage for subscriber emails, MailApp for sending newsletter, HtmlTemplate for output. And it is deployed as web app.

Use cases:
  • Subscribe new email
  • Unsubscribe
  • Send newsletter

The benefit of using Newsletter service is that you can connect it for many of your projects. Every project uses own sheet.  All is in one place - in your Google Drive. It is not need to recreate newsletter service on every of your webpages.

Data source

  • isSend - is set to 1 when newsletter has been send.
  • isUnsubscribe - is set to 1 when user clicks to unsubscribe link
  • hasError - is set to 1 when an exception occurred during sending email. Google does not find "Delivery Failure" during sending emails. I saw an obscure way how to someone solved it. :)

Send newsletter

It uses HtmlTemplate. HTML output on GAS is pain. GAS uses Caja Compiler. There are lots of restrictions.

For quality output use email templates:

By default Caja removes HTTP headers from your template, but fortunately this does not apply when you send an email.

It is known that Google has defined daily email quota.


New registration of subscriber is designed as HTTP POST request to Newsletter service. The form must contain the required parameters. Request creates a new record in the sheet.


Unsubscribe url is generated automatically at the end of newsletter template.

Final evaluation

This solution has some advantages but disadvantages and limitations too. It is not probably satisfy for big company who wants to send thousands of emails per day.

It might interest you


I was inspired by a great book Google Apps Script for Beginners

The book contains:
  • Spreadsheet automation
  • Manipulate Forms
  • Managing Email Account
  • Script in text document
  • Standalone Web application

Thursday, April 3, 2014

Convert PFX certificate from Windows XP to Windows 7 or Windows Server 2008

It is known that compatibility between version of MS Windows is poor. The best way is to leave MS Windows, but it is not sometimes possible. In this post I am going to describe how to solve problem of incompatibility between certificate in Windows XP and higher versions like Windows 7 and Windows Server 2008 and other.

Use cases
  • User of MS Windows XP migrates to Windows 7.
  • User of Windows XP connects to remote application on Windows Server 2008.

Problem description
User exports your certificate in PFX format on Windows XP and wants to import it to Windows 7 or Windows Server 2008. User receives the following error message:

An internal error occurred. This can be either the user profile is not accessible or the private key that you are importing might require a cryptographic service provider that is not installed on your system.


Use OpenSSL Toolkit for converting certificate to another format. OpenSSL is available for many various OSs.

Create new PFX Check expiration date

Format description

The PEM format is the most common format that Certificate Authorities issue certificates in. PEM certificates usually have extentions such as .pem, .crt, .cer, and .key. They are Base64 encoded ASCII files and contain "-----BEGIN CERTIFICATE-----" and "-----END CERTIFICATE-----" statements. Server certificates, intermediate certificates, and private keys can all be put into the PEM format.

The PKCS#7 or P7B format is usually stored in Base64 ASCII format and has a file extention of .p7b or .p7c. P7B certificates contain "-----BEGIN PKCS7-----" and "-----END PKCS7-----" statements. A P7B file only contains certificates and chain certificates, not the private key.

The PKCS#12 or PFX format is a binary format for storing the server certificate, any intermediate certificates, and the private key in one encryptable file. PFX files usually have extensions such as .pfx and .p12. PFX files are typically used on Windows machines to import and export certificates and private keys.