Script pour les polices municipales

gistfile1.js

var NAMES_EXTENSTIONS = ['LES', 'LE', 'LA'];
var CHAR_REPLACEMENT = {'À':'A', 'Á':'A', 'Â':'A', 'Ä':'A', 'Æ':'AE', 'Ç':'C', 'È':'E', 'É':'E', 'Ê':'E', 'Ë':'E',
  'Ì':'I', 'Î':'I', 'Ô':'O', 'Ù':'U', 'Û':'U', 'Ÿ':'Y', '-':' ', "'":' ', '’':' '};

var POLITIC_TO_ICON = {
  'PS':'small_red',
  'PC':'small_red',
  'DVG':'small_red',
  'FDG':'small_red',
  'MRG':'small_red',

  'EE':'small_green',

  'UMP':'small_blue',
  'DVD':'small_blue',

  'FN':'measle_brown'
};

function capitalize(text) {
  return text.replace(/\s+$/, '').toLowerCase().replace(/(^|\s|-)([a-z])/g, function (m, p1, p2) {
    return p1 + p2.toUpperCase()
  });
}


function normalizeCityName(cityName) {

  cityName = cityName.toUpperCase().replace(/\s+$/, '');

  for (var j = 0; j < NAMES_EXTENSTIONS.length; j++) {
    if ((cityName.length > (NAMES_EXTENSTIONS[j].length + 3)) &&
        (cityName.lastIndexOf('(' + NAMES_EXTENSTIONS[j] + ')') == cityName.length - ( 2 + NAMES_EXTENSTIONS[j].length))) {
      cityName = NAMES_EXTENSTIONS[j] + " " + cityName.substr(0, cityName.length - ( 3 + NAMES_EXTENSTIONS[j].length))
    }
  }

  cityName = cityName.replace(/[^A-Za-z0-9\[\] ]/g, function (a) {
    return CHAR_REPLACEMENT[a] || a
  });

  cityName = cityName.
      replace(/^SAINTE (.+)/, 'STE $1').
      replace(/^SAINT (.+)/, 'ST $1').
      replace(' SAINTE ', ' STE ').
      replace(' SAINT ', ' ST ');

  return cityName;
}

function loadDepartmentInfo() {
  var departmentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Départements');
  var result = {};
  var values = departmentSheet.getRange(2, 1, departmentSheet.getMaxRows() - 1, 6).getValues();

  for (var i = 0; i < values.length; i++) {
    if (values[i][0] != "") {
      result[(typeof(values[i][0]) == 'number') ? Math.floor(values[i][0]) : values[i][0] ] = {
        'nom':values[i][1],
        'population':values[i][3],
        'commentaire':values[i][4],
        'code_postal':values[i][5],
        'prefecture':values[i][2]
      };
    }
  }
  return result;
}

function loadInfoByDepartmentAndCityName() {
  var infoSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Villes');
  var result = {};
  var values = infoSheet.getRange(2, 1, infoSheet.getMaxRows() - 1, 7).getValues();

  for (var i = 0; i < values.length; i++) {
    var departmentId = values[i][0];

    if (result[departmentId] == null) {
      result[departmentId] = {};
    }

    result[departmentId][values[i][1]] = {
      'code_postal':values[i][2],
      'code_insee':values[i][3],
      'population':values[i][4],
      'longitude':values[i][5],
      'latitude':values[i][6]
    };
  }
  return result;
}

function fillDepartments() {
  var infoByDepartmentAndCityName = loadInfoByDepartmentAndCityName();
  var departmentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Département');
  for (var i = 2; i < departmentSheet.getMaxRows() - 1; i++) {
    var codeCell = departmentSheet.getRange(i, 6, 1, 1);

    var values = departmentSheet.getRange(i, 1, 1, 3).getValues()[0];
    var departmentCode = values[0];
    if (departmentCode != "") {

      if (infoByDepartmentAndCityName[departmentCode] == null) {
        codeCell.setValue("Dpt inconnu");
        codeCell.setBackgroundColor("Red");
      } else {
        var cityName = normalizeCityName(values[2]);
        var cityInfo = infoByDepartmentAndCityName[departmentCode][cityName];
        if (cityInfo == null) {
          codeCell.setValue("Non Trouvé");
          codeCell.setBackgroundColor("Red");
        } else {
          codeCell.setValue(cityInfo.code_postal);
          codeCell.setBackgroundColor("White");
        }
      }
    }
  }
}

function calculCarte() {
  var infoByDepartmentAndCityName = loadInfoByDepartmentAndCityName();

  var policeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Source');
  var resultSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Result');

  resultSheet.clear();
  resultSheet.
      getRange(1, 1, 1, 8).
      setValues(
      [
        [
          "Nom",
          "Code Postal",
          "Étiquette politique",
          "Population",
          "Effectif",
          "Latitude",
          "Longitude",
          "Icone"
        ]
      ]);

  var values = policeSheet.getRange(2, 1, policeSheet.getMaxRows() - 1, 7).getValues();
  var targetSheetRow = 2;

  var numbersForDepartments = {};

  for (var i = 0; i < values.length; i++) {
    var departmentCode = values[i][0];
    if (departmentCode != "") {
      var codeCell = policeSheet.getRange(2 + i, 4, 1, 1);
      if (infoByDepartmentAndCityName[departmentCode] == null) {
        codeCell.setValue("Dpt inconnu");
        codeCell.setBackgroundColor("Red");
      } else {
        var cityName = normalizeCityName(values[i][1].toUpperCase());
        policeSheet.getRange(2 + i, 3, 1, 1).setValue(cityName);

        var cityInfo = infoByDepartmentAndCityName[departmentCode][cityName];
        if (cityInfo == null) {
          codeCell.setValue("Non Trouvé");
          codeCell.setBackgroundColor("Red");
        } else {
          codeCell.setValue(cityInfo.code_postal);
          codeCell.setBackgroundColor("White");

          var effectif = values[i][4];
          if ((effectif != "") && (effectif != 0)) {

            var forDepartment = effectif;

            if (typeof(values[i][6]) == 'number') {
              forDepartment = values[i][6];
            }

            if (numbersForDepartments[departmentCode]) {
              numbersForDepartments[departmentCode] += forDepartment;
            } else {
              numbersForDepartments[departmentCode] = forDepartment;
            }

            var policitIcon = 'measle_white';
            if (values[i][6] && (values[i][6] != '')) {
              policitIcon = POLITIC_TO_ICON[values[i][6]] || 'measle_white';
            }

            resultSheet.
                getRange(targetSheetRow, 1, 1, 8).
                setValues(
                [
                  [
                    capitalize(values[i][1]), //city name
                    cityInfo.code_postal, // postal code
                    values[i][6] || "", // politic color
                    cityInfo.population, // population
                    effectif, // effectif
                    cityInfo.latitude, // latitude
                    cityInfo.longitude, // longitude
                    policitIcon // icon
                  ]
                ]);
            targetSheetRow++;
          }
        }
      }
    }
  }

  // aggregation by department
  var departmentsInfo = loadDepartmentInfo();
  for (var departmentId in numbersForDepartments) {
    var numberForDepartment = numbersForDepartments[departmentId];
    var departmentInfo = departmentsInfo[departmentId];
    var departmentPosition = infoByDepartmentAndCityName[departmentId][normalizeCityName(departmentInfo.prefecture)];

    resultSheet.
        getRange(targetSheetRow, 1, 1, 8).
        setValues(
        [
          [
            departmentInfo.nom, //department name
            departmentInfo.code_postal, // postal code
            "", // politic color
            parseInt(departmentInfo.population.replace(/\s/g, '')), // population
            numberForDepartment, // effectif
            departmentPosition.latitude, // latitude
            departmentPosition.longitude, // longitude
            'wht_blank' // icon
          ]
        ]);
    targetSheetRow++;
  }

}