Poor Man’s Mail-Merge with Gmail

Sample-personal-email-for-mail-mergeHere’s how you can send a lot of individual personal messages if all you have is a GMail or Google Apps account.

One of the key ways for successful startups or new IT projects is knowing your customers (and partners) and being available to them – so you can quickly collect feedback, learn more about what they are trying to achieve with your platform, and just make sure that they know who to contact when they need to.

Instructions below will help you set up your personal gmail account or Google Apps account to send personalized email like the one you see on the right. Note that this is as personalized as it gets with everything originating from your address (no on-behalfs) and the recipient being the only person in the To list (no BCCs or users seeing addresses of each other).

Phase One: Simple Email Script

1. Open the Google Script developer environment by going to http://script.google.com and clicking the Start Scripting button,

2. Close the welcome screen and you will get your first script created for you:

google-script

3. Click the Untitled project name at the top left and give it a proper name – this file will get added to your Google Docs.

4. The simplest script that sends an email to someone will likely look like:

simple-email-script

Every email needs to have a recipient, subject and body (the text in the email). Note that you need to add ‘\n\’ at the end of each line to have more than one line in your text. Also, note that besides the plain text – you can have nicely formatted HTML email text – see how I did that in the htmlbody variable.

5. Now click File / Save, and then Run / YourFunctionName to run the script.

The first time you run the script you will be getting a couple prompts to confirm access to gmail. After that the script will run and send the email.

Phase Two: Multiple Emails and Names

Running a script to send just one email is kind of meaningless. Lets modify the script quickly to send many emails at once:

1. Add arrays with names and addresses – e.g.:

var names = ['Bill', 'Susan']
var addresses = ['bill@test.com', 'susan@test.com']

2. Put everything in a FOR loop that goes through the lists:

for (var i=0; i<names.length; i++){
}

3. Use names[i] to put the name into subject and body (if you want) and addresses[i] instead of the recipient in the Gmail call. You code will likely look like:

multiple-emails-with-names

Phase Three: Get Recipients from a Spreadsheet

Now, if you want to have a spreadsheet rather than the list of recipients in the code, this is also fairly easy to do. Here’s how.

1. Create a Google Spreadsheet like this one and save it:

recipient-spreadsheet

2. In your script, add the code that opens the spreadsheet (you will the Id of the spreadsheet – copy it from the URL of the spreadsheet in the browser):

var sheet = SpreadsheetApp.openById("1speapm4TJpG5SxRFC94RIkpTzf6vAiZUBGoT7Xrk6Iw");
var data = sheet.getDataRange().getValues();

3. Change the loop to iterate through the cells of the spreadsheet instead of the preset array:

for (var i = 0; i < data.length; i++) {

4. Instead of name use use data[i][0] and instead of email address use data[i][1].

multiple-emails-from-spreadsheet

 

That’s it. Now you can send multiple personalized emails from your very own Gmail/Google Apps account.

Checklist for Cloud Service Operations

I knew one software company that failed their SaaS transition because they chose to cut a few corners with the operations. Since they were software engineers, they did not really want to spend time on such mundane tasks as security, auditing, backups and so on. One day they let a disgruntled employee go, the guy went to an internet cafe, logged into the hosting account with the shared admin credentials, and deleted all customer data.

There were no backups or data replicas to bring the data back, no personal admin accounts or procedures to prevent such an incident from happening, and even no monitoring to learn about the issue before customers did. This was the end of this SaaS application – it just never recovered.

Agile DevOps in the Cloud - Session recording from WSO2Con Asia 2014Cloud business is more than just putting some code online (and collecting money ;)) Whether you are offering Software-as-a-Service (SaaS) web application, Platform-as-a-Service (PaaS) or Infrastructure-as-a-Service (IaaS) – what you are offering is more than just your code – it is your service.

Even if you do not offer a formal service level agreement (SLA) and have a statement in your Terms of Service that you are not liable for anything, your online application or platform is still a service so your customers expect it to be reliable and secure.

At our recent WSO2Con, Chamith Kumarage delivered an excellent session on how our Cloud DevOps team works. If you are delivering a service online (or considering doing so) – make sure to watch the recording (quick registration required).

Here’s my quick summary of Chamith’s advice:

1. Automate everything: repetitive tasks not only are inefficient and mundane, and eat your time. When done manually they are unreliable. Humans tend to do things slightly differently each time they do them, or not do them at all.

2. Tasks are really parts of processes: when you come up with something that needs to be done, ask yourself what is the process flow for this task? For example, a data backup is really a part of a process that includes:

  • Scheduled (e.g. at 1 a.m. every day) script which creates a backup,
  • Some sort of monitoring system which verifies that the script ran and the backup got created,
  • Notifications on failures and procedures that need to be followed in not,
  • Backup testing: automated and/or regular manual recovery drills (if manual then documented and performed by different team members).

3. Design for failure: everything will be failing so make sure that your system can sustain the failures. For example, if your system uses multiple virtual machines in the cloud, keep running a “chaos monkey” script which keeps randomly killing the instances and automated tests which ensure that these instance failures do not affect the overall system (by the way, see how Netflix does that.)

4. Self-healing and success verification are critical for all tasks. Any task and operation can fail (see above) so the system should not get “surprised” but should always automatically validate the action results and if something didn’t go right – implement the healing procedures (start new instances, retry, and so on).

5. Enforce discipline, processes, automation, checklists. Document everything. This will make your processes repeatable and reliable.

Bus monkey test” (related to the above) if one of your team members gets hit by a bus – all operations should keep working: everything needs to be documented and tried by other team members. (* This is a mental experiment – do not actually hit your team-members by busses :))

6. Monitoring and analytics: the key is not to collect and show tons of data and alerts, but be able to quickly detect abnormal behavior.

7. Communications: your dashboards should quickly and clearly give you the big picture and relevant details. Key metrics and system state should be something that everyone sees and understands, effective drill-downs should make it easy to understand and fix stuff.

8. Agile delivery: waterfall processes in the cloud are bad and stressful.The smaller the changes and the more often and in more automated fashion they are – the more mundane they become: which lowers the risks and improves the skills and reliability. Cloud and big-bang releases do not go well together.

9. Use standard tools and native systems of underlying platforms – do not reinvent the wheels. For example, if the platform gives you SQL-as-a-service (Amazon RDS, Azure SQL and so on) – use those and not your own MySQL running on a virtual machine.

10. Post-mortem analysis is a must. If something did get wrong after all, you need a formal investigation process:

  • What happened?
  • Why and what needs to be done to prevent this in the future?
  • If automated monitoring didn’t catch it, why and what needs to be done to prevent this in the future?
  • If validation and self-healing didn’t catch it, why and what needs to be done to prevent this?

Full session recording and slides are available here.

Building WSO2 Cloud

This year I changed my job and became in charge of the Cloud business at WSO2. This means that now on this blog you will have even more “cloud stuff”, so I thought I would start with a quick intro to why I joined WSO2 and our general cloud directions.

Sorry if the stuff below sounds a bit salesy. :)

WSO2 started as a middleware company – with an enterprise service bus – WSO2 ESB – that is still one of the leading products in the industry.

For those who do not know, ESB is basically a system that helps connect all the discrepant IT systems that you might have – all talking different protocols, formats, etc. – so you can create your enterprise applications that use all the building blocks that you have in the company.

What made WSO2 cool (besides amazing performance and the fact that everything they produce is open source under Apache license and that all the core product discussions happen in the open, in public newsgroups) is that early on, the company designed the product to be a platform (called Carbon) which has many building blocks (for identity management, various protocols, event processing, transformations, analytics and so on) which the company then used to deliver a huge set of successful products:

  • ESB,
  • API Manager,
  • Identity Manager,
  • Business Analytics Monitor,
  • Complex Event Processing,
  • Private PaaS,
  • App Factory,
  • Enterprise Mobile Manager,
  • and many more.

All of these built from the same Carbon platform – which is very impressive.

This basically pushed the company from middleware provider to become a supplier of one of the most comprehensive platforms that the biggest enterprises are using to turn their IT platforms into Connected Business – unified system serving APIs, mobile apps, portals, applications and services connecting the company’s resources with employees, partners and customers.

Companies like Boeing, eBay, and StubHub are using this platform to run their systems – so inadvertently you have probably been a user of WSO2 technology one way or another.

Now my goal within WSO2 is to extend the reach of our technology to companies which would like to consume it as a service.

Coming from both enterprise and cloud background, I can attest that the collection of technology that WSO2 has is probably the most comprehensive cloud platform in the industry today.

At the moment, this technology gets used mostly in private cloud scenarios. My team is now actively building the public cloud side of the story. Stay tuned!

Website scraping to PowerShell module

I have not tried this one myself, but I have to admit: this Gargl video on turning Yahoo search site into a PowerShell module looks pretty cool:

See Joe’s blog post for details and download links.

Upvote PowerShell in WindowsITPro Community Awards

WindowsITPro is doing their annual Community Choice Awards voting – and there’s a bunch of PowerShell-related nominees:

1. SDM & SpecOps in Group Policy – both have PowerShell,
17. Best Wireless/Mobile: SAPIEN iPowerShell
20. Scripting Tools: PowerGUI, PowerShell Plus, PowerShell, Sapien PowerShell Studio,
25. Task Automation: Microsoft Windows PowerShell,
26. Training and Certification: Sapien PowerShell videos,
28. Free and Open-Source: PowerShell Plus, PrimalForms.

The voting closes tomorrow – Sept 20, 2013 – so there’s still a chance to upvote the PowerShell tools that you like. :)

PowerShell Script to Bulk-Change Excel File Formats

Today I had to find a way to change file formats for a lot of files. Here at Jelastic we use JIRA for bug-report tracking. Every week I am getting tons of automated reports from JIRA to send around to our partners and so on. The problem is that JIRA exports reports in some weird html format with xls extention, and although Windows Excel can open it (after displaying ‘the file format and extension do not match’ warnings) – Macs cannot.

Opening and resaving lots of files manually was not an option – PowerShell was. :)

[UPDATE] I’ve made a few minor changes per feedback from Stanley – now properly managing file extansions and formats.

Here’s the script that you can reuse if you have a similar problem to solve:

############################
# PowerShell script to open all Excel files in a folder
# and re-save them in proper format
# (c) Dmitry Sotnikov
############################

# create COM object to use Excel
$objExcel = new-object -comobject excel.application

$objExcel.Visible = $True

# open the files to re-save one by one
dir D:\myfolder\*.xls | ForEach-Object {
$doc = $objExcel.WorkBooks.Open($_.FullName)

# Save in new format and with new extension
# Format codes can be found here:
# http://msdn.microsoft.com/en-us/library/office/ff198017.aspx
$doc.SaveAs(“$($_.Directory)\$($_.BaseName).xlsx”,
[Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook)
$doc.close()
}

$objExcel.Quit()
$objExcel = $null

Just Script It!

Here’s Sean’s newest crazy PowerShell video in which he managed to feature your’s truly doing moonwalk (or trying to ;))

As you can see from the video – Microsoft MVP Summits are a lot of fun with amazingly smart and crazy people around.

No more one off IT management. Just Script It!


My Recent Tweets

Legal

The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer - WSO2 or anyone else for that matter. All trademarks acknowledged.

© 2007-2014 Dmitry Sotnikov

April 2014
M T W T F S S
« Mar    
 123456
78910111213
14151617181920
21222324252627
282930  

Follow

Get every new post delivered to your Inbox.

Join 93 other followers

%d bloggers like this: