script-v2.txt

//AUTHOR: MATVEY DERGUNOV
//Code that insert borders by months, and also insert checkboxes for every row.
//For Ian Hii

////////////////////////////////////////////////////////////
//CHANGE THIS VALUE IF YOU WANT TO START ON ANOTHER ROW//
////////////////////////////////////////////////////////////
var startingRow = 3;

////////////////////////////////////////////////////////////
//CHANGE THIS VALUE IF YOU WANT TO START ON ANOTHER SHEET//
////////////////////////////////////////////////////////////
sheetName = "Hoja 1"

///////////////////////////////////////////////////////////////
//CHANGE THIS VARIABLE IF YOU WANT TO START ON ANOTHER COLUMN//
///////////////////////////////////////////////////////////////
var checkboxColumn = 9;


//----------------------------------------------------------------------------
//PLEASE DO NOT CHANGE THIS
var entries;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sheetName);

//Main function for testing
function main(){
  numberOfEntries();
  sortByDate();
  paintBorders();
  addAllCheckboxes();
}

//This will calculate the size of the month's list
function numberOfEntries(){
  var r = sheet.getRange(startingRow,1).getValue();
  var row = startingRow;
  while (r!=""){
    row++;
    //Get of the value of the next month
    r = sheet.getRange(row, 1, 1).getValue();
  }
  entries = row;
}

//When we open the sheets it will execute this sequence
function onOpen(){
  numberOfEntries();
  sortByDate();
  paintBorders();
  addAllCheckboxes();
}

//Paint borders
function paintBorders() { 
  //Initilizing variables needed
  var prevMonth = 0/0/0; //Variable used to store the preavious cell's month, to compare with the next cell's month (actMonth)
  var actMonth;
  var row = startingRow;
  //Here we read the first cell
  var r = sheet.getRange(startingRow,1).getValue();

  //Basic iteration through the cells, the iteration won't stop until it finds a blank cell.
  while (r!=""){

    //Here extract and store the cell's month, from the cell's value we read (r).
    actMonth = r.getMonth();

    sheet.getRange(row, 1, 1, checkboxColumn).setBorder(false, false, false, false, false, false);
    //Here we decide what borders to insert, depending on if there is a change of month or its the same month as preavious.
    if (actMonth!=prevMonth || prevMonth == 0){
      sheet.getRange(row, 1, 1, checkboxColumn).setBorder(true, true, null, true,false,false, "green",SpreadsheetApp.BorderStyle.SOLID);
    } else {
      sheet.getRange(row, 1, 1, checkboxColumn).setBorder(null, true, null, true,false,false, "green",SpreadsheetApp.BorderStyle.SOLID);
    }

    //////////////////////////////////////////////////////
    //HERE WE START PREPARING THE NEXT ITERATION//////////
    //Increment row (for reading the next time the next cell)
    row++;

    //Get of the value of the next month
    r = sheet.getRange(row, 1, 1).getValue();

    //Swtich the prevMonth to actualMonth, as actualMonth gonna 
    prevMonth = actMonth;
  }

  //When we finished we need to insert the las bottom cell's border
  //Inserting of the last border
  sheet.getRange(row, 1, 1, checkboxColumn).setBorder(true, null, null, null,false,false, "green",SpreadsheetApp.BorderStyle.SOLID);
}

//When we edit something it will excute this sequence
function onEdit(e){
  var thisRow = e.range.getRow();
  var latestRow = sheet.getLastRow();
  for (row=thisRow; row<=latestRow; row++){
    if (sheet.getRange(row, 1).getValue() instanceof Date){
      sheet.getRange(row, checkboxColumn).insertCheckboxes();
    }else{
      sheet.getRange(row, checkboxColumn).clearContent();
    }
  }
    //sortByDate();
    //paintBorders();
    numberOfEntries();
}

//Sorting of the rows by date
function sortByDate(){
  sheet.getRange(startingRow, 1, entries).sort(1);
}

//Add all check
function addAllCheckboxes(){
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var checkboxColumn=9;
  var r = sheet.getRange(startingRow,1).getValue();
  var row = startingRow;
  while (r!=""){

    //Here we insert the checkboxes
    sheet.getRange(row, checkboxColumn).insertCheckboxes();

    //////////////////////////////////////////////////////
    //HERE WE START PREPARING THE NEXT ITERATION//////////
    //Increment row (for reading the next time the next cell)
    row++;

    //Get of the value of the next month
    r = sheet.getRange(row, 1, 1).getValue();

  }
}