Convert Sheet to To-Do-List - Google App Script - MON

/*
To-Do-List creator by Kasimir ROMER (as part of MON1 at EC Marseille)
It creates a To-Do-List from a simple sheet
*/

function createToDosAndChart() {
  // get different parts of the table and save them in variables
  var currentSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // returns the current spreadsheet
  //var activeSheet = currentSpreadsheet.getActiveSheet(); // returns the current sheet
  var activeSheet = currentSpreadsheet.getSheetByName('input'); // returns the sheet with the name 'input'

  var wholeTable = activeSheet.getDataRange(); // returns the whole table as range
  var dataSection = wholeTable.offset(1,0); //returns the wholeTable-DataRange without headers

  var lastColumn = wholeTable.getLastColumn();
  var lastRow = wholeTable.getLastRow();

  // sort the list by date
  dataSection.sort(2); // 2 = 2nd column which includes the date

  // -----------------------
  // -- Create checkboxes --
  // -----------------------
  doneColumnRange = activeSheet.getRange(2,3,lastRow-1, 1); //get range that includes the done values (done/not done)
  doneValues = doneColumnRange.getValues(); // get the values from this range (oui/non)

  // replace french words with corresponding boolean
  for (i in doneValues) {
    if (doneValues[i][0] == "oui") doneValues[i][0] = true;
    else if (doneValues[i][0] == "non") doneValues[i][0] = false;
  }
  doneColumnRange.setValues(doneValues); // update the range with the booleans
  doneColumnRange.insertCheckboxes(); // convert the booleans to checkboxes

  // iterate through all rows. If done is checked, strikethrough the line and grey it out
  var data = dataSection.getValues(); // get the values from the whole table range
  data.forEach(function (row, index) {
    var currentRowRange = wholeTable.offset(index+1, 0, 1); // offset returns a new range that contains the current row
    if (row[2] == true) {
      currentRowRange.setFontLine('line-through'); // the striktethrough is applied to this range
      currentRowRange.setFontColor('lightgray'); // text gets grey
    } else {
      currentRowRange.setFontLine(null); // reset strikethrough
      currentRowRange.setFontColor(null); // reset font color
    };
  })

  // ------------------------ \\
  // START of date operations \\
  // ------------------------ \\

  var datesAndDoneColumnRange = activeSheet.getRange(2,2,lastRow-1, 2); //get range that includes the dates and done values (done/not done)
  var dateAndDoneValues = datesAndDoneColumnRange.getValues(); // get the values from this range -> Array[Date][done]

  // create new sheet to store temporary data
  var temporarySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('temporary-data');
  temporarySheet.clear(); // clear the temporary sheet
  temporarySheet.getRange(1,1,1,3).setValues([["Date", "Done", "Not done"]]); // create header in the temporary sheet

  var firstDay = new Date(dateAndDoneValues[0][0]); // get first array element
  var lastDate = new Date(dateAndDoneValues.slice(-1)[0][0]); //get last array element

  // iterate through all dates in the range: https://stackoverflow.com/questions/4345045/loop-through-a-date-range-with-javascript
  var allDates = []; // array where all dates will be saved
  for (let d = firstDay; d <= lastDate; d.setDate(d.getDate() + 1)) {
    let newDate = new Date(d);
    allDates.push([newDate.toLocaleDateString('fr-FR'),0,0]);
  }
  temporarySheet.getRange(2, 1, allDates.length, 3).setValues(allDates); // write the array with all dates to the temporary sheet

  // iterate throug dateAndDoneValues and read them
  dateAndDoneValues.forEach(function(row, index) {
    let currentDate = new Date(row[0]);
    let currentDoneState = row[1];
    let currentRow = temporarySheet.createTextFinder(currentDate.toLocaleDateString('fr-FR')).findNext().getRow();

    if (currentDoneState == true) {
      temporarySheet.getRange(currentRow, 2).setValue(temporarySheet.getRange(currentRow, 2).getValue() + 1); // increase value of "done" by 1
    } else if (currentDoneState == false) {
      temporarySheet.getRange(currentRow, 3).setValue(temporarySheet.getRange(currentRow, 3).getValue() + 1); // increase value of "not done" by 1
    } else {
      Logger.log('Impossible state, currentDoneState is neither true nor false'); // this case should never happen
    }
  });

  // create a chart if there is none yet
  if (activeSheet.getCharts()[0] == null) { //
    var chartBuilder = activeSheet.newChart();
    chartBuilder.addRange(temporarySheet.getDataRange())
      .setChartType(Charts.ChartType.LINE)
      .setOption('title', 'Tasks per deadline')
      .setNumHeaders(1)
      .setPosition(1, lastColumn + 2, 0, 0);
    activeSheet.insertChart(chartBuilder.build());
  }
}

function convertTableToListOfTasks(){
  // get different parts of the table and save them in variables

  var currentSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // returns the current spreadsheet
  var activeSheet = currentSpreadsheet.getSheetByName('input'); // returns the current sheet
  var headers = activeSheet.getRange('A1:C1'); // returns the header row

  var wholeTable = activeSheet.getDataRange(); // returns the whole table as range

  // format header
  headers.setFontWeight('bold');
  headers.setBackground('lightblue')

  // add filter
  if (activeSheet.getFilter() == null) wholeTable.createFilter();
}

// Code from the YouTube-Tutorial to get used to Google Apps Script, not relevant for the To-Do-List
function formatReport() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var headers = sheet.getRange('A1:C1');
  var table = sheet.getDataRange();

  headers.setFontWeight('bold');
  headers.setFontColor('white');
  headers.setBackground('blue');

  table.setFontFamily('Roboto');
  table.setHorizontalAlignment('center');
  table.setBorder(true, true, true, true,false, true, 'blue', SpreadsheetApp.BorderStyle.SOLID);
  table.createFilter();
  Logger.log(SpreadsheetApp.getActiveSheet().getName());
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Kasis Skripts')
    //.addItem('Format List', 'formatReport')
    .addItem('Create ToDo', 'convertTableToListOfTasks')
    .addItem('Reload To-Dos and Stats', 'createToDosAndChart')
    .addToUi();
}

function onEdit(e) {
  createToDosAndChart();
}