Meraki freeshop google sheet script

meraki_freeshop.js

function check_limit() {
  // Get the active sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  // Get the range of the selected column
  var data = sheet.getDataRange().getValues();
  // Count the rows
  var numRows = data.length;
  var startRow = 4;
  var limitCol = 3;
  // Save the selected the Column
  var selectedColumn = sheet.getActiveRange().getColumn();
  // Get the given range of cells from the LIMIT column
  var limit = sheet.getRange(startRow,limitCol,sheet.getLastRow());
  // Get the given range of cells from the selected column 
  var order = sheet.getRange(startRow,selectedColumn, sheet.getLastRow());
  // Store the values of these cells
  var limitValues = limit.getValues();
  var orderValues = order.getValues();
  
  errors = [];
  // Iterates over the cells
  for (var i = 0; i < numRows-1; i++) {
    orderVal = orderValues[i][0];
    limitVal = limitValues[i][0];
    // If the order is empty we cast it as 0
    if (orderVal === "") orderVal = 0;
    // If the limit is empty or 0 we cast it as a big number
    if (limitVal === "" || limitVal === 0) limitVal = 1000000;
    // If the order is over the limit, tag this cell in red
    if (orderVal > limitVal) {
      order.getCell(i+1,1).setBackground('red');
      errors.push(`Cell ${startRow + i}: order ${orderVal} exceeded the limit ${limitVal}`);
    }
  }

  // If they are errors display it to the user
  if(errors.length > 0){
    var message = errors.join("\n");
    SpreadsheetApp.getUi().alert(message);
    Logger.log(message);
  }
}


function generate_results() {
  // Get IDS and TOTALS
  var feuille = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("rendezvous");
  var idRow = 2; 
  var ids = feuille.getRange(idRow, 6, 1, feuille.getLastColumn()).getValues()[0];
  var totalRow = 59; 
  var totals = feuille.getRange(totalRow, 6, 1, feuille.getLastColumn()).getValues()[0];

  // Parse data to get the id, rendezvous and points
  var data = {};
  for (var i = 0; i < ids.length; i++) {
    id = ids[i].trim();
    points = totals[i];
    if (id && points && id in data) {
      data[id].rendezvous += 1;
      data[id].points += points;
    }else{
      if(id && points > 0){
        data[id] = {id: id, rendezvous: 1, points: points};
      }
    }
  }
  Logger.log(data); 


  // create new sheet
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var newSheetName = "results";
  var newSheet = activeSpreadsheet.getSheetByName(newSheetName);
  if (newSheet != null) {
      activeSpreadsheet.deleteSheet(newSheet);
  }
  newSheet = activeSpreadsheet.insertSheet();
  newSheet.setName(newSheetName);

  // write results
  var header = ["id", "rendezvous", "points"]; // use the keys of the hash table as the header
  newSheet.getRange(1, 1, 1, header.length).setValues([header]); // set header values

  var rowIndex = 2; // starting row for the current table
  for (var key in data) {
    if (data.hasOwnProperty(key)) {
      var row = Object.values(data[key]); // get the values of the current object
      newSheet.getRange(rowIndex, 1, 1, row.length).setValues([row]); // set current row values
      rowIndex++; // increment the row index
    }
  }

}