Mail migration reports with Google Script

How the Softvision IT team completed a smooth Mails transition

Cristian

Cristian

IT Community Manager at Softvision
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.
Cristian

Latest posts by Cristian

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]!="") {

   status[data[i][0]]=data[i][1];   

   mails[data[i][0]]=data[i][3];   

 }

 //Logger.log(status);

 //Logger.log(mails);

}



/** 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]]);

                    break;

       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]]);

     }

   }

}



//RUN THIS

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

 //RUN ON IT COPY DOC 

 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);

 }

Conclusion

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.

Share This Article


Cristian

Cristian

IT Community Manager at Softvision
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.
Cristian

Latest posts by Cristian