Google Apps Script – Validating a column

At Paxcel , We love to experiment with technologies .

And in this blog , I present you our own experiment with Google Apps Script.

Problem Statement :-

We have a Google spreadsheet which contains information about a product . The spreadsheet has many worksheets and one of the sheets is called Releases. Each row of this sheet represents one release and each release has value TRUE/FALSE in the column named “IsCurrent”. Out of all the releases in Releases worksheet, only one will have value for “IsCurrent” column as TRUE while all other rows will have corresponding value as “FALSE” (as shown in figure below).

Google Apps Script

We achieved this logic through Google Apps Script .

Open Script Editor from Tools Menu as shown in figure below.

scripteditor

Add the following code snippet to your editor

function onEdit(event){

     var name = event.source.getActiveSheet().getName(); // Will get the active worksheet’s name             

    if(name == “enter your sheet’s name”) {

              //HandleCurrentRelease(event);

            // We will talk about this method later on

}  }

Now  to achieve the desired goal , we need to add a trigger to this which will notify the spreadsheet on every edit made

We can manage triggers from the script editor then Resources and then Current project triggers (as shown in figure below)

 triggers

Add a trigger with the following values

 triggerValue

Save Project from File Menu .

 

Now lets come to the logic i.e. HandleRelease  which we were talking about earlier.

 But before that we have made some common methods as follows  :-

function getFirstEmptyRow(activeSpreadsheet) {

 var spr = activeSpreadsheet;

 var cell = spr.getRange(‘a1′);

 var ct = 0;

 while ( cell.offset(ct, 0).getValue() != “” ) {

   ct++;

 }

 return (ct);

}

function getEditedCell(event){

 return (event.source.getActiveRange().getA1Notation());

}  

function getEditedRange(event){

 return (event.source.getActiveRange());

}

Now lets come to the logic which will achieve desired goal.

/* Declarations */

var Releases_IsCurrentColumn_Id = “I”;

var Releases_IsCurrentColumn_Id_DesiredIndex = 0;

var Releases_IsCurrentColumn_index = 9;

/* End Of Declarations */

function HandleCurrentRelease(event) {

 var changedCell= getEditedCell(event) ;

   if(IsCellEditedInIsCurrentColumn(changedCell))

   {

     var valueChanged = getEditedRange(event).getValue();

     if(valueChanged == true)

     {

       var lastRow = getFirstEmptyRow(event.source.getActiveSheet());

       for(i=First_Row_index;i<=lastRow;i++)

       {   event.source.getActiveSheet().getRange(i,Releases_IsCurrentColumn_index).setValue(‘FALSE’);

       }

        getEditedRange(event).setValue(‘TRUE’);

     }

   } 

}

function IsCellEditedInIsCurrentColumn(changedCell){

   var posOfI = changedCell.indexOf(Releases_IsCurrentColumn_Id);

   var posOfColon = changedCell.indexOf(Range_Separator);

   return (posOfI ==Releases_IsCurrentColumn_Id_DesiredIndex && posOfColon == RangeSeparator_DesiredIndex);

}

Now try changing any row and then change the IsCurrent value and try playing with it .

If you want to experience , you can do it here

Leave a Reply

Your email address will not be published. Required fields are marked *


− two = 6

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>