Google Sheet + Apps Script : Day03 (Removes blank Cells in a column consisting of hyperlinked strings)

 

Turns a spaced column into a compressed one :

 ==>

function removeEmptyCellsInColumn() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheets()[0];

    var start=130;

    var last=300;

    var i=start;

    var j=i+1;

    var rangeCut,rangePaste;

    var a_i;

    var a_j =sheet.getRange(j,2).getValue()

    while(j<last){

        if(a_j !== "") {// if a[j] isNOTempty //a[i+1] =a[j];

          rangePaste = sheet.getRange(i+1,2); //pulls up next val in  next contiguous empty col

          rangeCut =sheet.getRange(j,2);

          a_i =sheet.getRange(i,2).getValue();

          

          var richValue = SpreadsheetApp.newRichTextValue()

                        .setText(a_j)

                        .setLinkUrl(sheet.getRange(j,2).getRichTextValue().getLinkUrl())

                        .build();

          rangePaste.setRichTextValue(richValue); //paste at a[i]

          i++;

          rangeCut.clear();//clears or cuts the a[j]

        }

        j++; 

        a_j =sheet.getRange(j,2).getValue();

      }

}

Google Sheet + Apps Script : Day02(Removes/Blanks Cell containing Digit/Number)

 

This code helps remove all numbers in 3rd column of third sheet in a SpreadSheet.

function numberRemoverFromColumn() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheets()[2];

  //  var sheet = ss.getSheetByName("SheetName"); 

 

 var row=1;

  var startRowAt=2; //starting row

  var endRowAt=2192;//last row

  for ( row=startRowAt; row<endRowAt; row++ ) {

    var range = sheet.getRange(row,3);

    var presentCellValue =range.getValues(); //=values[0][0];

      if(presentCellValue%2===0 || presentCellValue%2===1){

        Logger.log(presentCellValue+" will be blanked now");

        var richValue = SpreadsheetApp.newRichTextValue()

                        .setText("")

                        .build();

        range.setRichTextValue(richValue)

      }

  }

}

Google Sheet + Apps Script : Day1

Created a program to change each strings containing cells of first column into hyperlinks redirecting to itself : 

function selfRefrencingHyperlinkMaker() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheets()[1];

  //  var sheet = ss.getSheetByName("SheetName"); 

  var row;

  var startRowAt=2;

  var endRowAt=150;

  for ( row =startRowAt; row<endRowAt; row++ ) {

    var range = sheet.getRange(row,1);

    var values = range.getValues();

      if(values[0][0].length >0){

        var richValue = SpreadsheetApp.newRichTextValue()

                        .setText(values)

                        .setLinkUrl("#gid="+sheet.getSheetId()+"&range=A"+row)

                        .build();

        range.setRichTextValue(richValue)

      }

  }

}