script-v3.txt

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

////////////////////////////////////////////////////////////
//SET THE SHEETS YOU DON'T WAN'T TO APPLY THE SCRIPT////////
////////////////////////////////////////////////////////////
sheetBlackList = [ "Summary", "Help"]

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

///////////////////////////////////////////////////////////////
//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 nameCurrentSheet;
var sheet;


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

//Will decide if the script should aply to certain sheet, using the blackList defined above
function establishSheet(){
  nameCurrentSheet = ss.getActiveSheet().getSheetName();
  if (sheetBlackList.includes(nameCurrentSheet)==false){
    sheet = ss.getSheetByName(nameCurrentSheet);
  } else {
   sheet = null;
  }
}

//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(){
  establishSheet();
  numberOfEntries();
  sortByDate();
  paintBorders();
  addAllCheckboxes();

  if (sheetBlackList.includes(nameCurrentSheet)==false){
    var sheet = ss.getSheetByName(nameCurrentSheet);
  } else {
   sheet = null;
  }
}

//Paint borders
function paintBorders() { 
  //Initilizing variables needed
  var prevMonth; //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 || row == startingRow){
      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){
  establishSheet();
  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, sheet.getLastColumn()).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();

  }
}