Apr 3, 2018 · by Cristian

Mail migration reports with Google Script

How the Softvision IT team completed a smooth Mails transition

In 2016, Softvision merged with SPI Company. After the merger, one of the challenges the IT department faced was to migrate data from the old SPI domain to the new Softvision domain. The most important data were our employees’ data, more specifically Mails.

The IT team had to migrate Mails for around 400-500 employees, and because the Mailbox sizes were up to 80GB, it proved to be a challenge to find the best approach in terms of security and reporting as well.

After analyzing many solutions, the team chose the Data Migration tool offered by Google in the Admin Console.

Reporting: Challenges and Solutions

Reporting is very important. You need to be able to answer the following questions in a relatively easy and fast manner:

  • How many mails did we migrate for a specific user?
  • Where are we in total?
  • When will it be done?
  • What mails failed to migrate?

Unfortunately, the Data Migration tool from Google is very time-consuming and difficult to access from the Admin Console. So the team decided to extract all the data from there and build their own “Reporting Sheet” where they could organize everything as they saw fit.

Furthermore, this update needed to be done on a daily basis, which would require a lot of manual work. The perfect solution for this was the Google Script that had access to both the Data Migration Tool and the team’s Reporting Sheet. A script being run daily solved this issue efficiently and also provided better visibility to the management parties involved.

Report Statuses

Below is an example of the team’s report statuses through each migration. It provided a comprehensive overview of the progress and estimated work remaining.

Google Script Code

The Google Script Code came in really handy to build up the migration reports and see the progress from one day to another. Also, the team could get an estimate on where they were during the migration process and the numbers. Below is an example of the Google Script Code:

/** Load google report statuses

googleReportSheet - the sheet we are importing from

status - key - value property to store the status for each user


function loadGoogleReportStatuses(googleReportSheet, status, mails){

 //The data we are parsing / 2 is the first row of interest

 var data=googleReportSheet.getRange(2, 1, googleReportSheet.getMaxRows()-1, 4).getValues();

 //Build key value properties for statuses

 for (i=0;i<googleReportSheet.getMaxRows()-1;i++)

 if (data[i][0]!="") {







/** Update SPI statuses

spiStatusSheet - the sheet we are updating

status - key - value property to store the status for each user


function updateStatusReport(spiStatusSheet, status, mails){

  //The data we are parsing / 2 is the first row of interest

 var data=spiStatusSheet.getRange(2, 1, spiStatusSheet.getMaxRows()-1, 9).getValues();

 //Update statuses

 for (i=0;i<spiStatusSheet.getMaxRows()-1;i++)

   if ((data[i][0]!="") && (status[data[i][0]]!=null)){

     //Logger.log("We have an update for:"+data[i][0]);

     switch (status[data[i][0]]){

       case "RUNNING":

                    //SET STATUS

                    spiStatusSheet.getRange(2+i, 4).setValue("IN PROGRESS");

                    //SET NUMBER OF MIGRATED MAILS

                    spiStatusSheet.getRange(2+i, 6).setValue(mails[data[i][0]]);


       case "COMPLETED":

                    if (spiStatusSheet.getRange(2+i, 4).getValue()!="COMPLETED") Logger.log("NOW COMPLETED FOR:"+data[i][0]);

                    //SET STATUS

                    spiStatusSheet.getRange(2+i, 4).setValue("COMPLETED");

                    //SET NUMBER OF MIGRATED MAILS

                    spiStatusSheet.getRange(2+i, 6).setValue(mails[data[i][0]]);





function main() {

  //The Google Report Document link that we're importing FROM

 var googleReportDoc = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1ozMxa8OTW1yh5d-Gzz1oyT9jBw6ygF1FmZOSIXIhFnw/edit');

 //The sheet we are importing FROM

 var googleReportSheet=googleReportDoc.getSheetByName('Report');

  //The SPI Mail Migration Status Document link that we're importing TO


 var spiStatusDoc = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/11TqoWN7uZbbwMRjOK3rPYpSGlqp1EZjOaMkY5LvfstM/edit');

 //Statuses for each user

 var status = {};

 //Number of migrated mails for each user

 var mails ={};

  loadGoogleReportStatuses(googleReportSheet, status, mails);

  updateStatusReport(spiStatusDoc.getSheetByName('Initial 25 accounts (Test)'), status, mails);

 updateStatusReport(spiStatusDoc.getSheetByName('1st batch of 100 (Darryl\'s Team)'), status, mails);

 updateStatusReport(spiStatusDoc.getSheetByName('2nd batch of 100 (US Team)'), status, mails);

 updateStatusReport(spiStatusDoc.getSheetByName('3rd batch of 200 (Sanjay\'s Team)'), status, mails);



The Softvision IT team migrated 421 employees, totalling over 13.5 million emails and approximately 2TB of data. By reaching out individually, planning each migration, completing it and following up with a report, the team was able to complete a smooth transition.


IT Community Manager
Cristian started his IT career in 2005 and in over 11 years of his career, he has had various roles in development, team leadership, business management, project management and line manager, being involved in over 25 projects, working with clients like Goldman Sachs, Credit Suisse, Voalte, Truevision. He also contributed to the growth of these clients and refining work-at-department level in organizational and process areas. Moreover, he has often managed to provide the necessary vision for colleagues to advance in career paths and evolve within Softvision.

Latest posts by Cristian

Share This Article
  • Anil Raj
    Posted at 13:18h, 21 June Reply

    First off, I would like to appreciate the IT Team for completing such a huge task successfully.

    Kudos team! You guys make our lives easier.
    Google App Script is a simple language based on JavaScript which is developed and
    deployed on Cloud. You can make interesting applications out of google products and can
    use External API’s as well. Glad to know our IT Team is improvising to meet the Business
    demands and automating tasks. Thanks for the share!

Post A Comment