Transpose GSheet table with 2 sorting conditions

Question

I need to transpose this table like this :

Sorted by City as ROWS name AND by Votes as COLUMNS :

In the city of Nîmes, Candidate Maël Beaumont has 0.5 votes, Jean-Jacques Bonnot has 0.49.

She then will be placed in the Candidat1 row, and Jean-Jacques goes in Candidat2 row.

The other rows (D & E) are generated according to the votes value.

Is it possible to make that ?

Answer 1

One option to accomplish this is doing the following in Apps Script:

• Get all useful data from DatasVilles with getRange.
• Store all the source data in an object that has a property for each different city (each property key would be the city name and each property value would be the information about the different candidates and votes).
• Transform each property value, which originally would be a 2D array, by sorting the candidates according to the votes via sort, and then change the array to an 1D array with concat so that it can get appended successfully to the sheet.
• Append the array to the first empty row in the target sheet with appendRow.

It could be something along the following lines:

function getTransposedTable() {
  var ss = SpreadsheetApp.getActive();
  var origin = ss.getSheetByName("DatasVilles"); // Origin sheet
  var target = ss.getSheetByName("Target Sheet"); // Target sheet (please change accordingly)
  // Get origin values:
  var firstRow = 2;
  var firstCol = 1;
  var numRows = origin.getLastRow() - firstRow + 1;
  var numCols = 3;
  var originValues = origin.getRange(firstRow, firstCol, numRows, numCols).getValues();
  // Create object with a property for each city and 2D array for each value:
  var data = {}; // Object where to store all data
  for (var i = 0; i < originValues.length; i++) { // Iterate through each row in origin sheet
    // Set each city as property key and declare its value as an empty array:
    var city = originValues[i][0];
    if (!data.hasOwnProperty(city)) {
      data[city] = [];
    }
    var votes = originValues[i][2];
    originValues[i].push(votes * 100); // Column D
    originValues[i].push(votes); // Column E (please change this accordingly)
    originValues[i].shift(); // Remove first element for each candidate (city name)
    data[city].push(originValues[i]);
  }
  for (var prop in data) { // Iterate through each city in the data
    data[prop].sort(sortFunction); // Sort the nested arrays according to vote count
    // Change 2D array to 1D in order to append it to sheet:
    var array = [];
    for (var j = 0; j < data[prop].length; j++) {
      array = array.concat(data[prop][j]);
    }
    data[prop] = array;
    data[prop].unshift(prop); // Add city name (to be added in column A)
    target.appendRow(data[prop]); // Append row to target sheet
  }
}

function sortFunction(a, b) {
  if (a[2] === b[2]) {
    return 0;
  } else {
    return (a[2] > b[2]) ? -1 : 1;
  }
}

Notes:

• In this sample, the destination sheet is called Target Sheet, please change this name accordingly in the script.
• In this sample, I have assumed that the headers are already set in place. If they are not, and the target sheet is empty, it will append the data starting from the first row.
• appendRow is used here, so every time the script runs, the appended data will be duplicated. If you want to avoid this, you can use clearContents before appending the data.
• I don't understand how the value in column E is generated. In this sample, it's just the number of votes. Please change this according to your preferences in the script.

Reference:

• Sheet.getRange
• Object.prototype.hasOwnProperty()
• Array.prototype.sort()
• Array.prototype.concat()
• Sort 2D array by column value
• Sheet.appendRow

I hope this is of any help.